對這文章發表回應
發表限制: 非會員 可以發表
Set a Database to Single-user Mode
Applies To: SQL Server 2016
This topic describes how to set a user-defined database to single-user mode in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.
Limitations and Restrictions
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 if the user that set the option logs off. 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).
Security
Permissions
Requires ALTER permission on the database.
To set a database to single-user mode
In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
Right-click the database to change, and then click Properties.
In the Database Properties dialog box, click the Options page.
From the Restrict Access option, select Single.
If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click 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).
To set a database to single-user mode
Connect to the Database Engine.
From the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. This example sets the database to
SINGLE_USERmode to obtain exclusive access. The example then sets the state of the AdventureWorks2012 database toREAD_ONLYand returns access to the database to all users.The termination optionWITH ROLLBACK IMMEDIATEis specified in the firstALTER DATABASEstatement. This will cause all incomplete transactions to be rolled back and any other connections to the AdventureWorks2012 database to be immediately disconnected.
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO
補充 （Community Additions）：
Switch to single-user mode and back, respecting AUTO_UPDATE_STATISTICS_ASYNC
Code that takes into account the AUTO_UPDATE_STATISTICS_ASYNC flag and if enabled, temporarily disables it. Sets the database into single-usermode, does the work, re-enablesmulti-usermode and re-enablesasynchronous statisticupdates (if needed).
-- Drop temporary table "##OriginalState" table
IF OBJECT_ID('tempdb.dbo.##OriginalState', 'U') IS NOT NULL BEGIN
DROP TABLE ##OriginalState;
END
GO
-- Create temporary table "##OriginalState"
CREATE TABLE ##OriginalState([AsyncAutoStats] [BIT] NOT NULL);
GO
-- Fill in "AutoUpdateStatsAsync" in temp table
DECLARE @AutoUpdateStatsAsync AS BIT;
SELECT @AutoUpdateStatsAsync = CASE WHEN (is_auto_update_stats_on = 0) THEN 0 ELSE CASE WHEN (is_auto_update_stats_async_on = 0) THEN 0 ELSE 1 END END
FROM sys.databases WHERE name = 'AdventureWorks2012'
INSERT INTO ##OriginalState ([AsyncAutoStats]) SELECT @AutoUpdateStatsAsync;
-- Disable "AutoUpdateStatsAsync"
IF (@AutoUpdateStatsAsync = 1) BEGIN
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
END
GO
-- Change database to "single-user mode"
USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
-- Do your work in sinle-user mode....
-- ....
-- Change database back to "multi-user mode"
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO
-- Re-enable asynchronous auto statistics
DECLARE @AutoUpdateStatsAsync AS BIT;
SELECT @AutoUpdateStatsAsync = [AsyncAutoStats] from ##OriginalState;
IF (@AutoUpdateStatsAsync = 1) BEGIN
ALTER DATABASE AdventureWorks2012 SET AUTO_UPDATE_STATISTICS_ASYNC ON;
END
GO
-- Drop temporary table "##OriginalState" table
IF OBJECT_ID('tempdb.dbo.##OriginalState', 'U') IS NOT NULL BEGIN
DROP TABLE ##OriginalState;
END
GO
原文出處： Set a Database to Single-user Mode