sql server解锁及查看连接数

--spid:死锁的进程,tableName :死锁的表
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id)tableName from sys.dm_tran_locks
where resource_type='OBJECT'

--spid:要结束的进程id
kill 78

 

 

存储过程方法：

CREATE procedure sp_who_lock as begin declare @spid int declare @blk int declare @count int declare @index int declare @lock tinyint set @lock=0 create table #temp_who_lock ( id int identity(1,1), spid int, blk int ) if @@error<>0 return @@error insert into #temp_who_lock(spid,blk) select 0 ,blocked from (select * from master..sysprocesses where blocked>0)a where not exists(select * from master..sysprocesses where a.blocked =spid and blocked>0) union select spid,blocked from master..sysprocesses where blocked>0 if @@error<>0 return @@error select @count=count(*),@index=1 from #temp_who_lock if @@error<>0 return @@error if @count=0 begin select '没有阻塞和死锁信息' return 0 end while @index<=@count begin if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid)) begin set @lock=1 select @spid=spid,@blk=blk from #temp_who_lock where id=@index select '引起数据库死锁的是: '+ CAST(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' select @spid, @blk dbcc inputbuffer(@spid) dbcc inputbuffer(@blk) end set @index=@index+1 end if @lock=0 begin set @index=1 while @index<=@count begin select @spid=spid,@blk=blk from #temp_who_lock where id=@index if @spid=0 select '引起阻塞的是:'+cast(@blk as varchar(10))+ '进程号,其执行的SQL语法如下' else select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下' dbcc inputbuffer(@spid) dbcc inputbuffer(@blk) set @index=@index+1 end end drop table #temp_who_lock return 0 end GO

连接数

--指定数据库的连接数 SELECT * FROM  [Master].[dbo].[SYSPROCESSES] WHERE [DBID] IN ( SELECT     [DBID]  FROM     [Master].[dbo].[SYSDATABASES]  WHERE     NAME='dbname'  ) --所有数据库（指定登录用户）SP_WHO 'sa'

SELECT     DB_NAME(dbid) as DBName,     COUNT(dbid) as NumberOfConnections,    loginame as LoginNameFROM    sys.sysprocessesWHERE     dbid > 0 GROUP BY     dbid, loginame;

数据库还原出现独占的情况：

Select * from master..sysdatabases where name = 'dbname';
 
Select * from sys.sysprocesses a where a.dbid = '8'
Kill 54