BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Single User Mode

In some maintenance situations, it is useful to obtain exclusive access to a SQL Server database. This can be achieved by setting the database's user access into single user mode, which permits only one connection to be made to the database at any time.

ALTER DATABASE Command

The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes changing the database user access options.

Setting a Database to Single User Mode

When you wish to obtain exclusive access to a database, the database can be set to single user access mode. Once in this mode, only the current connection is permitted to query or modify the database's data or schema. If any other users or processes attempt to make a connection to the database, they receive an error.

Once a database is set to single user mode, only the connection requesting the mode remains active. If this connection is closed before returning to multi-user mode, any other single user may connect to the database.

The simplest method to set a database into single user mode is to use the following command:

ALTER DATABASE database-name SET SINGLE_USER

The above command immediately prevents new connections being made to the database. However, if there are already active connections, these are not disconnected. Instead, the command is blocked until all other connections are closed. This ensures that any active connections are not terminated unexpectedly.

NB: Background processes that are running against SQL Server may cause the command to block indefinitely, as can internal processes such as the automatic updating of statistics. The AUTO_UPDATE_STATISTICS_ASYNC in particular should be checked. If set to ON, the command will be blocked.

Failing Immediately When Blocked

Executing the ALTER DATABASE command when it is not possible to switch to single user mode can cause problems. To prevent blocking, the command can be executed with the NO_WAIT option. This option causes the command to fail immediately if there are other active connections.

ALTER DATABASE database-name SET SINGLE_USER WITH NO_WAIT

Forcing Disconnection of Other Users

In many situations it is acceptable to execute the ALTER DATABASE command and allow it to remain blocked until all of the other users and processes have disconnected. For SQL Servers that are used only internally within an organisation, the users can be individually contacted and asked to disconnect. Once they have, the command will complete and exclusive access will be achieved.

In other situations, it may not be possible to request that users disconnect. Sometimes it will be necessary to force all users and processes to be disconnected and to roll back any active transactions. This can be achieved using the ROLLBACK termination clause.

The ROLLBACK clause can be used in one of two manners. To force the immediate disconnection of all other users and processes and the rolling back of their transactions, use the following command:

ALTER DATABASE database-name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Alternatively, a period of time can be allotted to permit some transactions to complete. This period is specified as a number of seconds. For example, to allow the command to be blocked for a maximum of ten seconds, use the following statement:

ALTER DATABASE database-name SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS

Setting a Database to Multi-User Mode

Once the use of exclusive access is no longer required, the database can be returned to multi-user mode. This is achieved with the ALTER DATABASE statement as follows:

ALTER DATABASE database-name SET MULTI_USER
3 May 2008