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).
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 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:
ALTER DATABASE AccessDB SET SINGLE_USER WITH NO_WAIT
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
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
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:
ALTER DATABASE AccessDB SET MULTI_USER WITH NO_WAIT
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:
EXEC sp_dboption ‘AccessDB’, ‘single user’, ‘true’
EXEC sp_dboption ‘AccessDB’, ‘single user’, ‘false’
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!
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.