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

Posted on 2009-04-20
Medium Priority
Last Modified: 2012-05-06
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?

Question by:erwin_des
  • 3
  • 2
LVL 25

Expert Comment

ID: 24186363
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

Author Comment

ID: 24187246
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?
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 24187386
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"

LVL 25

Expert Comment

ID: 24187447
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..

Author Closing Comment

ID: 31572312
Thanks !!!

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question