Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Kill All Spid(s) according to Database Name Expand / Collapse
Author
Message
Posted Thursday, June 14, 2012 6:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
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
Post #1315827
Posted Thursday, June 14, 2012 7:03 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:19 AM
Points: 728, Visits: 771
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.
Post #1315844
Posted Thursday, June 14, 2012 7:40 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:27 PM
Points: 15,541, Visits: 27,919
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1315881
Posted Thursday, June 14, 2012 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:52 AM
Points: 114, Visits: 991
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

Post #1315887
Posted Thursday, June 14, 2012 8:57 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1315966
Posted Thursday, June 14, 2012 9:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 11:23 AM
Points: 2,691, Visits: 3,376
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?


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1315971
Posted Thursday, June 14, 2012 9:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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 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?


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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1315979
Posted Thursday, June 14, 2012 9:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:19 PM
Points: 975, Visits: 3,342
Thanks Everyone
I am using the ALTER DATABASE code. Much more simple, and NO cursors

I wish we could dis-invent the cursor

Andrew SQLDBA
Post #1315989
Posted Thursday, June 14, 2012 9:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:22 PM
Points: 12,889, Visits: 31,839
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1316002
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse