–查看当前数据库中的所有的锁及锁住的表名称(spid即为锁对应的进程)
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type=’OBJECT’
–查看当前数据库中的所有的阻塞进程
select * from master..sysprocesses where blocked >0
–根据具体的进程号查询对应的ip地址
select client_net_address ip地址,* from sys.dm_exec_connections where session_id =59
–根据具体的进程号查询对应的计算机名以及登陆方式
select hostname 计算机名,net_address 物理地址,program_name 登陆方式,* from master..sysprocesses where spid =59
–结束一个进程（解锁）
kill 323
或者
declare @spid int
Set @spid = 283 –锁表进程
declare @sql varchar(1000)
set @sql=’kill ‘+cast(@spid as varchar)
exec(@sql)
–如果锁表的进程非常多，则需要批量解锁（注意：会同时结束正常的业务操作，一般在数据库死锁后使用）
–批量循环解锁
–commit;
DECLARE @spid int
DECLARE CUR CURSOR
FOR select request_session_id spid from sys.dm_tran_locks where resource_type=’OBJECT’
OPEN CUR
–SELECT spid FROM sysprocesses WHERE dbid = 5
FETCH NEXT FROM CUR INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
–commit;
EXEC (‘KILL ‘ + @spid )
FETCH NEXT FROM CUR INTO @spid
END
CLOSE CUR
DEALLOCATE CUR
–commit;