SQL数据库死锁语句查询结果

1.查询语句

  SELECT login_time,last_batch,spid, blocked,object_name(C.resource_associated_entity_id) as TABLENAME,sp.dbid, DB_NAME(sp.dbid) AS DBName, 
	       program_name,waitresource,lastwaittype,sp.loginame,sp.hostname,
		   a.[Text] AS [TextData],
	      SUBSTRING ( A. TEXT,sp.stmt_start / 2,(CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd] 
	FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
	WHERE spid > 50  AND blocked >0   AND request_session_id =spid AND resource_type = 'OBJECT' 
	ORDER BY blocked DESC,
	DB_NAME(sp.dbid) ASC,
	a.[text];

2.创建一个表A_LOCK_LOG，用于记录查询的结果，可以利用SQL的定时作业来记录清空

INSERT INTO A_LOCK_LOG(login_time,last_batch,spid,blocked,TABLENAME,dbid,DBName,program_name,waitresource,lastwaittype,loginame,hostname,TextData,current_cmd)
   SELECT login_time,last_batch,spid, blocked,object_name(C.resource_associated_entity_id) as TABLENAME,sp.dbid, DB_NAME(sp.dbid) AS DBName, 
	       program_name,waitresource,lastwaittype,sp.loginame,sp.hostname,
		   a.[Text] AS [TextData],
	      SUBSTRING ( A. TEXT,sp.stmt_start / 2,(CASE WHEN sp.stmt_end = - 1 THEN DATALENGTH (A. TEXT) ELSE sp.stmt_end END - sp.stmt_start) / 2) AS [current_cmd] 
	FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A ,sys.dm_tran_locks C
	WHERE spid > 50  AND blocked >0   AND request_session_id =spid AND resource_type = 'OBJECT' 
	ORDER BY blocked DESC,
	DB_NAME(sp.dbid) ASC,
	a.[text];

3.显示创建成功