For training related to .net,OOPS,design patterns, MVC,WCF and .net fundamentals
contact at sukeshmarla@gmail.com or Click Here

Click here for my training feedbacks

Followers

Follow Us



Share your Interview questions here


For software development queries contact SukeshMarla@Gmail.com

Follow by Email

Learn everything about JSON in a day

19 April 2011

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:
1ALTER 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:
1ALTER 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:
1ALTER DATABASE DATABASENAME SET MULTI_USER