Hi All
The error received:
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction
(Process ID 73) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

any suggestion on how to avoid this in future?
it came about in asp pages running stored procedures.
is it possible to find the cause of this deadlock?

thank for any advise...
akthar


ak

__________________
ak

Take a look at this post in the archive http://p2p.wrox.com/topic.asp?TOPIC_ID=3159

See also http://p2p.wrox.com/topic.asp?TOPIC_ID=3360

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff Mason and PGTIPS thanks for your help
...how would i recognise which objects are causing the dead locks ?
When a lock occures, I am trying to track the objects involved in the lock.


ak

It can be difficult to identify the offending objects, as you have to catch the deadlock actually happening. If you can, there are a few ways to view the lock activity. Be aware, though, that any way you do it may mean that you may have to sift through mountains of output, especially if your server is busy or heavily loaded (which is probably only when the deadlock occurs :( ), so this may not be for the faint of heart...

You can use EM; select the server, then Management -> Current Activity -> Locks. This will display information about the process, which locks they hold, and the last SQL statement they executed.

You can use the stored procedures sp_who2 and sp_lock. sp_who2 will identify the processes involved in blocking, and sp_lock will tell you why they are blocked.

You can set trace flag 1204 and get very detailed info about the processes involved and what they were doing. See BOL index for 'trace flag 1204' to see how to do this and for other info on identifying deadlock situations.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com