Technical Article

Temporary exclusive database access

,

This is a DBA script. You must have ALTER permission on the database, which is typically limited to those who are members of the db_owner database role or the sysadmin server role.

While the database is in single-user mode, you can perform tasks that require or would benefit from that restriction or just immediately switch back to multi-user.

We use this at my office when we need to refresh a database snapshot immediately. All users currently accessing the snapshot are forcibly disconnected. We then drop and recreate the snapshot.

USE mydb;
IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = DB_NAME() AND d.is_auto_update_stats_async_on = 1)
BEGIN;
--ALTER DATABASE mydb SET AUTO_UPDATE_STATISTICS_ASYNC OFF;
RAISERROR('Disable asynchronous statistics update before setting database to single-user.', 16, 1);
RETURN;
END;


ALTER DATABASE mydb SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

-- perform any work that requires single-user access

ALTER DATABASE mydb SET MULTI_USER WITH NO_WAIT;

Rate

2.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

2.5 (2)

You rated this post out of 5. Change rating