Obtain Exclusive Access to Database While Performing Maintenance Tasks in SQL Server 2005

When you need to perform maintenance tasks, you as a DB Admin may want to obtain exclusive access to the database. To do so, you can set the database to Single User Mode, which permits only one database connection at a time. By doing so, if other users try and access the database while you are working on that active connection, they will receive an error.

To bring a database to the single user mode, use the following query

ALTER DATABASE YOURDBNAME SET SINGLE_USER

Now if users are already connected to the db when you run this command, they will not be disconnected. Instead the 'SET SINGLE_USER' command will wait till the others have disconnected. If you want to override this scenario and forcefully disconnect other users, then use the following query

ALTER DATABASE YOURDBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once you are through with the maintenance tasks, to return the database back to the multi-user mode, use this query

ALTER DATABASE YOURDBNAME SET MULTI_USER


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

1 comment:

Marco said...

I am Trying to Restore as follows with a job (this database is in my backup server, the backup by itself works perfect, but during the job, it always goes to the error)
SQL SERVER 2000
Job1(all steps gotonext on success and only last one quits on failure)
Step1:
ALTER DATABASE SAR
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Step2:
RESTORE DATABASE [SAR]
FROM DISK = N'E:\MSSQL\BACKUP\BCKP_SAR_RELEVO.BAK'
WITH FILE = 1,
NOUNLOAD ,
STATS = 5,
RECOVERY ,
REPLACE ,
MOVE N'SAR_NEW_Data' TO N'E:\MSSQL\DATA\SAR.mdf',
MOVE N'SAR_NEW_AUDITORIA' TO N'E:\MSSQL\DATA\SAR_1.mdf',
MOVE N'SAR_NEW_LOG' TO N'E:\MSSQL\DATA\SAR_log.ldf'
STEP3:
ALTER DATABASE SAR
SET MULTI_USER
Step4:
USE SAR
go
SELECT COUNT(*) AS 'Total SAR_Rec' from SAR_Rec
THATS IT!
Error:
Job 'REST_SAR_RELEVO' : Step 2, 'REST_SAR_RELEVO' : Began Executing 2009-02-24 19:39:00

Msg 3101, Sev 16: Exclusive access could not be obtained because the database is in use. [SQLSTATE 42000]
Msg 3013, Sev 16: RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000]