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

Posted on 2009-04-20
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
    LVL 25

    Expert Comment

    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

    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

    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

    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

    Thanks !!!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Suggested Solutions

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now