Automatic Deletion of Forgotten Transactions in MS SQL Server

Total: 4 Average: 4

Introduction

It is often the case when an MS SQL Server transaction is being forgotten by the initiator. The best example would be the following: a script is executed in SSMS which, via the ‘begin tran’ instruction, starts a transaction and an error occurs; however, ‘commit’ or ‘rollback’ do not go through and the execution initiator has left this query for a long time. As a result, more and more fluctuation appears when it comes to blocking the queries which request access to closed-off resources (tables and server resources such as RAM, CPU and the input-output system).

In this article, we’ll look at one of the ways you can automate the forgotten transaction deletion process.

The solution

Let’s define a forgotten transaction as an active (currently executed) transaction which, during a sufficiently large span of time T, has no active (currently executed) queries.

Here’s the general algorithm for deleting such transactions:

  1. Creating a table to store and analyze information about currently forgotten transactions as well as a table to sort and archive the transactions selected from the first table by deletion actions.
  2. Gathering information (transactions and their sessions which have no queries, i.e., the transactions that have been executed and forgotten within a specified timespan T.
  3. Refreshing the table containing all currently forgotten transactions we got in step 1 (if a forgotten transaction has acquired an active query, then such a transaction will be deleted from this table).
  4. Retrieving the sessions we need to kill (a session has at least one transaction that was placed as forgotten into the table from step 1 K or more times and the session had a missing active query the same amount of times).
  5. Archiving the data that we’re going to delete (details about the sessions, connections and transactions that will be killed).
  6. Deleting the selected sessions.
  7. Deleting the processed entries along with those that cannot be removed and have been in the table from step 1 for too long.

Now, let’s see how we can implement this algorithm.
First of all, we’ll need to create a table to store and analyze the information about all currently forgotten transactions:

Here:

1) SessionID — session identifier
2) TransactionID — forgotten transaction identifier
3) CountTranNotRequest — the amount of times a transaction has been registered as forgotten
4) CountSessionNotRequest — the amount of times a session without active queries has been registered and had a forgotten transaction
5) TransactionBeginTime — date and time of the forgotten transaction’s initiation
6) InsertUTCDate — date and time of entry creation (UTC)
7) UpdateUTCDate — date and time of entry update (UTC)

Next, we’ll create a table to archive and sort the transactions from the first table by deletion actions:

Code

Here, all fields are taken from the ‘sys.dm_exec_sessions’  and  ‘sys.dm_exec_connections’ system representations, and ‘InsertUTCDate’ specifies the UTC time the entry was created.

Then, to complete the remaining steps, let’s implement the [srv].[AutoKillSessionTranBegin] stored procedure as follows:

Code

Step 7 of the algorithm is implemented through one of these two counters – CountTranNotRequest or CountSessionNotRequest – reaching a value of 250.

The result

In this article, we’ve looked at an implementation of a process which automatically deletes forgotten transactions.

This method allows us to automate the forgotten transaction deletion process. This results in decreasing or stopping the fluctuation growth in the blocking produced by such transactions. So, the DBMS performance is protected from the actions which can result in forgotten transactions.

Sources:

» sys.dm_exec_requests
» sys.dm_tran_active_transactions
» sys.dm_tran_session_transactions
» sys.dm_exec_sql_text
» sys.dm_exec_sessions
» sys.dm_exec_connections
» KILL

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