Find out session ID of blocked processes and kill it

最新推荐文章于 2023-04-11 18:21:05 发布

Burgess_Liu

最新推荐文章于 2023-04-11 18:21:05 发布

阅读量1.1k

点赞数

分类专栏： SQL SERVER 文章标签： session kill sql cmd

本文链接：https://blog.csdn.net/burgess_liu/article/details/6302155

版权

SQL SERVER 专栏收录该内容

92 篇文章 2 订阅

订阅专栏

Find out session ID with below script:

select spid 被锁进程ID,
blocked 锁进程ID,
status 被锁状态,
SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号,
SUBSTRING(hostname,1,12) 被锁进程用户机器名称,
SUBSTRING(DB_NAME(a.dbid),1,10) 被锁进程数据名称,
cmd 被锁进程命令,
waittype 被锁进程等待类型,b.text SQL
FROM master..sysprocesses a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE blocked>0 AND spid<>blocked

Kill session ID:

Kill <session ID>

查询锁源：

select spid 被锁进程ID, blocked 锁进程ID,  status 被锁状态,  SUBSTRING(SUSER_SNAME(sid),1,30) 被锁进程登陆帐号, 
 SUBSTRING(hostname,1,12) 被锁进程用户机器名称,  SUBSTRING(DB_NAME(a.dbid),1,10) 被锁进程数据名称, 
 cmd 被锁进程命令,  b.text SQL  --,waittype 被锁进程等待类型
FROM master..sysprocesses  a 
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
--WHERE blocked>0 AND spid<>blocked 
WHERE blocked=0 AND spid IN(SELECT blocked FROM master..sysprocesses WHERE blocked>0 AND spid<>blocked)

查询被锁对象：

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

查询等待类型：

SELECT wait_type ,SUM(wait_time_ms / 1000) AS [wait_time_s]
FROM sys.dm_os_wait_stats DOWS
WHERE wait_type NOT IN ( 'SLEEP_TASK', 'BROKER_TASK_STOP','SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT','CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP' )
GROUP BY wait_type ORDER BY SUM(wait_time_ms) DESC