asked on

Detect SQL Statement that causing Deadlock

Hello All,

I have a big data application, so many insert/udate/select statement execute at a single time. we are have so many sp's, function's, view's and sometime other people execute SQL commands from Management studio.

Generally we takecare of SQL hints, but We know some old SP's/function's/view's/statemtns/jobs may not using SQL hints.

Now problem is:
We want to detect which SQL statement is causing deadlock. we know sp_who2 and other commands that tell us how to see which spid is causing deadlock, but specifically we want to know Statement that causing problem, also name of SP/View/Job/Function that is related.

For SQL Statement, executed manually, can be determined easily using information available in sp_who2.

Thanks beforehand.

ASKER CERTIFIED SOLUTION

MSSystems

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mohit Vijay

ASKER

Thanks for quick response. I will check it on detail. But What these statements do? I dont think they will show me the SQL Statement/Stored Procedure/View/Function those causing deadlock.

MSSystems

The stored procedure will return the following, if anything was found. I hope this helps.

--------- -----------
Blocker: 56

EventType Parameters EventInfo
-------------- ---------- ----------------------------
Language Event 0 exec [sp_rk_blocker_blockee]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

------------------ ----------- -----------
Blockee: Waittime: 58 46380

EventType Parameters EventInfo
-------------- ---------- ------------------------------------------------------------------------------------------------------------------
Language Event 0 -- 3) Run in second connection
BEGIN TRAN
UPDATE tempdb.dbo.bar SET col1 = 1
UPDATE tempdb.dbo.foo SET col1 = 1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SOLUTION

ThakurVinay

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mohit Vijay

ASKER

I am still reviewing facts..