September 10, 2008

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


Did you like this post?
kick it on DotNetKicks.com
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"
  1. Marco said...
    February 24, 2009 at 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]

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions