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:
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]
Post a Comment