I have a database that is stuck in single-user mode. I kill the process that obtains the "lock" on the db but when I kill it another one spawns automatically, (using the sa account). This happens even with SQL Server agent disabled. Any ideas?
+1
A:
It's probably your SQL Management Studio (or similar) connection. Your killing your own connection, then re-connecting to see if anyone else is on it, resulting in another connection.
If that is not it, check out the properties (username, status, Application) of the connection in the "Activity Monitor" (under the "Management" folder).
If you want to move it out of single user mode, right click on the database and go to the properties. Select the "Options", and right down the bottom is a property called "Restrict Access".
Robert Wagner
2008-11-25 00:55:56
+3
A:
Can you login into the server/database?
If not, try the ADMIN:ServerName and it should open an emergency admin session
Then you can go into the SQL Server to ALTER DATABASE [name] SET MULTI_USER
jerryhung
2008-11-25 00:58:32
For reference, this is called the DAC , or dedicated administrator connection - and I believe it needs to be enabled in sql server surface area config.
Sam
2008-11-25 20:29:59
Didn't work for me the first time, but worked after restarting SQL Server.
g .
2009-04-28 15:14:52