Best way to make a database not in use?

Hi.

I am almost wrapping up a project of my own and I will have the option to restore the database, so dropping the existing one and then running the SQL script to create the database, tables, stored procedures and so on.

My question is, (using .NET 2.0 C#) - what is the best way to resolve the "cannot drop database as it is currently in use" message, or some message of the similar sort?

Even though I specified "USE MASTER" in the commandText string, there will be somewhere, in a situation, where the database will still be in use.

How can I forcefully make it NOT in use, drop it and do other things I like to?

What is the best way?

Many thanks!

Commonly we just cursor through open spids and execute KILL session_id/spid:

--2005
select session_id
from sys.dm_exec_requests
where db_name(database_id) = 'your database'

--2000 and earlier
select spid
from master..sysprocesses
where db_name(dbid) = 'your database'

|||Are you using SQL 2005? If so, in Management Studio, in Object Explorer, navigate to [Database Name]\Management\Acticity Monitor. Double click Activity Monitor - the Activity Monitor appears.

Using the Activity Monitor (with "Process Info" selected in the left-hand pane), you can see all active processes. As you have noticed, you won't be able to drop your database with active processes. Simply right-click processes that you wish to terminate, and choose "Kill Process". Note that you can see the database name as part of each process' information.

--

If you are using SQL 2000, (from my memory) an easy way to terminate all processes is to right-click the database, choose "Detach", and then when the dialog appears, there's an option to clear all existing users. After this has finished, you can drop the database.|||

Many thanks, I will give this a shot.

It's just amazing, how we can do ALL of this using SQL Server, kill processes, query the master tables as well as each of our own created tables/databases - fasinates me always :)

|||If you have any problems, or need to post again (SQL or otherwise), it's always helpful to specify product & the version of the software you are using. (SQL 2000 or SQL 2005, VB, VB.NET, C#, VS.NET 2003/2005 ... etc.)|||

Just:

use [YourDb]

alter database [YourDb] set single_user with rollback immediate

-- if there are no users with dbo permissions, you may use restricted_user instead

go

-- do your restore here

go

alter database [YourDb] set multi_user

|||gavrilenko_s - good suggestion. |||

ok, thanks for that

one more Q

if we have more than 1 SPID for a database, how can I kill them all?

I was thinking about doing this, however I think it's bad practice and prefer to do it the proper way!

DECLARE @.numberOfProcess int
SET @.numberOfProcess = 0
SET @.numberOfProcess = (select COUNT (spid) from master..sysprocesses where db_name(dbid) = 'db')
--SELECT @.numberOfProcess
IF (@.numberOfProcess > 0)
BEGIN
WHILE (@.numberOfProcess IS NOT NULL)
BEGIN
SET @.numberOfProcess = (select TOP 1 (spid) from master..sysprocesses where db_name(dbid) = 'db')
-- SELECT @.numberOfProcess
DECLARE @.SqlStatement NVARCHAR (20)
SET @.SqlStatement = 'KILL ' + CAST(@.numberOfProcess AS CHAR(2))
SELECT @.sqlStatement
EXEC sp_executesql @.SqlStatement
END

END

|||

Hi,

The method gavrilenko_s posted before is the best (which is always use for kicking users).

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de