How to Kill All Processes That Have Open Connection in a SQL Server Database[關閉資料庫連結 最佳方法] -摘自網路

標籤：des   style   http   io   os   ar   strong   for   資料   

SQL Server database administrators 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 SQL Server maintenance task over the SQL Server database.

In such situations when you need to kill or close 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. Actually, this task can be thought as a batch task to kill sql process running on a SQL Server.

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. This action is just like running to kill sql process t-sql command for a single 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.

How to Kill All Processes using T-SQL Code

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 processes which are connected to the sql database named @DatabaseName except the process that the code block is running in the scope of. You can also set the SQL Server database name by the DB_NAME() property.

The base of the below t-sql script is sql Kill SPId command. Within the sql cursor which loops through each process in sysProcesses view, each time a tsql Kill process command is called passing the SPId as an argument. And after Kill SPId sql statement is executed for each process, all database connections are dropped.

SQL Server database administrators can use below t-sql script in order to drop connections SQL Server 2008, or SQL Server 2005 and also drop connections to sql server 2000 database.

DECLARE @DatabaseName nvarchar(50) DECLARE @SPId int DECLARE @SQL nvarchar(100)
--SET @DatabaseName = N‘AdventureWorks2008‘ 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  SET @SQL = ‘KILL ‘ + CAST(@SPId as nvarchar(10))  print @SQL  EXEC sp_executeSQL @SQL  --KILL @SPId -- Causing Incorrect syntax near ‘@spid‘.
 FETCH NEXT FROM my_cursor INTO @SPId END
CLOSE my_cursor DEALLOCATE my_cursor

UPDATE : Please note that I altered above t-sql script and removed KILL @SPId sql statement. It was causing the below error. Msg 102, Level 15, State 1, Line 19 Incorrect syntax near ‘@SPId‘. This is because the sql processid can not be used using a variable with sql KILL command. The solution is using dynamic t-sql statement as shown in the above sql cursor code.

A second way to drop all 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 sql 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 above sql queries can be modified further for specific needs. For example you may create a sql stored procedure that drops all existing active connections. You may pass SQL database name or database id as parameter or use the current database information to kill processes except its own process, etc.

How to Kill All Processes That Have Open Connection in a SQL Server Database[關閉資料庫連結 最佳方法] -摘自網路