Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

KILL spids

To quickly disconnect all non admins from a database, I use this:

ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

One of the development teams recently requested that their production databases be copied to a development server on a weekly basis.  In the development environment, developers have db_owner access to all of their databases. 

In order to restore a database, no one can be connected to it if it already exists.  Since my ALTER DATABASE statements will only work on non admins, I also need to run this to kick out developers:

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid IN (DB_ID('Database1'), DB_ID('Database2'))

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE
  dbid IN (DB_ID('Database1'), DB_ID('Database2')) AND
  spid > @spid
END

Most SQL Server DBAs have a similar script to this, but I thought I'd post mine in case you don't have one already.

NOTE: I can't run the following commands as we are using SQL Litespeed.  Their extended stored procedure to restore databases, master.dbo.xp_restore_database, uses multiple connections to the database. 

ALTER DATABASE Database1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE Database2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Print | posted on Thursday, February 08, 2007 1:43 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: KILL spids

I used to handle disconnecting users this way, but I recently ran into an application that reconnected so fast that I could never get exclusive access.

Since all I wanted to do was restore the database from a backup, I hit on doing this:
alter database MyDatabase set offline with rollback immediate

It kicks everyone out of the database, and makes it impossible to reconnect until the database is set online. It even keeps out members of the sysadmin fixed server role, so you can’t accidentally shoot yourself in the foot with a connection you forgot to close.


2/15/2007 4:58 PM | Michael Valentine Jones
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET