February 2, 2005 at 11:57 am
Does anyone have a script that checks all processes running on a server and kills all connections to a particular database. any help will be greatly appreciated.
TIA
February 2, 2005 at 12:44 pm
This script will kill all users in every database. You can refine it by putting a where clause in the 'LoginCursor' declaration.
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE cp_KillUsers
@dbname varchar(50),
@hostname varchar(100) = null
as
/** Ref to
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=15138&post=true
This kills users of a given database. You need to be able to go into single-user to
run some DBCC Checks. **/
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(100), blk int, dbname varchar(50),
cmd varchar(30))
INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR READ_ONLY
FOR SELECT spid, dbname,hostname
FROM #tmpUsers WHERE upper(dbname) = upper(@dbname)
AND SPID > 50
AND SPID @@spid
exec ('select * from #tmpUsers')
DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
DECLARE @userhost varchar(100)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2, @userhost
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN if @hostname is null
begin
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
end
else
if @userhost is not null and @hostname = @userhost
begin
PRINT 'Killing ' + @spid
SET @strSQL = 'KILL ' + @spid
EXEC (@strSQL)
end
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2, @userhost
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----------------
Jim P.
A little bit of this and a little byte of that can cause bloatware.
February 3, 2005 at 6:05 am
Steve Jones added this script ot the library, and I use it occasionally;
it kills any spids that have been inactive longer than the # of seconds parameter you pass the procedure:
if object_Id( 'dbspKillIdleSpids') Is Not Null
drop procedure dbspKillIdleSpids
go
CREATE procedure dbspKillIdleSpids
@sec int = Null
as
/*
*************************************************************
Name: dbspKillIdleSpids
Description: kills connections that have been
inactive for @sec seconds.
Usage: exec dbspKillIdleSpids <sec>
Author: Steve Jones - http://www.dkranch.net
Input Params:
-------------
@sec int. defaults to Null, # seconds for connection to be
idle to kill it.
Output Params:
--------------
Return: 0, no error. Raises error if no parameters sent in.
Results:
---------
Locals:
--------
Modifications:
--------------
*************************************************************
*/
declare @err int,
@spid int,
@cmd char( 100)
if @sec Is Null
begin
raiserror( 'Usage:exec dbspKillIdleSpids <sec>', 12, 1)
return -1
end
declare u_curs scroll insensitive cursor for
select s.spid
from master..sysprocesses s
where ( datediff( ss, s.last_batch, getdate())) > @sec
open u_curs
fetch next from u_curs into @spid
while @@fetch_status = 0
begin
select @cmd = 'kill ' + convert( char( 4), @spid)
print @cmd
fetch next from u_curs into @spid
end
deallocate U_curs
return
GO
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply