Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Trigger for lock event.

Author  Topic 

Yak Posting Veteran

67 Posts

Posted - 2004-04-17 : 03:57:32

Is it possible to create a trigger on the event of block/Lock if the status "Sleeping" and wait type is "Awaiting Command" ?

So that it automativally kill the session when it detects lock contention.


12543 Posts

Posted - 2004-04-17 : 19:07:38
Not a trigger but you can write an sp and schedule it to run frequently.
You probably wouldn't want to kill this sort of process immediately anyway.

Another option would be to use the profiler to log the events to a table and put trigger on the table.

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Yak Posting Veteran

67 Posts

Posted - 2004-04-20 : 05:00:43
Nice idea.
Go to Top of Page

Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-20 : 08:02:20
If you're getting to the point where locks are this bad, you should probably look at using WITH(NOLOCK) strategically and a possible redesign on your tables. Have you thought about that? You can find the contention areas by using Profiler like Nigel suggested.


When life gives you a lemon, fire the DBA.
Go to Top of Page

- Advertisement -