Drop SQL Server Database

Question

I am creating a SQL Server database programmatically during a conversion. If the conversion code fails I want to delete/drop the database. If I use the shortcut menu for the database in SQL Server Management Studio 2005 the "delete" option is disabled. DROP DATABASE command also fails with message "Cannot drop database "XYZ" because it is currently in use."

I have shutdown and restarted the SQL Server and the database will not drop.

Any direction?

Answer 1

A new search found the following script that worked:

ALTER DATABASE [dbname]
SET SINGLE_USER --or RESTRICTED_USER
WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [dbname];
GO

Must have been some open transaction that was stopping the drop. Problem solved.

Answer 2

How long does your conversation takes.. Maybe you should consider using transactions, if that's an option. Just roll the transaction back when your conversation fails.