Monday, April 14, 2008

SQL 2005 would not let me drop a database

I did some digging to see if there was a 'real' reason for this. All I could find was the the database was in use.

I found this t-sql script to help me find out why the db was in use.


USE Master

DECLARE @dbid INT
DECLARE @dbname nvarchar(40)
SET @dbname = '[your_db_name_here]'
SELECT @dbid = dbid FROM sys.sysdatabases WHERE name = @dbname

IF EXISTS (SELECT spid FROM sys.sysprocesses WHERE dbid = @dbid)
BEGIN
--RAISERROR ('The restore cannot be done', 16, 1)
SELECT 'These processes are blocking the db in question' AS Note,
spid, last_batch, [status], hostname, loginame FROM sys.sysprocesses
WHERE dbid = @dbid
END
ELSE
BEGIN
SELECT 'There were no SPIDS using the database: ' + @dbname as Note
END



I ran this t-sql and saw that I had a single SPID (51) that was connected to my database. I executed 'kill [spid]' and then tried my drop db command again. Of course it didn't work. I then ran this script repeatedly by highlighting the code ind the SQL Server Management Studio code window and pressed F5 over and over until I could see that the command to drop the db was working.

Not sure why that SPID kept reappearing. But the problem is fixed.

1 comment:

Drewberry said...

[explanation]
Um, the reason I posted the code was not to fix the problem with a database that wouldn't let you drop a database, but to show folks how to see what the cause for not being allowed to take an action on a database object...
[/explanation]