Home » SQL Server » How to Start SQL Server in Single User Mode ?

How to Start SQL Server in Single User Mode ?


Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc.

Steps to start SQL Server in Single User Mode :-

1) Stop SQL Server Services

2) Add [-m] startup parameter in SQL Services from configuration manager

Parameter [-m] Starts SQL Server instance in single-user mode. SQL Server in single-user mode can connect with single user only and did not start CHECKPOINT process. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.

From SQL Server 2005 to SQL Server 2008 R2

From SQL Server 2012 onwards

3) Start SQL Services & SQL server will come online in single user mode. You can connect using SQLCMD & continue with desired operation

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Advertisements

17 Comments

  1. BV says:

    Nice one thanks. I suppose one would need to do this in a case of TempDB issues?

  2. Michael Meierruth says:

    Sometimes doing it just for a single database can be very useful too:
    alter database MYDB set single_user with rollback immediate
    go

    • Yes. Bringing DB in single user mode also required many time. But taking instance in single user mode in compare of database in single user mode is bit different. When instance in single user mode then one connection for all databases but when DB in single user mode instance & other DB will remain intact & keep working fine. Only that DB allow only one connection at one time.

  3. Tony says:

    Couple questions:

    In Step #1 – Do you stop all SQL Server services? Or, just the two that you have stopped in the image?

    In Step #3 – Do you have to connect using SQLCMD? Or, can you use SQL Server Management Studio? If you have to connect using SQLCMD, what is the best way to do that?

    • 1) Yes, only these 2 services are required. Not all.
      2) Preferred one is SQLCMD because SSMS create more than one connection itself to database that why you can face issue while connecting using SSMS when instance in single user mode. Got to Command Prompt > Type SQLCMD with Server name & user name & password.

  4. Chowdary says:

    if we add the -m to the Startup parameter does it make all the databases into a Single User mode?

    • According to theory NO. When you use -m you SQL instance will work as single mode. No directly impact on database. But I go to practically, If you instance is in single user mode that means only one user can connect to instance at one time. This will result all database to be behave like single user.

  5. […] You can also do it from configuration manager, Like we start SQL server in single user mode. (http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/) […]

  6. […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  7. Start Mssql Single User | Floydmeaveadue says:

    […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  8. […] How to Start SQL Server in Single User Mode ? « MSSQLFUN – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  9. Start Sqlcmd In Single User Mode | Floydmeaveadue says:

    […] How to Start SQL Server in Single User Mode ? « … – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  10. Single User Sql Server Command | Dumamey says:

    […] How to Start SQL Server in Single User Mode ? « MSSQLFUN – Sometime, We caught in a situation where we need to run SQL server in single user mode. Issues like Master DB restore, Sysadmin locked out etc. […]

  11. Brian Hart says:

    When I tried putting the -m; switch just now and attempting to then restart the SQL2008 service on my machine, it hangs at “starting service…” for a long time and then says “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.” What am I doing wrong?

    • Hi Brian

      Can you please check SQL server error log and windows event log for error details? There should be some issues due to which you are facing this issue.

      Regards
      Rohit Garg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: