Finding deatail Deadlock info IN SQl Server 2005 Standard Edtion

We are using SQL Server 2005 with SP2.Originally, we seemed to be having a lot of unnecessary lock escalation occurring to the page and table level.

I would like to track down all storedprocedure/queries or function that are being casue of deadlock in sql server 2005 with all deadlock input like transactionID ,command ran,loginID etc.But we have to need to find these information on SQL Server 2005 standard edtion.

Is there any tool,command or something like this which track down these information blindly in sql server 2005 without effecting system performance seriously.

Ahamd Drshen

Hi,

When executing DBCC TRACEON (1204,-1) in a command window, you're enabling the deadlock tracing option. (http://msdn2.microsoft.com/en-us/library/ms188396.aspx) When a deadlock occurs, you can find details in the logs. You can also try using the option 'with nolock' in your sql statements (SELECT * FROM Employees WITH (NOLOCK)).

Regards,

Remco

|||will it track all deadlock resource information SQL Server Errors log ?|||it will show all conflicting locks and its commands.|||

Use SQL Server Profiler with DeadLock Graph Event.

Or use stored procedures sp_create_trace and sp_trace_setevent for collecting profiler events without graphical interface.

@.eventId for DeadLock Graph = 148