Today to help colleagues solve the problem, the page report "Waiting for the operation is outdated", set breakpoints to find the database query statement at the exception, check the database a pass, found that the connection database is not connected, searched a circle to find a solution. Keep the spare.
First, the deadlock is detected, SQL statements are available
SELECT blocking_session_id ' Blocking process id ', Wait_duration_ms ' Wait Time (msec) ', session_id ' (Session ID) ' from sys.dm_os_waiting_tasks
or create the following stored procedure, query it out
Use [Master]GO/** * * * object:storedprocedure [dbo]. [Sp_who_lock] Script date:01/03/2017 10:58:03 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGOALTER procedure [dbo].[Sp_who_lock] asbeginDeclare @spid int,@bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int Create Table#tmp_lock_who (IDint Identity(1,1), spidsmallint, BLsmallint) IF @ @ERROR<>0 RETURN @ @ERROR Insert into#tmp_lock_who (SPID,BL)Select 0, blocked from(Select * fromsysprocesseswhereBlocked>0) Awhere not exists(Select * from(Select * fromsysprocesseswhereBlocked>0) bwhereA.blocked=spid)Union SelectSpid,blocked fromsysprocesseswhereBlocked>0 IF @ @ERROR<>0 RETURN @ @ERROR --number of records found for temporary table Select @intCountProperties = Count(*),@intCounter = 1 from#tmp_lock_whoIF @ @ERROR<>0 RETURN @ @ERROR if @intCountProperties=0 Select 'No blocking and deadlock information now' asmessage--Cycle Start while @intCounter <= @intCountPropertiesbegin--take the first record Select @spid =spid@bl =BL from#tmp_lock_whowhereId= @intCounter begin if @spid =0 Select 'causing the database deadlock is:'+ CAST(@bl as VARCHAR(Ten))+ 'process number, which executes the following SQL syntax' Else 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' DBCCInputBuffer (@bl ) End --The loop pointer moves down Set @intCounter = @intCounter + 1EndDrop Table#tmp_lock_whoreturn 0End
The deadlock process ID can be detected by the above and the kill deadlock process ID can be executed (kill 63)
Database query super Slow, database deadlock view and resolution