如何终止SQL Server中的用户进程

 我来答
谭覃
推荐于2017-09-06 · 大连七彩云网络科技有限公司计算机系统专家
谭覃
采纳数:1383 获赞数:3623

向TA提问 私信TA
展开全部
首先,我们在主数据库中创建“KILL2”这个进程,代码如下所示(参考图一):

USE [master]
GO
IF EXISTS (SELECT * FROM master.dbo.sysobjects
WHERE id = OBJECT_ID(N'[kill2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[kill2]
GO
--Usage1: Kill2 '51-57' --> Kills all the session IDs from 51 to 57
--Usage2: Kill2 '58' --> Kills the session IDs 58
--Usage3: Kill2 '肢嫌51,56,100,58'
--> Kills the session IDs 51,56,100 and 58
--Usage4: Kill2 'DB=MyDatabase'
-->搜饥数 Kills all the session IDs that are connected
to the database "MyDatabase"
use master
go
set concat_null_yields_null off
go
create procedure kill2 @param2 varchar(500)
as
--declare @param2 varchar(500)
declare @param varchar(500)
declare @startcount int
declare @killcmd varchar(100)
declare @endcount int
declare @spid int
declare @spid2 int
declare @tempvar varchar(100)
declare @tempvar2 varchar(100)
--set @param2 ='54'
set @param=REPLACE(@param2,' ','')
if CHARINDEX('-',@param) <> 0
begin
select @startcount= convert(int,SUBSTRING(@param,1,charindex('-',@param)-1))
select @endcount=convert(int,SUBSTRING(@param,charindex('-',@param)+1,(LEN(@param)-charindex('-',@param))))
print 'Killing all SPIDs from ' + convert(varchar(100),@startcount)+' to ' +convert(varchar(100),@endcount)
while @startcount <=@endcount
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=@startcount and spid>50)
if @spid = @startcount
begin
print 'Killing '世首+convert(varchar(100),@startcount)
set @killcmd ='Kill '+convert(varchar(100),@startcount)
exec(@killcmd)
end
else
begin
Print 'Cannot kill the SPID ' +convert(varchar(100),@startcount) + ' because it does not Exist'
end
set @startcount=@startcount + 1
end
end
if CHARINDEX(',',@param) <> 0
begin
set @tempvar =@param
while charindex(',',@tempvar ) <> 0
begin
SET @tempvar2=left(@tempvar,charindex(',',@tempvar)-1)
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar2) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar2)
begin
print 'Killing '+CONVERT(varchar(100),@tempvar2)
set @killcmd='Kill '+CONVERT(varchar(100),@tempvar2)
exec (@killcmd)

end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@tempvar2) + ' because it does not Exist'
end
set @tempvar =REPLACE(@tempvar,left(@tempvar,charindex(',',@tempvar)),'')
end
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar)
begin
print 'Killing '+CONVERT(varchar(100),@tempvar)
set @killcmd='Kill '+CONVERT(varchar(100),@tempvar)
exec (@killcmd)

end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@tempvar) + ' because it does not Exist'
end
end
if CHARINDEX('=',@param2) <>0
begin
print 'Killing all the SPIDs that are connected to the database '+RIGHT(@param2,(len(@param2)-3))
declare dbcursor
cursor forward_only for select SPID from master.dbo.sysprocesses where DB_NAME(dbid) = RIGHT(@param2,(len(@param2)-3))
open dbcursor
fetch dbcursor into @spid
while @@FETCH_STATUS =0
begin
set @spid2=(select spid from master.dbo.sysprocesses where spid=@spid and spid>50)
if @spid = @spid2 begin
print 'Killing '+CONVERT(varchar(100),@spid2)
set @killcmd='Kill '+CONVERT(varchar(100),@spid2)
exec (@killcmd)

end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@spid2) + ' because it does not Exist'
end
fetch dbcursor into @spid
end
close dbcursor
deallocate dbcursor
end
if CHARINDEX('-',@param)=0 and CHARINDEX(',',@param) = 0 and CHARINDEX('=',@param)=0
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@param) and spid>50)
if @spid = CONVERT(varchar(100),@param)
begin
print 'Killing '+CONVERT(varchar(100),@param)
set @killcmd='Kill '+CONVERT(varchar(100),@param)
exec (@killcmd)

