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.

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.

arindamgNew 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.

tomsqlNew 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.