How to Kill all Processes that has Open Connection in a SQL Server database[best Practices for closing database links]-excerpt from the network

SQL Server database administrators frequently need in especially development and test environments instead of the pro Duction environments to kill all the open connections to a specific database in order to process SQL Server main Tenance task over the SQL Server database.

In such situations if you need to kill or close all the active or open connections to the SQL Server database, your may Manage this task is using the Microsoft SQL Server Management Studio or by running T-SQL commands or codes. Actually, this task can is 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'll see the Activity Monitor objec T in the Object Explorer screens 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 T o Display the activities to is monitored on the Activity Monitor screen.

As seen on below your can monitor and view process ID ' s and process details on the list of prcesses running on the Databas e instance. If you want your can filter processes based on specific the values like user, database or status.

Note that default view if displayed the screen is first opened are filtered only for non-system processes which means SY Stem processes which own the first reserved ProcessID ' s is not listed in the view by default. You can view system processes by removing the filter on "Show system processes" under 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'll be asked-a confirmation to kill the related process and then'll kill the open connection to the database Over this process. This action was just like running to kill SQL process T-SQL command for a single process.

A second method which I do not recommend but can is used in some situations could be using the Detach Database screens to Dr OP connections and detaching the database and then re-attaching the database. You can open the Detach Database screens from the context menu displayed to a right click on the related daabase for exam Ple for the below screens shot the name of the database is Works. On the menu, highlight menu item, and then select the Detach ... menu item. This selection would open the Detach Database dialog screen. Note that if in the message column it's declared that active connections exists as for our case the number of active co Nnections is 2, you are not being 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 would f Ail

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 are selected you can successfully detach the database. Then you'll have to re-attach the database by selecting the Attach command from the context menu item displayed the Databases node of the SQL Server instance.

For SQL Server, the default behaviour was different than the SQL Server 2005. Because in SQL Server #, when you run the Detach command from the menu item, your is prompted if you want to drop all Active connections. Then you can confirm closing of any open connections, but the nice thing is so you can cancel detach process after the Open connections is dropped or closed. But for SQL Server 2005, this behaviour was not valid.

How to Kill all Processes using T-SQL Code

By using T-SQL commands or SQL codes, similarly closing connections can 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 pro  Cesses. This method is 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 is connected to the SQL database named @DatabaseName except The process, 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 the SQL Kill SPId command. Within the SQL cursor which loops through each process in sysprocesses view, each time a TSQL Kill process CO Mmand is called passing, the SPId as an argument. And after the Kill SPId SQL statement is executed for each process, the all database connections is dropped.

SQL Server database administrators can use below T-SQL script in order to drop connections SQL Server, or SQL Server 2005 and also drop connections to SQL Server database.

DECLARE @DatabaseName nvarchar (DECLARE) @SPId int DECLARE @SQL nvarchar (+)
--set @DatabaseName = N ' Adventu reWorks2008 ' 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 I NTO @SPId
While @ @FETCH_STATUS = 0 BEGIN  set @SQL = ' KILL ' + CAST (@SPId as nvarchar (ten))  print @SQL   ; EXEC sp_executesql @SQL  --kill @SPId-causing incorrect syntax near ' @spid '.
 fetch NEXT from My_cursor to @SPId END
CLOSE my_cursor deallocate my_cursor

Update:please Note that I altered the above T-SQL script and removed KILL @SPId SQL statement. It was causing the below error. MSG 102, Level 1 , state incorrect syntax near ' @SPId '. This is because the SQL ProcessID can isn't used using a variable with SQL KILL command. The solution is using the dynamic T-SQL statement as shown in the above SQL cursor code.

A second-to-drop all active connections of a database can is 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 create a SQL stored procedure this drops all existing active connections. You could pass SQL database name or database ID as parameter or use the current database information to kill processes Excep T its own process, etc.

How to Kill all Processes that has Open Connection in a SQL Server database[best Practices for closing database links]-excerpt from the network