need script to kill user SPIDS
-
here's the issue: when doing off-hours maintenance or code migration, the user community does not alway pay attention to notifications. inevitably, when it comes time to take a database away into restricted mode, there are always user SPIDS which prevent it.
rather than the time consuming task of using EM to right click/kill process over and over (and over and over and over and over....), I am able to query master and determine which SPID need to be zapped:
use master
select spid from sysprocesses where loginame <> the login names I need to keep running, such as sa and a coupla other custom apps.
my question is: how can I use this info in a script to kill them all at once? there's gotta be a script out there somewhere where all user SPIDs can be zapped, but the DBA can exlcude process SPIDs that need to be allowed to live.
-
You can kill the processes all at once using EM. If you right-click the DB and select Detach, you can clear all of the database connections there. If you do not want to Detach, click cancel after you clear your connections.
You are right, I'm sure someone will post a script to do it also.
-
pretty slick John, never thought of using detach. still, would be nice to have a script in my bag-o-tricks too.
I'll try the detach on DEV instance to get the feel of it. appreciate yer help.
-
search this site for USP_KillUsers, this script will give you what you want
-
I use this script:
declare @spid smallint, @k varchar(20), @@message varchar(255), @dbname varchar(10)
set @dbname = 'database_name'
if(@dbname is null) set @dbname = (select name from master..sysprocesses,master..sysdatabases where master..sysprocesses.dbid = master..sysdatabases.dbid and spid=@@spid)
declare who cursor for
select master..sysprocesses.spid
from master..sysprocesses, master..sysdatabases
where master..sysprocesses.dbid = master..sysdatabases.dbid
and master..sysdatabases.name = @dbname
open who
fetch next from who into @spid
while @@fetch_status = 0
begin
fetch next from who into @spid
set @k = 'kill ' + cast(@spid as char)
exec(@k)
set @@message = 'spid ' + cast(@spid as varchar) + ' killed by ' + (select net_address from master..sysprocesses where spid = @spid)
exec master..xp_logevent 50001, @@message, error
print @@message
end
close who
deallocate who
Let me know if this works!
-Marti
-
i stick this in master and use the command sp_kill dbname to kick my users out:
what is nice is that when called in a job or script, it is not allowed to kill it's own process, giving the script exclusive access.
--enhanced 02/04/2005 to also list hostname
CREATE PROCEDURE sp_Kill
@DBNAME VARCHAR(30)
--Stored procedure to Delete SQL Process
AS
BEGIN
SET NOCOUNT ON
DECLARE @SPID INT
DECLARE @STR NVARCHAR(50)
DECLARE @HOSTNAME NVARCHAR(50)
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100)
 
