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

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!
Maybe these will help:
http://msdn.microsoft.com/library/de...tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

find out lock start time

I think it would be great to investigate into system performance and dead
lock if we can find out the start time for the current locks. Is there a way
to do that? thanks!
I tried the following 2 methods:
1) EXEC sp_lock
2) In the enterprice manager | management | current activities |
locks/object
Neither view gives me what I'm looking for.
thanks again!Maybe these will help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
Andrew J. Kelly SQL MVP
"Zeng" <zeng@.nononospam.com> wrote in message
news:%23ATn9732GHA.2096@.TK2MSFTNGP05.phx.gbl...
> I think it would be great to investigate into system performance and dead
> lock if we can find out the start time for the current locks. Is there a
> way to do that? thanks!
> I tried the following 2 methods:
> 1) EXEC sp_lock
> 2) In the enterprice manager | management | current activities |
> locks/object
> Neither view gives me what I'm looking for.
> thanks again!
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked?
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.
Yitzak
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegrou ps.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked?
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId = 1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked'
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.Yitzak
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegroups.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId => 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked'
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>

Find a name of an index in deadlock situation.

Hi
I have a deadlock occurring.
Using MSSQL 2005 current activity lock screens I can see one process is
waiting for a lock to be released on a Database object. with objectId =
1441294802092032 and type = KEY - so I know I have a lock on an Index
and what table the index belongs to,
But how can I find the name of the index that is blocked'
This was part of the default view in SQL2000 activity manager. I cannot
use object_name (1441294802092032 ).
Pain is cannot even export the details of the locks per process to a
file.Yitzak
http://blogs.msdn.com/bartd/archive.../09/747119.aspx
"Yitzak" <terryshamir@.yahoo.co.uk> wrote in message
news:1164814916.178901.38620@.16g2000cwy.googlegroups.com...
> Hi
> I have a deadlock occurring.
> Using MSSQL 2005 current activity lock screens I can see one process is
> waiting for a lock to be released on a Database object. with objectId =
> 1441294802092032 and type = KEY - so I know I have a lock on an Index
> and what table the index belongs to,
> But how can I find the name of the index that is blocked'
> This was part of the default view in SQL2000 activity manager. I cannot
> use object_name (1441294802092032 ).
> Pain is cannot even export the details of the locks per process to a
> file.
>