Disconnect all users from Sql Server 2005/2008 Database

In some cases DBA's need to expire or close all connections from SQL server 2005 / SQL server 2008 database such as for attach detach DataBase, Make DB readonly, perform maintenance tasks etc. For such type of issues DBA wants to get 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. At that moment if other users try to 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:

1	ALTER DATABASE DATABASENAME SET SINGLE_USER

Users those 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:

1	ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

OK now your database immediately move to the single user mode. Now After completion of your maintenance task you need to go back to multiuser mode by applying another TSQL command which is given below:

1	ALTER DATABASE DATABASENAME SET MULTI_USER