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 |
|
bobanjayan
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. |
|
|
nr
SQLTeam MVY
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. |
 |
|
|
bobanjayan
Yak Posting Veteran
67 Posts |
Posted - 2004-04-20 : 05:00:43
|
| Nice idea. |
|
|
|
derrickleggett
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
Trigger for lock event.