Automatic removal of hung processes in MS SQL Server

Foreword

Often there are situations when some application for some reason holds a connection to the database for a long time. It seems to be a trifle, but if such software makes several connections or, even worse, there are several such applications, it is better to deal with this somehow.

This article is not a guide. In it, I just wanted to show possible solutions to this problem. I would be glad if they propose alternative solutions.

Decision

1) Create a stored procedure that closes all connections or all connections of a specific user to the specified database:

USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[KillConnect] @databasename nvarchar(255), -- @loginname nvarchar(255)=NULL -- AS BEGIN /*          */ SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; if(@databasename is null) begin ;THROW 50000, '   !', 0; end else begin declare @dbid int=db_id(@databasename); if(@dbid is NULL) begin ;THROW 50000, '    !', 0; end else if @dbid <= 4 begin ;THROW 50000, '     !', 0; end else begin declare @query nvarchar(max); set @query = ''; select @query=coalesce(@query,',' ) +'kill ' +convert(varchar, spid) +'; ' from master..sysprocesses where dbid=db_id(@databasename) and spid<>@@SPID and (loginame=@loginname or @loginname is null); if len(@query) > 0 begin begin try exec(@query); end try begin catch end catch end end end END GO 

It is not needed in the decision. This stored procedure helps to manually disconnect all connections to the database or to a specific user for further database manipulations.

2) Create a stored procedure to remove all hung processes:

 USE [__] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [srv].[KillFullOldConnect] AS BEGIN /*   ,       . !   master, tempdb, model  msdb    . ,  distribution       . */ SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; declare @query nvarchar(max); set @query = ''; select @query=coalesce(@query,',' ) +'kill ' +convert(varchar, spid) +'; ' from master..sysprocesses where dbid>4 and [last_batch]<dateadd(day,-1,getdate()) order by [last_batch] if len(@query) > 0 begin begin try exec(@query); end try begin catch end catch end END GO 

This stored procedure deletes all those connections that were last made more than a day ago. Also, this stored procedure does not affect the main system databases (master, tempdb, model, and msdb). Nothing terrible will happen, because if access is requested and the connection is disconnected, then a new connection will be created for this application with the requesting user.

And now the stored procedure from item 2 is enough to run once a day in the task of the Agent:

 exec [__].[srv].[KillFullOldConnect]; 

It is better, of course, to impose this call on a try-catch block in order to handle possible exception calls.

Result

This article has reviewed an example of the implementation of stored procedures for closing a connection to a database (all or a particular user) and completing hung processes. An example of the automatic daily launch of the task of terminating hung processes was also considered. This reduces the number of dead connections to the server. Removing all connections to the database allows you to change some properties for it, as well as immediately kill the process that creates a problem.

Sources:

» Sysprocesses
Kill
» Db_id
" @@ SPID