Stored procedures:
1 Create procedureSp_who_lock2 3 as4 5 begin6 7 Declare @spid int,@bl int,8 9 @intTransactionCountOnEntry int,Ten One @intRowcount int, A - @intCountProperties int, - the @intCounter int - - - + Create Table#tmp_lock_who ( - +Idint Identity(1,1), A atspidsmallint, - -Blsmallint) - - - in IF @ @ERROR<>0 RETURN @ @ERROR - to + - Insert into#tmp_lock_who (SPID,BL)Select 0, blocked the * from(Select * fromsysprocesseswhereBlocked>0) A $ Panax Notoginseng where not exists(Select * from(Select * fromsysprocesseswhereBlocked>0) b - the whereA.blocked=spid) + A Union SelectSpid,blocked fromsysprocesseswhereBlocked>0 the + - $ IF @ @ERROR<>0 RETURN @ @ERROR $ - - the --number of records found for temporary table - Wuyi Select @intCountProperties = Count(*),@intCounter = 1 the - from#tmp_lock_who Wu - About $ IF @ @ERROR<>0 RETURN @ @ERROR - - - A if @intCountProperties=0 + the Select 'No blocking and deadlock information now' asmessage - $ the the --Cycle Start the the while @intCounter <= @intCountProperties - in begin the the --take the first record About the Select @spid =spid@bl =BL the the from#tmp_lock_whowhereId= @intCounter + - begin the Bayi if @spid =0 the the Select 'causing the database deadlock is:'+ CAST(@bl as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax' - - Else the the Select 'process number spid:'+ CAST(@spid as VARCHAR(Ten))+ 'was' + 'process number spid:'+ CAST(@bl as VARCHAR(Ten))+'The SQL syntax that the current process executes is blocked as follows' the the DBCCInputBuffer (@bl ) - the End the the 94 the --The loop pointer moves down the the Set @intCounter = @intCounter + 198 About End - 101 102 103 104 the Drop Table#tmp_lock_who106 107 108 109 return 0 the 111 End
1 //// viewing processes, processing processes 2 3 4 /// Close Process 5 KILL 126 6 7 8 9 /// View process Status Ten DBCC InputBuffer (a)
C # The path of the sage note--sql by creating a stored procedure to view the current process (whether it caused a table lock death)