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 LoginName
FROM
    sys.sysprocesses
WHERE 
    dbid > 0 
GROUP BY 
    dbid, loginame
;

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

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

Kill 54