Sql Server进程阻塞导致查询无响应怎么办？亲测有效

1、在sql查询分析器中，找出阻塞的进程：

select t1.blocked as '阻塞的进程ID',
    OBJECT_NAME(t2.resource_associated_entity_id) as '阻塞的表',
    t3.hostname as '阻塞进程-hostname',
    t3.nt_domain as '阻塞进程-nt_domain',
    t3.nt_username as '阻塞进程-nt_username',
    t3.loginame as '阻塞进程-loginame'
from master..sysprocesses t1
inner join sys.dm_tran_locks t2 on t1.blocked = t2.request_session_id and t2.resource_type = 'OBJECT'
inner join master..sysprocesses t3 on t1.blocked = t3.spid
where t1.blocked > 0

2、根据阻塞的进程ID查询阻塞的sql语句代码。如进程ID为80。

DBCC INPUTBUFFER (80)

3、杀死阻塞进程

kill 80

附：查看sql连接数

select * from master.dbo.sysprocesses