Notification of blocking locks
-
I know that there are trace flags that can be set to notify an administrator of any deadlocks are occurring, there is even a trace flag that writes the deadlock system information to the error log.
However, is there any way to notify an administrator of blocking locks, without having to run a trace via Profiler, develop a script to poll the system databases or using some form of Microsoft's mailing applications?
-
Not that I know of. And you wouldn't want to be notified unless the block lasted xx amount of time. There will be tons of blocks that occur constantly, but most last miliiseconds. You wouldn't want to be pinged on these. Best bet is run a script and if the last batch time is substantially more than a few seconds and there is a block, then notify the administrator.
-
Try this SQL script
set quoted_identifier off
declare @blocked int
select @blocked=count (*) from sysprocesses where status <> "background" and cmd not in
('signal handler', 'lock monitor', 'log writer', 'lazy writer', 'checkpoint sleep', 'awaiting command')
and blocked <>0
Select Blocked = @Blocked
If @blocked <> 0
select spid ,
hostname=convert (char (15), hostname) ,
UserName=convert (char (10), nt_username) ,
blkby=convert(char(5),blocked) ,
Application=convert (char (14), program_name) ,
status=convert (char (8), status) ,
dbname= convert ( char (8), db_name(dbid)) ,
cmd=convert (char (8),cmd) ,
CPU=convert (char(6),cpu) ,
physIO=convert(char (7),physical_io)
from master.dbo.sysprocesses
where status <> "background" AND cmd NOT IN ( 'signal handler', 'lock monitor', 'log writer', 'lazy writer', 'checkpoint sleep', 'awaiting command')
and blocked<>0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply