SQL Server deadlock processing Stored Procedure

Source: Internet
Author: User

/* -- Handle deadlocks

View the current process or deadlock process and automatically kill the dead Process

Because it is intended for dead, if there is a deadlock process, you can only view the deadlock Process
Of course, you can use Parameter Control to check only the deadlock process, whether there is a deadlock or not.

-- Producer build 2004.4 --*/

/* -- Call example

Exec p_lockinfo
--*/
Alter proc p_lockinfo
@ Kill_lock_spid bit: -- whether to kill the deadlock process; 1: Kill; 0: only show
@ Show_spid_if_nolock bit -- if there is no deadlock in the process, whether the normal process information is displayed, 1 is displayed, 0 is not displayed
As
Declare @ count int, @ s nvarchar (1000), @ I int
Select id = identity (INT, 1, 1), flag,
Process id = spid, thread id = kpid, block process id = blocked, Database ID = dbid,
Database Name = db_name (dbid), user id = uid, user name = loginame, accumulated CPU time = CPU,
Login Time = login_time, number of opened transactions = open_tran, Process status = status,
Workstation name = hostname, application name = program_name, workstation process id = hostprocess,
Domain name = nt_domain, NIC address = net_address
Into # T from (
Select flag = 'deadlocked process ',
Spid, kpid, A. Blocked, dbid, uid, loginame, CPU, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = A. spid, S2 = 0
From master .. sysprocesses a join (
Select blocked from Master... sysprocesses group by blocked
) B on A. spid = B. Blocked where a. Blocked = 0
Union all
Select '| _ victim _> ',
Spid, kpid, blocked, dbid, uid, loginame, CPU, login_time, open_tran,
Status, hostname, program_name, hostprocess, nt_domain, net_address,
S1 = blocked, S2 = 1
From master .. sysprocesses A where blocked <> 0
) A order by S1, S2

Select @ COUNT = @ rowcount, @ I = 1

If @ COUNT = 0 and @ show_spid_if_nolock = 1
Begin
Insert # T
Select flag = 'normal process ',
Spid, kpid, blocked, dbid, db_name (dbid), uid, loginame, CPU, login_time,
Open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address
From master .. sysprocesses
Set @ COUNT = @ rowcount
End

If @ count> 0
Begin
Create Table # T1 (ID int identity (255), a nvarchar (30), B INT, eventinfo nvarchar ))
If @ kill_lock_spid = 1
Begin
Declare @ spid varchar (10), @ sign varchar (10)
While @ I <= @ count
Begin
Select @ spid = process ID, @ sign = sign from # t where id = @ I
Insert # T1 exec ('dbcc inputbuffer ('+ @ spid + ')')
If @ sign = 'deadlocked process' exec ('Kill '+ @ spid)
Set @ I = @ I + 1
End
End
Else
While @ I <= @ count
Begin
Select @ s = 'dbcc inputbuffer ('+ Cast (process ID as varchar) +') 'from # t where id = @ I
Insert # T1 exec (@ s)
Set @ I = @ I + 1
End
Select a. *, the SQL statement of the Process = B. eventinfo
From # t a join # t1 B on A. ID = B. ID
End

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.