SQL 2012 :: Track Blocking Details At A Specific Time?
May 28, 2015In sql 2012 i want to track the blocking details at a specific time is any query to get the data ?
View 6 Replies
ADVERTISEMENT
SQL 2012 :: Kill Processes At A Specific Time Then Log To A Table
Apr 3, 2015So I have a job that shows active processes
INSERT INTO Query_results(login_name, total_elapsed_time, total_elapsed_time)
SELECT login_name, total_elapsed_time, total_elapsed_time FROM
sys.dm_exec_sessions
I need to then kill all sessions at 11:59pm then log all those that are killed. This is so I can schedule a job at that time, I have sessions that are blocking my job.
SQL Server 2012 :: Cursor Stop Query At Specific Time?
Oct 29, 2015This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
[code]....
Track A Specific Procedure In Profiler
Aug 2, 2006Hello
I am using SQL Server 2000 and How should I track a specific procedure and DMLs in it with profiler, I was trying to filer ObjecName=MySP but it seems it does not track only the SP. Any idea?
How To Track Changes Made To A Specific Table?
Nov 3, 1998Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it
and it has a delete trigger on it but we want to track the changes to
the table and were wondering how to track specific changes to a user database?
We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used
SELECT * FROM SYSPROCESSES
and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and
Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
Track A Specific Procedure In Profiler
Aug 2, 2006Hello
I am using SQL Server 2000 and How should I track a specific procedure and DMLs in it with profiler, I was trying to filer ObjecName=MySP but it seems it does not track only the SP. Any idea?
T-SQL (SS2K8) :: Track How Many Times A Specific SP Called Every Day
Apr 3, 2014I need to know /observe for a period like 10 days in a row HOW MANY TIMES a specific Stored Proc is executed on the server. How can I do that? is there a way to precisely know a number of such calls via DMVs?
View 9 Replies View RelatedBlocking Specific IP From Logon
Nov 24, 2007My company is apparently performing some sort of security test on our SQL Servers by sending malformed packets to it, which results in SQL Server throwing a Severity 20 error and resulting in my butt being paged every night at around 3 AM. As you can imagine, this ticks me off, but they refuse to stop this test. The [edited] error text from the SQL Log is as follows:
Source Logon
Message
Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: 1.2.3.4]
Source Logon
Message
Error: 17836, Severity: 20, State: 1.
Is there a way to completely block a specific IP from attaching to SQL Server? Someone please let me have a complete night's sleep!
Brandon
SQL Server 2008 :: How To Track Modification Date On Specific Table
Jul 13, 2015I would like to know about the DML process (Insert/update /delete) in a particular table .. it is like change tracking but I also want to know the modification date
I know CDC ( Change data capture ) but unfortunately it needs SQL 2008 developer/enterprise edition and my SQL server is SQL 2008 STANDARD edition.
SQL 2012 :: How To Track SPs To TempDB Usage
Apr 10, 2015I can get a snapshot of tables in tempDB, but I would like to track which procs are causing the load in the tempDB.
I think I can sample and record objects in the tempdb, but I would like to record the proc creating the most tempDB usage, and disk read/writes associated with those procs.
The DMV's give usage in the individual DB's, but what's a good way to correlate procs in the DB's to tempdb usage?
SQL 2012 :: How To Keep IAM Page Track Of Data Pages
Sep 15, 2014I in a table or partition, the first page is an IAM-Page if I'm not wrong, this page keeps track of the extents.
In the first extent to where the "first_iam_page" points to, the extent is a mixed extent, therefore the pages can be from different tables or partitions, correct? How does my IAM-Page map the right pages to the corresponding table? the following extents are all uniform of one table type, so I guess it doesnt matter then. But n, how does it keep track of which pages belong to which tables in the first extent?
My second question is, the first IAM-Page is obviously an IAM-Page, but there are also GAM , SGAM and PFS Page files... where are they stored? Because when I create a table and insert a big value(8000) into it, it reserves 16KB for that table, one for the IAM-page, and one for the first data-page. But where can I find the GAM,SGAM and PFS page files? or are they not page files, just some other structures?
SQL 2012 :: Track All Logins Onto A Single Database?
Oct 17, 2014We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
Blocking With RECEIVE And SqlDataReader For Real-time Updates
Jul 17, 2007I have this scenario which is working fine, but would like to know if others have tried it or can recommend a better approach. Below is a brief description, but code should fully explain:
A process updates a SQL Server table via a stored proc, which in turn writes information to a service broker target queue. In my client/server architecture, I would like clients to see this new information as soon as it gets written to the target queue. To do this, I have a WCF service that calls a stored procedure; the stored proc contains an infinite loop which has a RECEIVE statement with an infinite timeout. Once RECEIVE reads data from the SB queue, data is retrieved via a SqlDataReader and results are sent to clients via a pub/sub. I then wait for more rows until RECEIVE unblocks and so on.
Stroed Proc
Code Snippet
-- ...
WHILE 1 = 1
BEGIN
-- Param declarations
...
BEGIN TRANSACTION;
WAITFOR
(
-- Blocks while TargetQueue is empty
RECEIVE
TOP(1)
@conversation_handle = conversation_handle,
@message_type_name = message_type_name,
@conversation_group_id = conversation_group_id,
@message_body =
CASE
WHEN validation = 'X' THEN CAST(message_body AS XML)
ELSE CAST(N'' AS XML)
END
FROM [dbo].[TargetQueue] -- No time out!
)
-- Handle errors
...
-- Return received information. After this statement is executed,
-- reader.NextResult unblocks and reader.Read() can read these
-- new values
SELECT 'Conversation Group Id' = @conversation_group_id,
'Conversation Handle' = @conversation_handle,
'Message Type Name' = @message_type_name,
'Message Body' = @message_body ;
COMMIT TRANSACTION
END -- WHILE
C# Code
Code Snippet
// Create a SqlCommand, and initialize to execute above stored proc
// (set command time to zero!)
...
// ExecuteReader blocks until RECEIVE reads data from the target queue
using (SqlDataReader reader = cmd.ExecuteReader())
{
// Begin an infinite loop
while (true)
{
// Process data retrieved by RECEIVE
while (reader.Read())
{
Trace.WriteLine("Conversation Group Id :" + reader.GetGuid(0) +
"Conversation Handle: " + reader.GetGuid(1) +
"Message Type Name : " + reader.GetString(2) +
"Message Body : " + reader.GetString(3));
// Send data via pub/sub
...
}
// Blocks until stored procedure returns another select statement
// i.e., blobks until RECEIVE unblocks and retrieves more data from queue
reader.NextResult();
} // while
}
SQL Server 2012 :: Script To Track When Stored Procedures Changed
Sep 30, 2015Any script to identify when Stored Procedures were changed?
View 2 Replies View RelatedSQL 2012 :: Blocking During Bulk Inserts?
Dec 9, 2014We are noticing a lot of blocking when performing bulk inserts.
Sometimes the lead blocker(s) are blocking inserts into the same tables, but most of the time, it's insert to other tables. So, while bulk insert into table X is running, bulk insert into table Y is blocked from the insert into X.
- We turned off transactions from the application performing the bulk inserts - no change.
- We enabled table locking from the application - no change
- We enabled lock_on_bulk_load on the tables - this seems to have worked; however still see some blocking.
According to the lock_on_bulk article - When you specify table locking for a bulk import operation, a bulk update (BU) lock is taken on the table for the duration of the bulk-import operation, shouldn't we be seeing this BU lock? Instead, we see nothing but LCK_M_X (exclusive table locks).
Just found out that in order get a BU lock, no indexing can exist on the table ... sure be nice if that was in the article!
Also, we saw the exclusive locks happening before we made these changes, meaning it wasn't using the row locks that it should be by default. Assuming we're missing something here with lock escalation. I am profiling just for lock escalation, and it never happens ...
So I guess my pending question at this time is that why when inserting into table X do inserts into table Y get blocked?
SQL 2012 :: Set Statistics XM Off - Blocking From Using Object Explorer
Jul 9, 2014Seen activity like this? If so, where does it come from?
dd hh:mm:ss.mss:00 00:18:32.210
session_id:79
login_name:meme
wait_info:(6ms)IO_COMPLETION
CPU:646,180
tempdb_allocations:2,088
tempdb_current:0
blocking_session_id:NULL
reads:171,237,095
writes1,439,934
physical_reads:637,080
used_memory:2
status:rollback
SQL 2012 :: Blocking Alerts Using Event Notifications?
Nov 28, 2014I created a Blocking Event Notification following this article ->[URL=
How do I throttle the alerts so that I only receive one alert per hour instead of 1000?
SQL 2012 :: Blocking In Report Server TempDB
Sep 21, 2015I am seeing select * from sys.sysprocess where blocked<>0 is always the report server
ReportServer.dbo.GetSessionData;1 is blocked by ReportServer.dbo.WriteLockSession;1
We have different reporting servers using same database.
Is it require to rebuild indexes on report server and report server temp db on daily basis?
SQL 2012 :: Blocking Frequently Report Server TempDB
Aug 27, 2015How to solve the below blocking issue
One of our production server is configured with Always on with sql cluster.
We are getting frequently blocking
Waiting Resource Key Type: Key
WaitType: LCK_M_S
WaitResourceDatabaseName : ReportServerTempDB
WaitingSessionProgramName: Report Server
BlockingSessionProgramName: Report Server
WaitCommandType: Select
WaitingCommandText: Create Procedure dbo.checksessionLoak @sessionID...
SQL 2012 :: Capture Deleting Job Details?
May 13, 2015One of our Application creating run time SQL job to run batch process and application itself deleting this run time SQL job, but when doing deletion it’s not checking whether job is running or not, just doing direct delete. Our challenge is how to capture deleted job details, after incident happen we could see error details only in this log.
We can run profile to trace but we don’t know when it will trigger and we cannot possible keep active profiler as it will kill server.so we can't run the trace for log time as we don't know when the issue happens.
SQL 2012 :: Status Of Order Details Per Consumer
Nov 27, 2014I have a requirement to present computed sales order status value
Parent table : consumer
Child table of consumer : sales order
Child table of sales order: items
Items table has status column with awaiting, accepted , rejected.
Status of the sales order should be set as following
If all items are rejected then sales order status=reject
Or if at least one item is accepted then sales order status = accept
Or if items status is awaiting then sales order status = pend
Query to find all sales order per consumer with computed order status for each sales order.
SQL 2012 :: Computer Details Of The User That Is Logged In
Jan 20, 2015I need to get the computer details of the person that logged in. I have a client that needs me to write a computer asset logging system for them. This is a one table database that will keep all the computers in the company's details like serial number, bios serial number, ram size disk size, mother board info etc. This is a vast number of details and what they want to do is when for instance the ram changes e.g. taken out or added they need the program to update the table with the new information once the computer starts again.
View 4 Replies View RelatedSQL 2012 :: Finding Version Details From IP And Port
Oct 26, 2015Is it possible to find the version details from IP and Port.
For example I have IP and port number known to me ,but I don't have database credentials and other details.
I want to know which version of Sql server is running.
My purpose is to find the false vulnerabilities in security scanning.
SQL 2012 :: Blocking In Report Server Database And Changing Isolation Levels?
Oct 12, 2015We are getting frequently blocking in Report server database.
Is it ok to change isolation level to RCSI for report server database?
SQL 2012 :: Script To Find Out Blocked Sessions And Send Email Whenever It Finds Blocking
Feb 20, 2014I want to implement a script which will run continuously to find out blocked sessions and send an email when ever it finds the blocking.
View 5 Replies View RelatedSQL 2012 :: Sleeping Queries Blocking Rebuild Index And Update Statistics Job In AlwaysOn
Feb 3, 2015At one of your client sides we have configured Always on with synchronous mode.Also we have schedule rebuild index and update statistics job which runs in night every alternate day. the issue is there are more then 100 sleeping queries which is blocking update statistics job.
I have to stop update statistics job manually once i come to office manually.
Once I have killed blocking sleeping query but then other sleeping query blocked it and so on.
To SLQ Statement At A Specific Time
Sep 26, 2005Hi There,
I want to able to run a batch of sql statments (execute procedures) in SQl Server at a specific time. Just wondering if there is a method for that.
Setting A Specific Time
Aug 13, 1998I`m developing a library catalog in SQL server with a Cold Fusion front end, and I`m having some trouble with setting due dates for books when they get checked out. Most items in the library are due in a week, a month, etc., so it is easy to calculate the due date. However, we have a number of items that are due at the end of the working day.
So, I need to find a way to calculate "the next 5 PM", no matter what time of day it is, and store it in a datetime field? Any ideas?
Any help would be appreciated.
Thanks,
-Elizabeth S. Thomas
Technical Librarian
MAJIQ, Inc.
ELizabeth_Thomas@majiq.com
Run Sql Script At A Specific Time
Oct 25, 2005I have an sql statement that I can run by hand. How do I get a job to run this automatically.
I have tried to create a job and it just failes out every time...
SQL Server 2012 :: Query To Select Parent Details From Child Table
Mar 3, 2015I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
Automatic Trigger On A Specific Time
Aug 25, 2006Hi!I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:periode startdate enddate1 23.08.2006, 15:00 24.08.2006, 17:002 24.08 .2006, 17:00 25.08.2006, 08:00and so onCan anyone help me how to do this? Thanks!
View 5 Replies View RelatedHow To START And Stop Profiler At Specific Time
Feb 25, 2004Hi,
We want trace ALL activity on prod
server between 3:00 AM and 3:15 AM
Profiler has stop time option ,but
no start option.
I am looking for example how it could be done
Thank you
Alex
Query To Find SQL Activity At Specific Time?
Mar 23, 2007Ok, please pardon my complete ignorance; but I am extremely SQL challenged. I manage the server right now and I am the closest thing to a DBA at the moment with this server.
Here is the problem. I have a job that runs every hour and until last week it was running without any problems. Now, once a day it hangs, and I end up having to restart sql to get it to be functional again. I guess I could just kill the spid, but regardless, it's fails.
It doesn't happen at the same time each day. I thought it was from the backups doing a SQL backup job, but I eliminated that possibility. I can't find what is causing the problem, because the SQL logs just stop at the time that this happens. It's like some other job/query is running and taking all the sql server resources. But I can never catch it when it's happening and I have no event logs or SQL logs to go on.
Is there any query that I can run to find what happened around the specific time the job failed to run? I just need to find what process did what when this job stopped working.
Again, I apologize if my lack of SQL knowledge is making me ask dumb questions, but I am really lost.
Thanks in advanced!