WEB开发网
开发学院数据库MSSQL Server 终止SQL Server中的用户进程方法 阅读

终止SQL Server中的用户进程方法

 2012-12-01 11:45:50 来源:WEB开发网   
核心提示:set @killcmd='Kill '+CONVERT(varchar(100),@tempvar2)exec (@killcmd)endelsebeginPrint 'Cannot kill the SPID ' +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

图一

上一页  1 2 

Tags:终止 SQL Server

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接