Skip to content

Latest commit

 

History

History
65 lines (40 loc) · 3.82 KB

set-a-database-to-single-user-mode.md

File metadata and controls

65 lines (40 loc) · 3.82 KB
title description author ms.author ms.date ms.service ms.topic helpviewer_keywords
Set a database to single-user mode
Learn how to set a database to single-user mode.
WilliamDAssafMSFT
wiassaf
07/19/2024
sql
how-to
single-user mode [SQL Server], database option

Set a database to single-user mode

[!INCLUDE SQL Server]

This article describes how to set a user-defined database to single-user mode in [!INCLUDE ssnoversion] by using [!INCLUDE ssManStudioFull] or [!INCLUDE tsql]. Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

Limitations

  • If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

  • The database remains in single-user mode even after the user that set the option is disconnected. At that point, a different user, but only one, can connect to the database.

Prerequisites

  • Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. For more information, see ALTER DATABASE SET Options (Transact-SQL).

Permissions

Requires ALTER permission on the database.

Use SQL Server Management Studio

To set a database to single-user mode:

  1. In Object Explorer, connect to an instance of the [!INCLUDE ssDEnoversion], and then expand that instance.

  2. Right-click the database to change, and then select Properties.

  3. In the Database Properties dialog box, select the Options page.

  4. From the Restrict Access option, select Single.

  5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, select Yes.

You can also set the database to Multiple or Restricted access by using this procedure. For more information about the Restrict Access options, see Database Properties (Options Page).

Use Transact-SQL

To set a database to single-user mode:

  1. Connect to the [!INCLUDE ssDE].

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example sets the database to SINGLE_USER mode to obtain exclusive access. The example then sets the state of the [!INCLUDE ssSampleDBobject] database to READ_ONLY and returns access to the database to all users.

Warning

To quickly obtain exclusive access, the code sample uses the termination option WITH ROLLBACK IMMEDIATE. This will cause all incomplete transactions to be rolled back and any other connections to the [!INCLUDE ssSampleDBobject] database to be immediately disconnected.

:::code language="sql" source="codesnippet/tsql/set-a-database-to-single_1.sql":::

Related content