SQL Server 2005 database stuck in single user mode

Question

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?

Answer 1

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

Answer 2

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".