INSERT INTO #TMPLOG EXEC SP_WHO
IF @@ERROR <> 0 GOTO Error_Handle
DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG
WHERE DBNAME LIKE @DBNAME
OPEN CURPROCESSID
FETCH NEXT FROM CURPROCESSID INTO @SPID
SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID
IF @SPID IS NOT NULL
PRINT 'Spid Process Kill List For database: ' + @dbName
ELSE
PRINT 'NO Processes Exist to be killed on database ' + @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
IF @@ERROR <> 0 GOTO ERROR_HANDLE
FETCH NEXT FROM CURPROCESSID INTO @SPID
END
Error_Handle:
IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
drop table #tmpLog
SET NOCOUNT OFF
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell:
appreciate the script. my other question would be how can I exclude certain SPIDS from this, based on loginame from sysprocesses?
I would want to exlcude by "loginame <>'process #' and repeat this for each loginame I would want to preserve. would I put it here ??
DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG
WHERE DBNAME LIKE @DBNAME and loginame <>'process that need to live'
OPEN CURPROCESSID
or here??
FETCH NEXT FROM CURPROCESSID INTO @SPID
SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID
IF @SPID IS NOT NULL
and loginame <>'process that still needs to live'
apologies for being such a newbie, but this would help a lot.
thanx in advance.
-
declare @tab table (spid integer)
INSERT @tab
select spid FROM OpenRowset('SQLOLEDB', 'Server=(local);Trusted_Connection=yes',
'SET fmtonly OFF
exec master..sp_who2 ') A
where
dbname='DBASE'
and ProgramName='Microsoft (R) .NET Framework'
and hostname='devel'
and login<>'process that need to live'
declare @Spid varchar(5)
declare lcur cursor for
select spid from @tab
open lcur
fetch next from lcur into @Spid
while (@@fetch_status =0)
begin
declare @hd nvarchar(50)
SELECT @hd = 'KILL ' + @SPID
EXECute sp_executesql @hd
fetch next from lcur into @Spid
end
close lcur
deallocate lcur;
-
DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG
WHERE DBNAME LIKE @DBNAME and loginame NOT IN 'sa,login1,login2'
OPEN CURPROCESSID
-
usually I just alter database set single_user with rollback immediate.
Seems to do the job.
-
This is ABSOLUTELY the easiest way to do this.... I copied the script from the library here and while it stopped a lot of processes, it did NOT allow me to put the DB into single_user mode. Using rollback immediate did the trick, and saved a LOT of complication.
Thanks WangKhar!!!
Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982 -
I absolutely agree with the previous post about set single user. If you have to do this on a per database level then it is totally low rent.
I had an issue where I needed to kill users within a certain NT group. These were developers using QA to query live data. Sometimes they would leave transactions open, or leave a monster query running. We run this at 11pm each night to ensure no over night issues. Here is what I came up with.
CREATE PROCEDURE p_Admin_Kill_Users
@Group_Name Nvarchar(400)
AS
/*
The purpose of this procedure is to remove any active connections
associated with the NT group supplied from the server. This has been
requested in order to prevent users from running potentially damaging
scripts overnight
Parameters:
@Group_Name: This is an NT group name. The format of this string
should be 'Domain\Groupname' all users that are contained
within this group will be killed.
Example Usage: EXEC p_Admin_Kill_Users 'domain\group'
*/
SET NOCOUNT ON
/* Common Initialisation */
DECLARE @ReturnStatus int
DECLARE @Error int
DECLARE @spid int
DECLARE @UName varchar(100)
DECLARE @sql varchar(200)
SET @ReturnStatus = 0
SET @Error = 0
/* Start by creating a table to hold the resultset */
CREATE TABLE #Users ( UserID int identity,
UserName Nvarchar(300),
type varchar(10),
priviledge varchar(10),
Mapped_login_name varchar(300),
permission_path varchar(200)
)
SELECT @error = @@error
IF (@error 0)
BEGIN
RAISERROR ('Error %d executing stored procedure p_Admin_Kill_Users whilst creating #Users table.',16,1,@error)
GOTO ErrorProcessing
END
/* Now populate the table for the group provided */
INSERT INTO #Users
(UserName, type, priviledge, mapped_login_name, permission_path)
EXEC master..xp_logininfo @Group_Name, 'members'
SELECT @error = @@error
IF (@error 0)
BEGIN
RAISERROR ('Error %d executing stored procedure p_Admin_Kill_Users whilst populating #Users table.',16,1,@error)
GOTO ErrorProcessing
END
/* Declare a cursor for each user, and each of their spids */
DECLARE spid_cursor CURSOR FOR
SELECT sp.spid, RTRIM(sp.nt_domain) + '\' + sp.nt_username as UName
FROM master..sysprocesses sp
INNER JOIN #Users U
ON RTRIM(sp.nt_domain) + '\' + sp.nt_username = u.UserName
WHERE sp.spid @@spid -- except for this user's spid. (just in case)
OPEN spid_cursor
FETCH NEXT FROM spid_cursor INTO @spid, @Uname
WHILE @@FETCH_STATUS = 0
BEGIN
/* Now Kill each spid */
PRINT 'Killing: ' + RTRIM(@Uname) + '(' + CAST(@spid as varchar(4)) + ')'
SET @sql = 'kill ' + CAST(@spid as varchar(4))
--PRINT @sql
EXEC (@sql)
SELECT @error = @@error
IF (@error 0)
BEGIN
RAISERROR ('Error %d executing stored procedure p_Admin_Kill_Users whilst killing user',16,1,@error)
GOTO ErrorProcessing
END
FETCH NEXT FROM spid_cursor INTO @spid, @Uname
END
CLOSE spid_cursor
DEALLOCATE spid_cursor
ExitProcessing:
RETURN @ReturnStatus
ErrorProcessing:
SELECT @ReturnStatus = 1
GOTO ExitProcessing
GO
-
Thanx Mark. I'll play around with this in DEV.
Thanx to all for the nifty script ideas. In the meantime, this is what I came up with, sort of fits in with what wangkhar suggests:
use master
alter database <database name here>
set single_user -- or offline, depending on your needs
with rollback immediate
go
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply