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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.5 (2)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Undocumented T-SQL Tricks With Logs

An interesting feature added to SQL Server 7.0 and 2000 is the ability to purge
an error log through a stored procedure or DBCC command. In this article, Brian Knight shows some of the undocumented stored procedures to detect, read and purge an SQL Server error log in T-SQL.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

1 (1)

You rated this post out of 5. Change rating

2001-06-27

14,813 reads