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

September 10, 2008

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

Wednesday, September 10, 2008 Posted by Suprotim Agarwal
Labels: SQL Server Administration

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

Did you like this post?



	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal


Feedback:

comments

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

Marco said...

February 24, 2009 5:46 PM

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]