SQL Server query and Kill deadlock statement, sqlserverkill

SQL Server query and Kill deadlock statement, sqlserverkill

Query deadlock process statements

select request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'

Statement used to kill a deadlock

kill spid

Next, I will share with you a piece of information about SQL Server Deadlock Detection, killing locks and processes, and viewing lock information.

-- Check deadlocks -- if a deadlock occurs, how can we detect the specific SQL statement or stored procedure in which a deadlock occurs? -- In this case, we can use the following stored procedure to detect the process and SQL statements that cause the deadlock. The built-in system stored procedures sp_who and sp_lock of SQL Server can also be used to find blocking and deadlocks, but the method described here is not easy to use. Use mastergocreate procedure sp_who_lockasbegindeclare @ spid int, @ bl int, @ intTransactionCountOnEntry int, @ intRowcount int, @ intCountProperties int, @ intCounter int create table # tmp_lock_who (id int identity (1,1 ), spid smallint, bl smallint) IF @ ERROR <> 0 RETURN @ ERROR insert into # tmp_lock_who (spid, bl) select 0, blocked from (select * from sysprocesses where blocked> 0) a where not exists (sele Ct * from (select * from sysprocesses where blocked> 0) B where. blocked = spid) union select spid, blocked from sysprocesses where blocked> 0 IF @ ERROR <> 0 RETURN @ ERROR -- find the number of records in the temporary table select @ intCountProperties = Count (*), @ intCounter = 1 from # tmp_lock_who IF @ ERROR <> 0 RETURN @ ERROR if @ intCountProperties = 0 select 'no blocking and deadlock information' as message -- the loop starts while @ intCounter <= @ intCountPropertiesbegin -- obtain the first entry Select @ spid = spid, @ bl = bl from # tmp_lock_who where Id = @ intCounter begin if @ spid = 0 select ', which causes a database deadlock: '+ CAST (@ bl as varchar (10) +' process number. The SQL syntax is AS follows: 'else select' process ID: '+ CAST (@ spid as varchar (10) +' blocked by '+' process number SPID: '+ CAST (@ bl as varchar (10) +, the SQL syntax executed by the current process is as follows: 'dbcc INPUTBUFFER (@ bl) end -- move the loop pointer down set @ intCounter = @ intCounter + 1 enddrop table # tmp_lock_whoreturn 0end -- killing locks and processes -- How to manually Killing processes and locks? The simplest way is to restart the service. But here we will introduce a stored procedure. through explicit calling, the process and lock can be killed. Use mastergoif exists (select * from dbo. sysobjects where id = object_id (n' [dbo]. [p_killspid] ') and OBJECTPROPERTY (id, n' IsProcedure') = 1) drop procedure [dbo]. [p_killspid] GOcreate proc p_killspid @ dbname varchar (200) -- Name of the database to shut down the process as declare @ SQL nvarchar (500) declare @ spid nvarchar (20) declare # tb cursor for select spid = cast (spid as varchar (20) from master .. sysprocesses where dbid = db_id (@ dbname) op En # tb fetch next from # tb into @ spid while @ fetch_status = 0 begin exec ('Kill '+ @ spid) fetch next from # tb into @ spid end close # tb deallocate # tbgo -- usage exec p_killspid 'newdbpy' -- View lock information -- how to view details of all locks in the system? In the enterprise management manager, we can see some process and lock information. Here we introduce another method. -- View the lock information create table # t (req_spid int, obj_name sysname) declare @ s nvarchar (4000), @ rid int, @ dbname sysname, @ id int, @ objname sysnamedeclare tb cursor for select distinct req_spid, dbname = db_name (rsc_dbid), rsc_objid from master .. syslockinfo where rsc_type in (4, 5) open tbfetch next from tb into @ rid, @ dbname, @ idwhile @ fetch_status = 0 begin set @ s = 'select @ objname = name from ['+ @ dbname +'] .. sysobjects where id = @ id' exec sp_executesql @ s, n' @ objname sysname out, @ id int ', @ objname out, @ id insert into # t values (@ rid, @ objname) fetch next from tb into @ rid, @ dbname, @ idendclose tbdeallocate tbselect process id =. req_spid, database = db_name (rsc_dbid), type = case rsc_type when 1 then 'null Resource (not used) 'When 2 then' database 'when 3 then' file 'when 4 then' index 'when 5 then' table 'when 6 then' page 'when 7 then' key 'when 8 then 'Extended disk partition 'when 9 then' RID (row ID) 'When 10 then' application 'end, Object id = rsc_objid, object name = B. obj_name, rsc_indid from master .. syslockinfo a left join # t B on. req_spid = B. req_spidgodrop table # t

The above is a small series of SQL Server query and Kill process deadlock statements, I hope to help you, if you have any questions, please leave a message, the small series will reply to you in a timely manner. Thank you very much for your support for the help House website!