Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
solart
Posting Yak Master
148 Posts |
 Posted - 2002-08-16 : 13:45:50
|
| I would like to be able to setup an "alert" which detects that blocking has occurred and then put that notification in the Windows event log. This "alert" would be turned on for short periods of time.My purpose in this is just to simply determine if blocking is occuring (at this point). My casual periodic observation of the system is never able to detect any blocking occurring.How do I do this?TIA solart |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-08-16 : 15:30:07
|
How often do you want to check for blocking? You could stick the code I provided below into a SQL Agent job, and run it every few minutes... If you want to monitor your server more aggressively, you could create a stored proc with an endless loop and a WAITFOR DELAY to monitor the server every few seconds. Personally I don't feel that checking for blocking every few seconds is a good idea.The code below will only tell you if blocking occured, but it will not give you any information on what caused it. So if you need to troubleshoot a blocking problem, you might want to search Microsoft KB -- I know there is a good article in there someplace that has a script of the stored proc that can tell you everything you ever wanted to know about blocking in your db. declare @blocked_cnt intdeclare @logging_lvl intdeclare @blocked_msg varchar (255)set @logging_lvl = 3select @blocked_cnt = count(*) from master..sysprocesses where blocked <> 0 if @blocked_cnt >= @logging_lvlbegin set @blocked_msg = cast(@blocked_cnt as varchar(5)) + ' blocks detected' exec master..xp_logevent 50001, @blocked_msg, 'WARNING'end Edited by - izaltsman on 08/16/2002 15:30:59 |
 |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-08-16 : 16:28:50
|
| What you have proposed looks perfect to me. The script is very much appreciated.I will take the route where SQL Agent kicks off the job every few minutes.Again, many, many thanks!!!solart |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-08-16 : 17:37:11
|
Here's a bit of light reading  Q251004 INF: How to Monitor SQL Server 7.0 Blocking[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q251004[/url]Q271509 INF: How to Monitor SQL Server 2000 Blocking[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q271509[/url]Q224453 INF: Resolving SQL Server 7.0 or 2000 Blocking Problems[url]http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453[/url]HTHJasper Smith |
|
|
|
|
|
|
How to setup an alert notifying DBA of blocking?