Automatic Deletion of Stuck Processes in MS SQL Server

Introduction

There are situations when applications keep the database connection for a long period of time. It seems to be not important. However, if this application makes many connections or there are several applications with such behavior — things are getting worse.

This article is not a tutorial. It describes possible solutions to this issue. As usual, I will be glad to hear any alternative solutions.

Solution

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

This stored procedure helps manually disable all the connections to the database or a certain user for further actions with the database.

2. Create a stored procedure to remove all the stuck processes.

This stored procedure removes the connections that were completed more than 24 hours ago. In addition, this procedure does not affect the main system databases (master, tempdb, model, and msdb). If you try to access a database while the connection is disabled, a new connection for this application will be created.

Now, it is necessary to run a stored procedure in the Agent task once a day:

It would be better to wrap this query into the try-catch block to process a possible call for exceptions.

Result

In this article, I have analyzed how to implement stored procedures on closing a connection to a database (all or a certain user) and to delete stuck processes on a particular example. In addition, I have explored on a particular example how to automatically run a task on the stuck processes deletion on a daily basis. It allows decreasing an amount of ‘dead’ connections to a server. The deletion of all the connections to the database allows you to modify some properties, as well as close the process that causes any issue.

References:

» sysprocesses
» kill
» db_id
» @@SPID

Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.
Evgeniy Gribkov

Evgeniy Gribkov

Evgeniy is a MS SQL Server database analyst, developer and administrator. He is involved in development and testing of tools for SQL Server database management. Evgeniy also writes SQL Server-related articles.