How to Kill All Processes That Have Open Connection in a SQL Server Database[关闭数据库链接 最佳方法] -摘自网络
时间:2022-03-14 00:19
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.
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.
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)
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.