【sqlserver】sqlserver锁与解锁;查询锁表的进程和计算机机器名ip
2014-01-10 14:06:46 from—http://libao2235.blog.51cto.com/407124/1350377

–查看当前数据库中的所有的锁及锁住的表名称(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;

上一篇
下一篇