June 14, 2012 at 6:49 am
Hello Everyone
I know there is a way, but I cannot seem to figure out the correct query. I would like to kill all SPIDS according to the database name. Or simply put, drop all connections to a specific database so that I can perform a restore of a database on a scheduled basis.
If anyone can show the query, or point me in the right direction, I would greatly appreciate it.
Thank you in advance
Andrew SQLDBA
June 14, 2012 at 7:03 am
There was an artical on this here http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx, i've not tested the script personally so don't know how sucessful it is but the suggestion of setting your DB to single user or restricted user in your restore script may be a better option.
June 14, 2012 at 7:40 am
I usually just set the database to restricted_user (assuming I've got proper control of the server and everyone isn't logging in as 'sa') and use WITH ROLLBACK IMMEDIATE set. But be careful. If you're on a production server, it's going to rollback all open transactions. You might get a phone call... just sayin'.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2012 at 7:44 am
Following works fine with me in scheduled restore.
ALTER DATABASE DatabseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
--DO restore
ALTER DATABASE DatabseName
SET MULTI_USER
GO
June 14, 2012 at 8:57 am
here's something i use a lot;
it's just a proc that cursors thru all the spids for a given database name;
sp_kill SandBox would kill all spids attached to the database, except your own, and report the results:
example output:
exec sp_kill SandBox
Spid Process Kill List For database: SandBox
52 - DEV223
53 - DEV223
Cannot kill your own SPID, skipping 54 - DEV223
exec sp_kill SandBox
NO Processes Exist to be killed on database SandBox
exec sp_kill Bananas
No database exists with the name Bananas, Check the Spelling of the db.
NO Processes Exist to be killed on database Bananas
The code:
--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)
DECLARE @DBID INT
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100) ,
RID INT,
)
select @DBID=db_id(@DBNAME)
IF @DBID IS NULL
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
INSERT INTO #TMPLOG EXEC SP_WHO
--do not even try to kill spids that are SQL server itself.
DELETE FROM #TMPLOG WHERE SPID < 50
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
IF @SPID = @@SPID
BEGIN
PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
END
ELSE
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
END
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
June 14, 2012 at 9:05 am
Lowell (6/14/2012)
here's something i use a lot;it's just a proc that cursors thru all the spids for a given database name;
sp_kill SandBox would kill all spids attached to the database, except your own, and report the results:
example output:
exec sp_kill SandBox
Spid Process Kill List For database: SandBox
52 - DEV223
53 - DEV223
Cannot kill your own SPID, skipping 54 - DEV223
exec sp_kill SandBox
NO Processes Exist to be killed on database SandBox
exec sp_kill Bananas
No database exists with the name Bananas, Check the Spelling of the db.
NO Processes Exist to be killed on database Bananas
The code:
--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)
DECLARE @DBID INT
CREATE TABLE #TMPLOG (
SPID INT,
ECID INT,
STATUS VARCHAR(50),
LOGINAME VARCHAR(255),
HOSTNAME VARCHAR(50),
BLK INT,
DBNAME VARCHAR(30),
CMD VARCHAR(100) ,
RID INT,
)
select @DBID=db_id(@DBNAME)
IF @DBID IS NULL
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
INSERT INTO #TMPLOG EXEC SP_WHO
--do not even try to kill spids that are SQL server itself.
DELETE FROM #TMPLOG WHERE SPID < 50
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
IF @SPID = @@SPID
BEGIN
PRINT 'Cannot kill your own SPID, skipping ' + convert(varchar,@spid) + ' - ' + @HOSTNAME
END
ELSE
BEGIN
SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)
EXEC SP_EXECUTESQL @STR
PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME
END
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, in what cases do you use this? For the operation of restoring a database, I would prefer to use the
ALTER DATABASE DatabseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO because of the possibility of some process beginning after I have killed them all. So under what situations do you use your SP?
Jared
CE - Microsoft
June 14, 2012 at 9:17 am
SQLKnowItAll (6/14/2012)
Lowell, in what cases do you use this? For the operation of restoring a database, I would prefer to use the
ALTER DATABASE DatabseNameSET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO because of the possibility of some process beginning after I have killed them all. So under what situations do you use your SP?
actually, for me, it's two things;
convenience and typing: easier to call a short-named proc. typing the full command would annoy me.
i have an sp_kill2 which calls the same ALTER DATABASE you mentioned for the situations when i need to kick off and restore.
but the one i posted is very handy for when i run into some developer that is in the middle of a transaction while stepping thru the code, and then went to lunch...getting the hostnames back for the spids I killed tells me who was the blocker who went to lunch...just more information for me , i guess.
at my shop, it happens too much, since people get called into meetings and stuff a lot.
literally yesterday i had to do it just to know who was blocking my dev databbase and open it back up to use again...
Lowell
June 14, 2012 at 9:25 am
Thanks Everyone
I am using the ALTER DATABASE code. Much more simple, and NO cursors:w00t:
I wish we could dis-invent the cursor:-D
Andrew SQLDBA
June 14, 2012 at 9:31 am
here's my other proc: note that it "toggles" the setting from single user to multi user for multiple calls;
that way if i need exclusive access, i can get it do stuff and put it back;
CREATE PROCEDURE sp_Kill2
@DBNAME VARCHAR(30)
AS
BEGIN
SET NOCOUNT ON
DECLARE @DBID INT
SELECT @DBID=DB_ID(@DBNAME)
IF @DBID IS NULL
BEGIN
PRINT 'No database exists with the name ' + @DBNAME + ', Check the Spelling of the db.'
RETURN 1
END
IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc = 'MULTI_USER')
BEGIN
DECLARE @sql varchar(2000)
SET @sql = 'ALTER DATABASE '
+ quotename(@DBNAME)
+ ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
PRINT @sql
EXEC(@sql)
END
ELSE IF EXISTS(SELECT * FROM sys.databases WHERE name = @DBNAME AND user_access_desc <> 'MULTI_USER')
BEGIN
SET @sql = 'ALTER DATABASE '
+ quotename(@DBNAME)
+ ' SET MULTI_USER'
PRINT @sql
EXEC(@sql)
END
END --PROC
Lowell
September 9, 2014 at 11:48 am
I found an example online that used a cursor and didn't protect its own spid from getting killed so I created a different version.
DECLARE @spid INT, @SQL VARCHAR(8000);
SET @spid = @@SPID;
SET @SQL = '';
SELECT @SQL += 'KILL ' + CONVERT(VARCHAR(5), spid) + ';'
FROM [master]..sysprocesses
WHERE [dbid] = db_id('<<YourDatabaseNameHere>>')
and spid != @spid;
EXEC(@SQL);
GO
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply