Single-user mode in SQL Server 2008

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 By Timothy Warner - Tue, November 29, 2011 - 1 comments

Timothy Warner is a Windows systems administrator, software developer, author, and technical trainer based in Nashville, TN.

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.

-1+1 - Rate this post
Loading ... Loading ...
Your question wasn't answered? Ask in the new 4sysops forum!

One Comment- Leave a Reply

  1. Ramaudaya says:

    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.

    Thanks,
    Rama Udaya

===Leave a Comment===

Login

Lost your password?