October 11, 2012 at 6:51 pm
Recently I seemed to lock myself out of a database and was wondering if their was a different way I could get access back.
I was trying to Rename a database so ran "ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;"
The database went to SINGLE_USER Ok but an external application made the single connection before I could connect.
I killed this SPID but each time another connection from the application(s) pool made the connection before me.
When ever I tried connecting I either got the "one user only" error or a "deadlock" error.
Basically I had no way of getting into this database to return it to normal mode.
I ended up getting the application services turned off so I could make the connection.
On hind sight maybe I should have used RESTRICTED_USER mode but is their any way I could have got the connection without the more extreme measures of turning a 3rd party application off? Would connecting via the DAC been any different?
thanks
October 11, 2012 at 9:32 pm
Follow the ALTER DATABASE with a USE statement in the same batch will usually get the connection.
USE [master]
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
USE [MyDatabase]
October 12, 2012 at 12:43 am
Thank you, so obvious!!!
I was in Master and was trying the following to get my renaming to work:
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE oldName MODIFY NAME = newName
I guess in the time the ALTER DATABASE Rename code tried gain all its locks etc their was enough time for the 3rd party app to steal the connection.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply