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

2024-01-02 20:14:51

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.显示创建成功在这里插入图片描述

文章来源:https://blog.csdn.net/lovemelovefish/article/details/135340122
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。