Catch Blocking and log it to a database - SQL 2005

Last Post 05 Aug 2008 03:54 AM by tomsql. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Jeff Armstrong
New Member
New Member

--
27 Sep 2007 06:17 PM
This script is in it's infancy, but I wanted to share with you all now in hopes that someone could add some suggestions to improve it's results.

Occasionally our system will encounter some blocking and if we're lucky, we'll be close by or physically looking at the servers at the time which allows us to capture it. Other times it goes unnoticed by us, but not by our customers. My boss has always asked me how often we have blocking and to what severity. Most of the time I have no answer for him as we do not have any real time monitoring of blocking, thus no historical data to look back on either (we have used Teratrax Performance Monitor, but that does get expensive when you start putting it on multiple servers). This lead me to create my own type of real time monitoring of blocking which writes to a database for future reference. Eventually I will have it send me an email if the wait time on the first blocked process reaches a certain threshold. For now, here it is (simple isn't it).

The below script is put into a SQL Server job that is run once. The code will loop forever with a wait delay of 15 seconds between iterations.


while 1=1 begin
insert into tp_work.dbo.blockingsql select * from openquery
([linked server name here], 'select getdate(), text, owt.wait_duration_ms from sys.dm_exec_connections ec
cross apply sys.dm_exec_sql_text(most_recent_sql_handle)
cross apply sys.dm_os_waiting_tasks owt
where blocking_session_id = ec.session_id
and blocking_task_address is NULL ')
waitfor delay '00:00:15'
end


Basically, the script outputs the current date, the text of the process doing the blocking not the blocked process, and the duration that the first process has been blocked for. Eventually I'd like to add a threshold for the duration of the blocking so that I'm not capturing 0.2 second blocks ect.

I have yet to decide if I'm going to put this job on the servers themselves or simply continue calling them via linked servers and storing the data on my admin server (suggestions welcome).

Hope someone is able to use this to their advantage

Jeff.
arindamg
New Member
New Member

--
20 Mar 2008 04:09 AM
Hi,

Although it is too late to reply. I have a query. Have you already made any matured systems for checking the blocking ?

My main concern is that the continuous execution of this script over the network may bring down the conjestion.

Warm Regards,
Arindam.
tomsql
New Member
New Member

--
05 Aug 2008 03:54 AM
Hi,

Great script, I like and thanks this, it is useful for our projects, I need this script for SQL Server job that is run once. Thanks again.
You are not authorized to post a reply.

Acceptable Use Policy