/*--处理死锁 查看当前进程,或死锁进程,并能自动杀掉死进程 因为是针对死的,所以如果有死锁进程,只能查看死锁进程 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4--*//*--调用示例 exec p_lockinfo --*/ create proc p_lockinfo @kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示 @show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示 as declare @count int,@s nvarchar(1000),@i int select id=identity(int,1,1),标志, 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid, 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu, 登陆时间=login_time,打开事务数=open_tran, 进程状态=status, 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess, 域名=nt_domain,网卡地址=net_address into #t from( select 标志='死锁的进程', spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=a.spid,s2=0 from master..sysprocesses a join ( select blocked from master..sysprocesses group by blocked )b on a.spid=b.blocked where a.blocked=0 union all select '|_牺牲品_>', spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran, status,hostname,program_name,hostprocess,nt_domain,net_address, s1=blocked,s2=1 from master..sysprocesses a where blocked<>0 )a order by s1,s2select @count=@@rowcount,@i=1if @count=0 and @show_spid_if_nolock=1 begin insert #t select 标志='正常的进程', spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master..sysprocesses set @count=@@rowcount endif @count>0 begin create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255)) if @kill_lock_spid=1 begin declare @spid varchar(10),@标志 varchar(10) while @i<=@count begin select @spid=进程ID,@标志=标志 from #t where id=@i insert #t1 exec('dbcc inputbuffer('+@spid+')') if @标志='死锁的进程' exec('kill '+@spid) set @i=@i+1 end end else while @i<=@count begin select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i insert #t1 exec(@s) set @i=@i+1 end select a.*,进程的SQL语句=b.EventInfo from #t a join #t1 b on a.id=b.id end go
其实就是个KILLKILL 根据系统进程 ID (SPID) 终止用户进程。如果指定的 SPID 或 UOW 有许多工作要取消,KILL 命令可能需要一些时间来完成,特别是在涉及回滚长事务时。在 Microsoft® SQL Server™ 2000 中,KILL 命令可用来终止正常连接,这将在内部终止与给定 SPID 关联的事务。另外,在使用 Microsoft 分布式事务处理协调器 (MS DTC) 时,该命令也可用于终止所有孤立的分布式事务。当分布式事务未与任何当前 SPID 关联时,即为孤立的。语法 KILL {spid | UOW} [WITH STATUSONLY] 参数 spid是要终止进程的系统进程 ID (SPID)。SPID 值是当用户进行连接时指派给该连接的一个唯一的整数 (smallint),但该指派不是永久的。使用 KILL spid 终止与给定 SPID 相关联的常规非分布式和分布式事务。UOW标识 DTC 事务的工作单元 ID (UOW)。UOW 是一个字符串,可从 syslockinfo 表中获得,该表把 UOW 赋予每个由 DTC 事务控制的锁。UOW 也可从错误日志中获得,或通过 DTC 监视器获得。有关监视分布式事务的更多信息,请参见"MS DTC 用户手册"。使用 KILL UOW 终止孤立的 DTC 事务,这些事务不与任何真实的 SPID 关联,而是手工与 SPID = '-2' 关联。有关 SPID = '-2' 的更多信息,请参见本主题后面的注释部分。WITH STATUSONLY指定 SQL Server 生成关于正在回滚的给定 spid 或 UOW 的进度报告。带 WITH STATUSONLY 的 KILL 命令不终止或回滚 spid 或 UOW,而只显示当前进度报告。若要使带 WITH STATUSONLY 选项的 KILL 命令成功地生成报告,spid 或 UOW 当前必须处于回滚状态。进度报告指出已完成的回滚量(按百分比计算)和估计的剩余时间(以秒为单位),格式如下:Spid|UOW <xxx>: Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.如果执行带 WITH STATUSONLY 选项的 KILL 命令时 spid 或 UOW 回滚已经结束,或者如果没有 spid 或 UOW 正在回滚,带 WITH STATUSONLY 的 KILL 命令将返回下列错误:Status report cannot be obtained. KILL/ROLLBACK operator for Process ID|UOW <xxx> is not in progress.执行两次不带 WITH STATUSONLY 选项的 KILL spid|UOW 命令可得到同样的状态报告;但不建议这样做。第二次执行命令可能会终止已指派给释放的 SPID 的新进程。注释 KILL 命令通常用于终止这样一些进程,它们锁住了其它重要进程,或者正在执行一个查询,而该查询正在使用必需的系统资源。系统进程和运行扩展存储过程的进程不能被终止。应当小心使用 KILL 命令,特别是正在运行重要进程时。用户不能取消自己的进程。其它不能取消的进程包括: AWAITING COMMAND CHECKPOINT SLEEP LAZY WRITER LOCK MONITOR SELECT SIGNAL HANDLER 执行 sp_who 可得到有效 SPID 值的报告。如果某个特定的 SPID 的回滚正在进行,则 sp_who 结果集中的这个 SPID 的 cmd 列将显示"KILLED/ROLLBACK"。使用 @@SPID 可显示当前会话的 SPID 值。 在 SQL Server 2000 中,KILL 命令可用于解决与非分布式和分布式事务相关联的 SPID。KILL 还可用于解决孤立的或置疑的分布式事务。当分布式事务未与任何当前 SPID 关联时,即为孤立的。 SPID 值为 '-2' 用于指示无连接或孤立的事务。SQL Server 将该值指派给所有孤立的分布式事务,使在 sp_lock(spid 列)、sp_who(blk 列)、syslockinfo 和 sysprocesses 中标识这样的事务更容易。当特定的连接在数据库资源上有锁并阻塞事务进程时,该功能十分有用。用户能够识别拥有锁的 SPID,并结束连接。KILL 命令可用来解决怀疑有问题的事务,即由于未计划重新启动数据库服务器或 DTC 处理协调器而产生的未解决的分布式事务。有关解决怀疑有问题的事务的更多信息,请参见 MS DTC 事务疑难解答。权限 默认情况下,sysadmin 和 processadmin 固定数据库角色的成员具有 KILL 的默认权限,KILL 权限不可转让。示例 A. 使用 KILL 终止 SPID 下面的示例显示如何终止 SPID 53。KILL 53B. 使用 KILL spid WITH STATUSONLY 获得进度报告。 下面的示例为特定的 spid 生成回滚进程的状态。KILL 54 KILL 54 WITH STATUSONLY--This is the progress report. spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.C. 使用 KILL 终止孤立的分布式事务。 下例说明如何使用 UOW = D5499C66-E398-45CA-BF7E-DC9C194B48CF 终止孤立 (SPID = -2) 事务。KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
/* 关闭用户打开的进程,再分离即可 */ use master go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_killspid] GO
declare #tb cursor for select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname) open #tb fetch next from #tb into @spid while @@fetch_status=0 begin exec('kill '+@spid) fetch next from #tb into @spid end close #tb deallocate #tb go
--用法 exec p_killspid '数据库名'
use master go create proc killspid(@dbname varchar(20)) as begin declare @sql nvarchar(500),@temp varchar(1000) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end 用法: use master exec killspid 'temp' --temp为数据库名称
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) AS SET NOCOUNT ON DECLARE @strSQL varchar(255) PRINT 'Killing Users' PRINT '-----------------' CREATE TABLE #tmpUsers ( spid int, eid int, status varchar(30), loginname varchar(50), hostname varchar(50), blk int, dbname varchar(50), cmd varchar(30), request_id int ) INSERT INTO #tmpUsers EXEC SP_WHO DECLARE LoginCursor CURSOR READ_ONLY FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10) DECLARE @dbname2 varchar(40) OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 WHILE (@@fetch_status <> -1) BEGIN IF ( @@fetch_status <> -2 ) BEGIN PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid EXEC (@strSQL) END FETCH NEXT FROM LoginCursor INTO @spid, @dbname2 END CLOSE LoginCursor DEALLOCATE LoginCursor DROP TABLE #tmpUsers go --EXEC usp_KillUsers YOURDBNAME
当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程--邹建 2004.4--*//*--调用示例 exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1, --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
域名=nt_domain,网卡地址=net_address
into #t from(
select 标志='死锁的进程',
spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=a.spid,s2=0
from master..sysprocesses a join (
select blocked from master..sysprocesses group by blocked
)b on a.spid=b.blocked where a.blocked=0
union all
select '|_牺牲品_>',
spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
status,hostname,program_name,hostprocess,nt_domain,net_address,
s1=blocked,s2=1
from master..sysprocesses a where blocked<>0
)a order by s1,s2select @count=@@rowcount,@i=1if @count=0 and @show_spid_if_nolock=1
begin
insert #t
select 标志='正常的进程',
spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
from master..sysprocesses
set @count=@@rowcount
endif @count>0
begin
create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
if @kill_lock_spid=1
begin
declare @spid varchar(10),@标志 varchar(10)
while @i<=@count
begin
select @spid=进程ID,@标志=标志 from #t where id=@i
insert #t1 exec('dbcc inputbuffer('+@spid+')')
if @标志='死锁的进程' exec('kill '+@spid)
set @i=@i+1
end
end
else
while @i<=@count
begin
select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
insert #t1 exec(@s)
set @i=@i+1
end
select a.*,进程的SQL语句=b.EventInfo
from #t a join #t1 b on a.id=b.id
end
go
根据系统进程 ID (SPID) 终止用户进程。如果指定的 SPID 或 UOW 有许多工作要取消,KILL 命令可能需要一些时间来完成,特别是在涉及回滚长事务时。在 Microsoft® SQL Server™ 2000 中,KILL 命令可用来终止正常连接,这将在内部终止与给定 SPID 关联的事务。另外,在使用 Microsoft 分布式事务处理协调器 (MS DTC) 时,该命令也可用于终止所有孤立的分布式事务。当分布式事务未与任何当前 SPID 关联时,即为孤立的。语法
KILL {spid | UOW} [WITH STATUSONLY] 参数
spid是要终止进程的系统进程 ID (SPID)。SPID 值是当用户进行连接时指派给该连接的一个唯一的整数 (smallint),但该指派不是永久的。使用 KILL spid 终止与给定 SPID 相关联的常规非分布式和分布式事务。UOW标识 DTC 事务的工作单元 ID (UOW)。UOW 是一个字符串,可从 syslockinfo 表中获得,该表把 UOW 赋予每个由 DTC 事务控制的锁。UOW 也可从错误日志中获得,或通过 DTC 监视器获得。有关监视分布式事务的更多信息,请参见"MS DTC 用户手册"。使用 KILL UOW 终止孤立的 DTC 事务,这些事务不与任何真实的 SPID 关联,而是手工与 SPID = '-2' 关联。有关 SPID = '-2' 的更多信息,请参见本主题后面的注释部分。WITH STATUSONLY指定 SQL Server 生成关于正在回滚的给定 spid 或 UOW 的进度报告。带 WITH STATUSONLY 的 KILL 命令不终止或回滚 spid 或 UOW,而只显示当前进度报告。若要使带 WITH STATUSONLY 选项的 KILL 命令成功地生成报告,spid 或 UOW 当前必须处于回滚状态。进度报告指出已完成的回滚量(按百分比计算)和估计的剩余时间(以秒为单位),格式如下:Spid|UOW <xxx>: Transaction rollback in progress. Estimated rollback completion: yy% Estimated time left: zz seconds.如果执行带 WITH STATUSONLY 选项的 KILL 命令时 spid 或 UOW 回滚已经结束,或者如果没有 spid 或 UOW 正在回滚,带 WITH STATUSONLY 的 KILL 命令将返回下列错误:Status report cannot be obtained. KILL/ROLLBACK operator for Process ID|UOW <xxx> is not in progress.执行两次不带 WITH STATUSONLY 选项的 KILL spid|UOW 命令可得到同样的状态报告;但不建议这样做。第二次执行命令可能会终止已指派给释放的 SPID 的新进程。注释
KILL 命令通常用于终止这样一些进程,它们锁住了其它重要进程,或者正在执行一个查询,而该查询正在使用必需的系统资源。系统进程和运行扩展存储过程的进程不能被终止。应当小心使用 KILL 命令,特别是正在运行重要进程时。用户不能取消自己的进程。其它不能取消的进程包括: AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
执行 sp_who 可得到有效 SPID 值的报告。如果某个特定的 SPID 的回滚正在进行,则 sp_who 结果集中的这个 SPID 的 cmd 列将显示"KILLED/ROLLBACK"。使用 @@SPID 可显示当前会话的 SPID 值。 在 SQL Server 2000 中,KILL 命令可用于解决与非分布式和分布式事务相关联的 SPID。KILL 还可用于解决孤立的或置疑的分布式事务。当分布式事务未与任何当前 SPID 关联时,即为孤立的。 SPID 值为 '-2' 用于指示无连接或孤立的事务。SQL Server 将该值指派给所有孤立的分布式事务,使在 sp_lock(spid 列)、sp_who(blk 列)、syslockinfo 和 sysprocesses 中标识这样的事务更容易。当特定的连接在数据库资源上有锁并阻塞事务进程时,该功能十分有用。用户能够识别拥有锁的 SPID,并结束连接。KILL 命令可用来解决怀疑有问题的事务,即由于未计划重新启动数据库服务器或 DTC 处理协调器而产生的未解决的分布式事务。有关解决怀疑有问题的事务的更多信息,请参见 MS DTC 事务疑难解答。权限
默认情况下,sysadmin 和 processadmin 固定数据库角色的成员具有 KILL 的默认权限,KILL 权限不可转让。示例
A. 使用 KILL 终止 SPID
下面的示例显示如何终止 SPID 53。KILL 53B. 使用 KILL spid WITH STATUSONLY 获得进度报告。
下面的示例为特定的 spid 生成回滚进程的状态。KILL 54
KILL 54 WITH STATUSONLY--This is the progress report.
spid 54: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.C. 使用 KILL 终止孤立的分布式事务。
下例说明如何使用 UOW = D5499C66-E398-45CA-BF7E-DC9C194B48CF 终止孤立 (SPID = -2) 事务。KILL 'D5499C66-E398-45CA-BF7E-DC9C194B48CF'
关闭用户打开的进程,再分离即可
*/
use master
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GO
create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
--用法
exec p_killspid '数据库名'
go
create proc killspid(@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status<>-1
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
用法:
use master
exec killspid 'temp' --temp为数据库名称
CREATE PROCEDURE usp_KillUsers @dbname varchar(50) AS
SET NOCOUNT ON DECLARE @strSQL varchar(255)
PRINT 'Killing Users' PRINT '-----------------'
CREATE TABLE #tmpUsers
(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30),
request_id int
)
INSERT INTO #tmpUsers
EXEC SP_WHO
DECLARE LoginCursor CURSOR READ_ONLY
FOR
SELECT spid,
dbname
FROM #tmpUsers
WHERE dbname = @dbname
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (
@@fetch_status <> -2
)
BEGIN
PRINT 'Killing ' + @spid SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END CLOSE LoginCursor DEALLOCATE LoginCursor
DROP TABLE #tmpUsers
go --EXEC usp_KillUsers YOURDBNAME