Thursday, February 25, 2010

Kick expire close all user connection from Sql Server 2005 / 2008 Database




Please visit my new Web Site https://coderstechzone.com



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

So now hope you can gain quick access in your database by applying the above TSQL command.

0 comments:

Want to say something?
I WOULD BE DELIGHTED TO HEAR FROM YOU

Want To Search More?
Google Search on Internet
Subscribe RSS Subscribe RSS
Article Categories
  • Asp.net
  • Gridview
  • Javascript
  • AJAX
  • Sql server
  • XML
  • CSS
  • Free Web Site Templates
  • Free Desktop Wallpapers
  • TopOfBlogs
     
    Free ASP.NET articles,C#.NET,VB.NET tutorials and Examples,Ajax,SQL Server,Javascript,Jquery,XML,GridView Articles and code examples -- by Shawpnendu Bikash