SQL Server 2008 - Single User-mode after backup - I can't reconnect

My intention was to create a backup of my database on SQL Server 2008 (service pack 1).
After my attempt  I couldn't reconnect. The database is now mentionning that it is in Single User mode. And, I'm not able to reconnect to my database....

What steps are necessary to reconect to my database and to set sngle user mode to false?



erwin_desAsked:
Who is Participating?
 
reb73Commented:
Do you have SQL Server Management Studio open by any chance with this particular database expanded?

If yes, right-click on the database, choose options and uncheck the single user checkbox

If not execute the following command -

sqlcmd.exe -S <ServerName> -E -d tempdb -o "c:\current_connections.txt" -Q "exec sp_who2"

This command should give you a list of active/passive connections. Find the connection to the relevant database and note the SPID for this connection. Then run the following command substituting <spid> with the integer value of the spid found -

sqlcmd.exe -S <ServerName> -E -d tempdb -Q "kill <spid>; exec sp_dboption '<dbname>','single user', false"





0
 
reb73Commented:
Run the following command in the OS Command Prompt after substituting the <ServerName> and <dbname> with server and the database set to single use mode..

sqlcmd.exe -S <ServerName> -E -d tempdb -Q "exec sp_dboption '<dbname>','single user', false"

This should set the database back to multi-user
0
 
erwin_desAuthor Commented:
I have tried your proposition but received following message : Changes to the state or options of database  cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server ..., Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

It seems I need first to drop a coonection... but nobody is connected.... How to proceed?
0
 
reb73Commented:
The other option is to restart the SQL Server service using the SQL Configuration Management tool, but this is to be only as a last resort..
0
 
erwin_desAuthor Commented:
Thanks !!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.