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