In this article you will learn the 'hows and whys' of single-user mode in Microsoft SQL Server 2008 R2. We will also differentiate single-user mode from the Dedicated Administrator Connection (DAC).

Timothy Warner

Timothy Warner is a Microsoft Cloud and Datacenter Management Most Valuable Professional (MVP) who is based in Nashville, TN. Check out his Azure and Windows Server video training at Pluralsight, and feel free to reach out to Tim via Twitter.

Some administrative actions in Microsoft SQL Server 2008 R2, such as restoring a corrupted system database, require that you start the database in question by using single-user mode.

When a database is started in single-user mode, the following events occur:

  • Any current connections to the database are dropped without warning
  • One and only one connection is then allowed into that database
  • The CHECKPOINT process is not started

NOTE: If you do not stop the SQL Server Agent service prior to putting a database into single-user mode, the Agent service will consume (and subsequently block) the single user connection.

Thus, single-user mode is useful for troubleshooting serious issues with a particular SQL Server database. Let’s now examine how to put a database into single-user mode.

Putting SQL Server into single-user mode ^

We can start up a SQL Server 2008 database in single-user mode by using either the graphical SQL Server Management Studio (SSMS) tool or by issuing Transact-SQL (T-SQL) statements.

In the former case, log into SSMS as an administrator, right-click the database in question, and select Properties from the shortcut menu. In the Database Properties sheet, navigate to the Options page and set the Restrict Access property to SINGLE_USER. This is shown in the following screen capture.

Setting single-user mode for SQL Server in SSMS

Setting single-user mode in SSMS

By contrast, you can use either the SSMS Query Editor or a sqlcmd session to issue the following T-SQL statement. For instance, the following statement puts the example AccessDB database into single-user mode:

There are several options you can add to the SET SINGLE_USER clause to adjust the aggressiveness of this action; as always, you should consult the first-party documentation.

Working in single-user mode ^

As you can see in the following screenshot, a database in single-user mode is graphically apparent in SSMS:

Database in single-user mode

Database in single-user mode

As long as you are the single user (remember that SQL Server Agent service must be stopped!), you have typical access to that database. However, if another user attempts a connection to the target database, SQL Server 2008 R2 raises an error:

SQL Server Error 924

SQL Server Error 924

Getting SQL Server out of single-user mode ^

Some novice SQL Server 2008 DBAs panic a bit when they are faced with taking a database out of single-user mode and back into multi-user mode. The quickest way to do this in my experience is to log into the instance by using sqlcmd and issue the following T-SQL statement as an administrator:

While we are on the subject of T-SQL, we can employ the sp_dboption system stored procedure to change the access mode of a SQL Server 2008 database. Check this out:

How is single-user mode different from DAC? ^

I discussed the Dedicated Administrator Connection (DAC) functionality of SQL Server 2008 in a previous blog post. In that post, a reader asked me to differentiate between the DAC and single-user mode.

We will recall that SQL Server 2008 sets aside a separate process that DBAs can use for emergency access to an unresponsive SQL Server instance; this is the DAC.

We can now see that there is a fundamental difference between the DAC and single-user mode even though both modalities are used for troubleshooting purposes. Namely, single-user mode is a database startup mode, and DAC is an "emergency pipeline" to an entire instance of the SQL Server database engine.

In fact, you can log into a single-user mode database by using the DAC!

Conclusion ^

SQL Server is a wonderful topic to discuss, especially because many Windows systems administrators find themselves to be unwilling SQL Server DBAs and have lots of questions. Speaking of questions, please leave those, along with any other SQL Server blog post ideas, in the comments portion of this post.

Win the monthly 4sysops member prize for IT pros


Related Posts

1 Comment
  1. Ramaudaya 5 years ago

    Hi Tim ,
    Dont mind,you have to change the title "Putting SQL Server database into single-user mode" not an "Putting SQL Server into single-user mode"
    because you can put SQL server also single user mode.

    Rama Udaya


Leave a reply

Your email address will not be published. Required fields are marked *



Please ask IT administration questions in the forum. Any other messages are welcome.

© 4sysops 2006 - 2017

Log in with your credentials


Forgot your details?

Create Account