DROP DATABASE problem

I have an application (VS2005, C#) that creates one or more SQL Server
Express (2005) databases and populates them with a schema and some
initial data. This all works.
From time to time there is a legitimate runtime issue that means I
should remove one of the newly created databases. The problem is that
the DROP DATABASE XXX command, which I execute from a new connection via
an admin login on the master database, fails with the error "Cannot drop
database "XXX" because it is currently in use.".
I have checked with "sp_who" and there are no active connections to the
database, but there are a couple of 'sleeping' connections, which I
presume belong to the connection pool.
If I wait for some period of time, the sleeping connections disappear
and the DROP DATABASE command then succeeds.
What is the simplest mechanism for permitting me to drop the database
without waiting for the connection pool to expire on its own?
-ken
Hi Ken,
ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE SomeDatabase
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I actually found another method that works as well -- I simply use the
SqlConnection.Clear(specificConnection) to remove the entries from the
pool, and this seems to work.
-ken
Jens wrote:
> Hi Ken,
> ALTER DATABASE SomeDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> GO
> DROP DATABASE SomeDatabase
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>