Kill all sessions for a given database
Posted by decipherinfosys on August 1, 2008
Sometimes, when doing maintenance work or upgrading from one version of the database to another one, a DBA/Database Developer needs to make sure that there are no other sessions connected to the user database. Of course, prior to do anything like this one would notify everyone about the maintenance window and when services will be restored etc.. Re-starting services was not an option since this was being done on only one of the databases on the instance and not all the databases.
Prior to SQL Server 2005, the way to do that was to do something like this in a script:
a) Take the database name as an input parameter, collect all the spids for that database from sysprocesses.
b) Loop through and kill those sessions, log the record and if needed – send an e-mail alert to the concerned account if that information is available.
This used to be an issue obviously since if there is a service that the developer forgot to switch off prior to this work, it could keep on trying to make a connection and post the kill command, can again make a connection to the database and be active.
In SQL Server 2005, this is pretty simply achieved by just setting the database in a single user mode. I typically always do this using the ALTER DATABASE COMMAND since it is a simple script and one can run it, do their work and then turn it back to multi user again. Here are the commands to do it:
Suppose we want to set the single user option for the database called PLANGOALS on the instance, the command would be:
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = ‘PLANGOALS’ AND IS_AUTO_UPDATE_STATS_ASYNC_ON = 1)
ALTER DATABASE PLANGOALS SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE PLANGOALS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
What we are doing above is that we are first checking whether the asynch option is set to ON for the database and are first setting it to off to make sure that the background thread that is used to update the statistics does not take a connection to the database – this is important. After that, we set the database in the single user mode with the termination option of rollback immediate which will rollback any other open transactions from other sessions and terminate those sessions.
And if you are a GUI person, this option is also available via SSMS. In the object explorer, right click on the database in question and select properties. Go to Options and then in the Restrict Access option select Single. If there are open connections to this database, an Open Connections message will appear and you can just click on Yes to close other connections and move on with setting it to a single user mode.
And once you are done with your work, you can set the option back to MULTI_USER using the ALTER DATABASE command or from the GUI (if the GUI is going to be the only session through which you would be connecting):
ALTER DATABASE PLANGOALS SET MULTI_USER