Wednesday, September 06, 2006
Ruthless Database Administration
I like to keep a special database on my server, called 'admin', wherein I store nasty little procedures like this one. So, you thought you were going to restore the database from backup, but after waiting about 30 seconds the server complains that there are users in the database.
If you're trying to restore the database from backup, who cares if you ruin a query that was in progress? Get them out of there! The following sproc will kill all processes running in the specified database, except for the current process. No questions asked.
USE [admin]
GO
If you're trying to restore the database from backup, who cares if you ruin a query that was in progress? Get them out of there! The following sproc will kill all processes running in the specified database, except for the current process. No questions asked.
USE [admin]
GO
/*************************************************************
*
* sproc: kill_processes
* author: tgannon@gmail.com
* This code is provided as is, and the author is not responsible for data damaged or lost
* sproc: kill_processes
* author: tgannon@gmail.com
* This code is provided as is, and the author is not responsible for data damaged or lost
* through its use.
*
*************************************************************/
ALTER PROCEDURE dbo.kill_processes (@database_name SYSNAME)
AS
DECLARE test_cursor CURSOR
READ_ONLY
FOR
select spid from master..sysprocesses p inner join master..sysdatabases d
on p.dbid = d.dbid
where name=@database_name
DECLARE @spid int, @cmd varchar(50)
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @spid <> @@spid
BEGIN
PRINT 'Killing process ' + CONVERT(VARCHAR, @spid) + '.'
SELECT @cmd = 'KILL ' + CONVERT(VARCHAR, @spid)
EXEC (@cmd)
END
END
FETCH NEXT FROM test_cursor INTO @spid
END
CLOSE test_cursor
DEALLOCATE test_cursor
ALTER PROCEDURE dbo.kill_processes (@database_name SYSNAME)
AS
DECLARE test_cursor CURSOR
READ_ONLY
FOR
select spid from master..sysprocesses p inner join master..sysdatabases d
on p.dbid = d.dbid
where name=@database_name
DECLARE @spid int, @cmd varchar(50)
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @spid
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @spid <> @@spid
BEGIN
PRINT 'Killing process ' + CONVERT(VARCHAR, @spid) + '.'
SELECT @cmd = 'KILL ' + CONVERT(VARCHAR, @spid)
EXEC (@cmd)
END
END
FETCH NEXT FROM test_cursor INTO @spid
END
CLOSE test_cursor
DEALLOCATE test_cursor
# posted by gone_cat @ 9:21 PM 