end
else
begin
Print 'Cannot kill the SPID ' +CONVERT(varchar(100),@param) + ' because it does not Exist'
end
end
go
--kill2 '51'
--go
--kill2 '51-56'
--go
--kill2 '56,57,58,52'
--go
--kill2 'db=AdventureWorks2008'
--kill2 'db=My Database'
--go
--sp_who

   图一
现在,我们假设进程ID(SPID)为51、52、53、54、55、57这几个进程(见图二)连接到了SQL Server数据库,而我们只想把进程ID为54、55和57的进程结束掉。

  图二
  执行以下命令。注意,在这个例子当中还在命令中加入了其他几个SQL Server中不存在的SPID:61和100。 

use master
  go
  kill2 '54,57,55,61,100'
  go

  运行结果: 

Killing 54
  Killing 57
  Msg 6104, Level 16, State 1, Line 1
  Cannot use KILL to kill your own process.
  Cannot kill the SPID 55 because it does not Exist
  Cannot kill the SPID 61 because it does not Exist
  Cannot kill the SPID 100 because it does not Exist

   图三
  我们可以从结果(见图三)看到,执行指令后成功终止了SPID 54。当试图终止57时失败了。同时结果也显示了为什么没能终止特定SPID的信息
下面,假设我们有51、52、53、54、55、57、58、59和60这几个SPID,而我们的目标是结束SPID从25到70的进程。
  执行以下命令:  

use master
  go
  kill2 '25-75'
  go

  运行结果: 

Killing all SPIDs from 25 to 75
  Cannot kill the SPID 25 because it does not Exist
  …..
  Cannot kill the SPID 48 because it does not Exist
  Cannot kill the SPID 49 because it does not Exist
  Cannot kill the SPID 50 because it does not Exist
  Killing 51
  Killing 52
  Killing 53
  Killing 54
  Killing 55
  Cannot kill the SPID 56 because it does not Exist
  Killing 57
  Msg 6104, Level 16, State 1, Line 1
  Cannot use KILL to kill your own process.
  Killing 58
  Killing 59
  Killing 60
  Cannot kill the SPID 61 because it does not Exist
  .....
  Cannot kill the SPID 75 because it does not Exist

图四
  从结果(见图四)我们可以看到“KILL2”存储过程忽略了所有SPID小于50的连接,而结束了从51到70的所有进程。

接下来,假设我们要终结掉所有连接到数据库AdventureWorks2008的会话,同时又假设SPID为53、54、58和60的进程连接到了该数据库(见图五)。

  图五
  现在,我们执行以下的T-SQL语句结束掉所有这些会话。 

Use master
  go
  kill2 'db=AdventureWorks2008'
  go

  运行结果:

 Killing all the SPIDs that are connected to the database AdventureWorks2008
  Killing 53
  Killing 54
  Killing 58
  Killing 60

图六
  从结果(见图六)我们可以看到“KILL2”存储过程终止了所有连接到AdventureWorks2008数据库的会话。
  用法四
  “KILL2”存储过程的第四种用法类似于“KILL命令,也就是一次解决一个会话,如下所示: 

Use master
  go
  kill2 '56'
  go
 
本回答被提问者和网友采纳
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
踏足所有领域
2014-12-13 · 超过37用户采纳过TA的回答
知道小有建树答主
回答量:154
采纳率:0%
帮助的人:51.9万
展开全部
kill 进程iD
已赞过 已踩过<
你对这个回答的评价是?
评论 收起
收起 1条折叠回答
推荐律师服务: 若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询

为你推荐:

下载百度知道APP,抢鲜体验
使用百度知道APP,立即抢鲜体验。你的手机镜头里或许有别人想知道的答案。
扫描二维码下载
×

类别

我们会通过消息、邮箱等方式尽快将举报结果通知您。

说明

0/200

提交
取消

辅 助

模 式