How to Kill All Processes That Have Open Connection in a SQL Server Database
You may frequently need in especially development and test environments
instead of the production environments to kill all the open connections to a
specific database in order to process maintainance task over the SQL Server database.
In such situations when you need to kill or cloase all the active or open
connections to the SQL Server database, you may manage this task by using the
Microsoft SQL Server Management Studio or by running t-sql commands or codes.
If you open the SQL Server Management Studio and connect to a SQL Server
instance you will see the Activity Monitor object in the Object Explorer screen
of the related database instance. You can double click the Activity Monitor
object or right click to view the context menu and then select a desired item to
display the activities to be monitored on the Activity Monitor screen.
As seen on below you can monitor and view process id's and process details on
the list of prcesses running on the database instance. If you want you can
filter processes based on specific values like user, database or status.
Note that default view when displayed the screen is first opened is filtered
only for non-system processes which means system processes which own the first
50 reserved processid's are not listed in the view by default. You can view
system processes by removing the filter on "Show System Processes" criteria in
the filter settings screen.
SQL Server 2005 SQL Server Management Studio Activity Monitor screen
You can kill a process by a right click on the process in the grid and
selecting the Kill Process menu item. You will be asked for a confirmation to
kill the related process and then will kill the open connection to the database
over this process.
A second method which I do not recommend but can be used in some situations
may be using the Detach Database screen to drop connections and detaching the
database and then re-attaching the database. You can open the Detach Database
screen from the context menu displayed by a right click on the related daabase
for example for the below screen shot the name of the database is Works. On the
menu, highlight menu item Tasks then select the Detach... menu item. This
selection will open the detach database dialog screen. Note that if in the
message column it is declared that active connections exists as for our case the
number of active connections is 2, you will not be able to detach the database
unless the Drop Connections checkbox is also selected.
The above configuration as the Drop Connections check box is cleared and
active connections exist, the detach task will fail:
Detach database failed for Server '{DatabaseInstanceName}'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Detach+database+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot detach the database '{DatabaseName}' because it is currently in use.
(Microsoft SQL Server, Error: 3703)
But if the Drop Connections is selected you can successfully detach the
database. Then you will have to re-attach the database by selecting the Attach
command from the context menu item displayed on the Databases node of the SQL
Server instance.
For SQL Server 2000 the default behaviour was different than the SQL Server
2005. Because in SQL Server 2000, when you run the detach command from the menu
item, you are prompted if you want to drop all active connections. Then you can
confirm closing of all open connections, but the nice thing is that you can
cancel detach process after the open connections are dropped or closed. But for
SQL Server 2005, this behaviour is not valid.
By using t-sql commands or sql codes, similarly closing connections can be
implemented by a few methods. One of the methods is using a cursor which loops
for all the active connections of the related database and kill these processes.
This method was also mention on SQL Server article named
How to Alter a SQL
Server Database as Single User Mode and as Multi User Mode
The below code block can be used to kill all the processes which are
connected to the database named @DatabaseName except the process that the code
block is running in the scope of. You can also set the database name by the DB_NAME()
property.
DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
SET @DatabaseName = N'Works'
--SET @DatabaseName = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
KILL @SPId
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_cursor
DEALLOCATE my_cursor
A second way to drop the active connections of a database can be implemented
by generating dynamic sql commands that runs a list of "Kill @spId" commands.
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Works'
--SET @DatabaseName = DB_NAME()
DECLARE @SQL varchar(max)
SET @SQL = ''
SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';' FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
-- SELECT @SQL
EXEC(@SQL)
A very similar to the code above an other code block can be used by using the
COALESCE as shown below
DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Works'
DECLARE @SQL varchar(max)
SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';' FROM
MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
--SELECT @SQL
EXEC(@SQL)
The queries can be modified for specific needs, for example you may create a
stored procedure that drops all existing active connections. You may pass
database name or database id as parameter or use the current database
information to kill processes except its own process, etc.
 BlinkList
 Del.icio.us
 Digg
 Furl
 Simpy
 Spurl
 DZone
 ma.gnolia
 Shadows
|