I have a development database that re-deploy frequently from a Visual Studio Database project (via a TFS Auto Build).
Sometimes when I run my build I get this error:
ALTER DATABASE failed because a lock could not be placed on database 'MyDB'. Try again later.
ALTER DATABASE statement failed.
Cannot drop database "MyDB" because it is currently in use.
I tried this:
ALTER DATABASE MyDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
but I still cannot drop the database. (My guess is that most of the developers have dbo access.)
I can manually run SP_WHO and start killing connections, but I need an automatic way to do this in the auto build. (Though this time my connection is the only one on the db I am trying to drop.)
Is there a script that can drop my database regardless of who is connected?
