Find and delete incomplete open transactions in SQL Server – Part 2

April 3rd, 2020

In the first part of the series of our articles on this subject, we presented the general algorithm for deleting open transactions that are not completed from a SQL Server database and considered the process of creating a table to record incomplete transactions. Now, let’s look at the process of creating a CRUD stored procedure to find and delete active transactions in SQL Server.

Contents

To perform the remaining steps of the algorithm, we’ll implement the stored procedure [srv]. [AutoKillSessionTranBegin].

Creating a CRUD stored procedure

Updating metadata

So, let’s proceed to creating a CRUD stored procedure. First, we need to make a selection from the table srv.SessionTran created earlier.

SELECT TOP (1000) [SessionID]
      ,[TransactionID]
      ,[CountTranNotRequest]
      ,[CountSessionNotRequest]
      ,[TransactionBeginTime]
      ,[InsertUTCDate]
      ,[UpdateUTCDate]
  FROM [srv].[SessionTran];

Now, hover the mouse cursor over the table. Notice that a hint with the description of the table appears with its columns listed.

A hint containing the information about the table

Fig. 1 A hint containing the information about the table

Please take note of the “test” in the description of the table. In order to give a more objective description of the table, type in “Transactions that do not have active requests and their sessions” in the advanced properties of the table and click the OK button.

Changing the table description

Fig. 2 Changing the table description

You can also add or update the description in this way.

Now, hover the mouse cursor over the table again.

The former description of the table

Fig. 3 The previous description of the table

As you can see, nothing has changed. To see the changes, you need to update the information by updating the local repository of objects for tooltip and metadata for SQL Complete with the help of the following commands.

Updating metadata

Fig. 4 Updating metadata

The Refresh Local Cache command updates the local cache for the current database, and the Reset Suggestions Cache command resets all hints for all databases, forcing the update of the local cache against all the necessary databases.

In our case, we need to click Refresh Local Cache. After that, when you hover the mouse cursor over the srv.SessionTran table, you will see the updated description.

The updated table description

Fig. 5-1 The updated table description

Generating CRUD Code

Let me remind you that CRUD creates 4 stored procedures to manipulate data in a table:

  1. Insert
  2. Select
  3. Update
  4. Delete

We need only one operation out of those.

To customize CRUD, in the CRUD section of the SQL Complete settings, leave only the Include Select Procedure checkmark and click the OK button.

Customizing CRUD in SQL Complete

Fig. 5-2 Customizing CRUD in SQL Complete

Now you need to right-click the table and select the Script Table as CRUD command in the context menu that appears.

Generating a CRUD stored procedure from tables

Fig. 6 Generating a CRUD stored procedure from tables

A script to create a stored procedure to select data from the srv.SessionTran table will be generated automatically.

USE SRV;
GO

IF OBJECT_ID('srv.usp_SessionTran_Select') IS NOT NULL
BEGIN 
    DROP PROC srv.usp_SessionTran_Select 
END
GO
CREATE PROC srv.usp_SessionTran_Select
    @SessionID int,
    @TransactionID bigint
AS
    SET NOCOUNT ON 
    SET XACT_ABORT ON  

    BEGIN TRAN

    SELECT SessionID, TransactionID, CountTranNotRequest, CountSessionNotRequest, TransactionBeginTime, InsertUTCDate, UpdateUTCDate 
    FROM   srv.SessionTran
    WHERE  SessionID = @SessionID AND TransactionID = @TransactionID  

    COMMIT
GO

-- Insert procedure was excluded by the option

-- Update procedure was excluded by the option

-- Delete procedure was excluded by the option

Now let’s change the name of the stored procedure from “usp_SessionTran_Select” to “AutoKillSessionTranBegin” and delete the contents of the stored procedure.

IF OBJECT_ID('srv.AutoKillSessionTranBegin') IS NOT NULL
BEGIN 
    DROP PROC srv.AutoKillSessionTranBegin 
END
GO
CREATE PROC srv.AutoKillSessionTranBegin
    @SessionID int,
    @TransactionID bigint
AS
    SET NOCOUNT ON 
    SET XACT_ABORT ON  
GO

Query Analysis

To perform query analysis, you need to open a new window in SSMS and type the following script there.

SELECT session_id, request_id, status, command, sql_handle, wait_time, cpu_time, reads, writes, logical_reads, database_id
FROM sys.dm_exec_requests

Now, run the script. It displays information about all queries.

With the Grid aggregates feature of SQL Complete, you can select the range of values you are interested in and get their summary value.

Analyzing logical reads for queries

Fig. 7 Analyzing logical reads for queries

In this example, we identified 5 indicators that concern us (in the logical_reads column) for queries coming from sessions with identifiers 9, 10, 11, 12, and 13.

It is easy to notice that the maximum value among the selected ones is 1219, the minimum value is 0, the average value equals (1219 + 0 + 0 + 0 + 64) / 5 = 256.6, and the summary value is 1219 + 0 + 0 + 0 + 64 = 1283, the number the selected items totals to 5, and the number of unique values totals to 3.

All this information is calculated and displayed at the bottom of the Results Grid upon request. The calculating aggregates feature behaves similarly when selecting cells in other columns if necessary. The DISTINCT parameter can help you understand, for instance, how many databases have queries (by the database_id column).

Detecting the number of databases that have queries

Fig. 8 Detecting the number of databases that have queries

You can also view the contents of the cell in the appropriate format (XML, CSV, HTML, JSON, binary, etc.).

For example, for sql_handle the value will be displayed in an easy to read hexadecimal format.

Viewing the cell value in a readable form

Fig. 9 Viewing the cell value in a readable format

Also, if necessary, data can be saved to a file or exported to any of the following formats: CSV, XML, HTML, JSON.

Exporting cell contents

Fig. 10 Exporting cell contents

You can find more information about working with the data in a results grid here.

Finding SQL Server incomplete transactions

Having analyzed the requests, their statuses and sessions, open a new window and type the following scripts to find open transactions in SQL Server that are left incomplete.

declare @tbl table (
						SessionID int,
						TransactionID bigint,
						IsSessionNotRequest bit,
						TransactionBeginTime datetime
					   );

With this script, we collect information about SQL Server active transactions and their sessions that have no requests, i.e., transactions that were launched and left forgotten.

Please, follow the link to see the code example.

To verify the script, select a code fragment that identifies lost transactions and execute it by selecting the Execute Current Statement command in the SQL Complete menu.

Executing the code snippet

Fig. 11 Executing the code snippet

Let’s make sure the selection works correctly.

The result of executing the code snippet

Fig. 12 The result of executing the code snippet

You can also run the script to the cursor position. To achieve that, place the cursor in the right place and select the Execute To Cursor command in the SQL Complete menu.

Executing a script to a cursor

Fig. 13 Executing a script to a cursor

In this case, the @tbl table variable will be created and the information on incomplete transactions will be inserted into it.

The result of code execution to a cursor

Fig. 14 The result of code execution to a cursor

While we were working, a long-running query to check the integrity of the database, launched in another window, was executed. SQL Complete notifies us of this with a message at the bottom right of the screen.

Query execution notification

Fig. 15 Query execution notification

This pop-up window shows the result of the query execution (a green checkmark indicates success, while a red cross indicates failure). The window also displays the name of the tab or file that contains the script for execution and the total execution time of the script. By double-clicking this window, we will automatically be transferred to the tab with the executed script.

Now let’s get back to our stored procedure.

We’ve checked the script and made sure that it selects incomplete transactions. Therefore, we can insert this code into the created stored procedure with the help of the ALTER command.

Inserting the first part of the code into the body of the stored procedure

Fig.16 Inserting the first part of the code into the body of the stored procedure

Now let’s apply formatting to the selected code fragment.

Formatting a code snippet

Fig. 17 Formatting a code snippet

As a result, we will get a more comprehensible script.

The result of formatting a code snippet

Fig. 18 The result of formatting a code snippet

Now we need to change the alias name from ta to dtat. To do this, change the name directly in the code and note that SQL Complete suggests pressing F2 to change the alias name throughout the entire query.

Updating the alias name

Fig. 19 Updating the alias name

Having changed the alias name, press the F2 button and then, after previewing the changes, click Apply.

Updating the alias name throughout the entire query

Fig. 20 Updating the alias name throughout the entire query

It is easy to see that aliases have been renamed successfully throughout the query.

The result of updating the alias name throughout the stored procedure code

Fig. 21 The result of updating the alias name throughout the stored procedure code

You can also rename the alias using the Rename command in the SQL Complete menu.

Selecting the Rename command in the SQL Complete menu

Fig. 22 Selecting the Rename command in the SQL Complete menu

Updating a table containing incomplete open transactions

Now we need to update the srv.SessionTran table according to the current lost transactions. To do this, type the following script.

--update the table of running transactions that have no active queries
	;merge srv.SessionTran as st
	using @tbl as t
	on st.[SessionID]=t.[SessionID] and st.[TransactionID]=t.[TransactionID]
	when matched then
		update set [UpdateUTCDate]			= getUTCDate()
				 , [CountTranNotRequest]	= st.[CountTranNotRequest]+1			
				 , [CountSessionNotRequest]	= case when (t.[IsSessionNotRequest]=1) then (st.[CountSessionNotRequest]+1) else 0 end
				 , [TransactionBeginTime]	= coalesce(t.[TransactionBeginTime], st.[TransactionBeginTime])
	when not matched by target and (t.[TransactionBeginTime] is not null) then
		insert (
				[SessionID]
				,[TransactionID]
				,[TransactionBeginTime]
			   )
		values (
				t.[SessionID]
				,t.[TransactionID]
				,t.[TransactionBeginTime]
			   )
	when not matched by source then delete

You can also apply formatting to it, if necessary. As a result, we got the following stored procedure.

 Updated Stored Procedure (Part 1)

Fig. 23 Updated Stored Procedure (Part 1)

Updated Stored Procedure (Part 2)

Fig. 24 Updated Stored Procedure (Part 2)

Adding the semicolon character to the end statement

To insert the semicolon character at the end of each statement, select the Insert Semicolons command in the SQL Complete menu.

Selecting  the Insert Semicolons command from the SQL Complete menu

Fig. 25 Selecting the Insert Semicolons command from the SQL Complete menu

The result of inserting the semicolon character at the end of each statement (part 1)

Fig. 26 The result of inserting the semicolon character at the end of each statement (part 1)

The result of inserting the semicolon character at the end of each statement (part 2)

Fig. 27 The result of inserting the semicolon character at the end of each statement (part 2)

Navigating between blocks of code

It’s a common situation that you need to go forth to the end of a BEGIN\END block. With SQL Complete, this can be done in the following way:

     1) move the cursor to the desired BEGIN

     2) press SHIFT + F12 or select the Jump Between Syntax Pairs command in the SQL Complete menu.

Selecting the Jump Between Syntax Pairs command in the SQL Complete menu

Fig. 28 Selecting the Jump Between Syntax Pairs command in the SQL Complete menu

Going to definition for SQL objects

If you need to go to the definition of a database object, move the cursor to the desired object and press F12. For example, we want to go to the srv.SessionTran table definition.

Going to object definition

Fig. 29 Going to object definition

You can also do this by selecting the Go To Definition command in the SQL Complete menu.

Selecting the Go to Definition command in the SQL Complete menu

Fig. 30 Selecting the Go to Definition command in the SQL Complete menu

It is worth noting that this functionality works well both between different databases and different instances of MS SQL Server.

Implementing the remaining part of the stored procedure

Now we need to supplement the stored procedure with the code by changing the names of the input parameters, as well as by formatting its entire body (with the Format Document command) and inserting the missing semicolon characters at the end of each statement (with the Insert Semicolons command).

The updated stored procedure (part 1)

Fig. 31 The updated stored procedure (part 1)

The updated stored procedure (part 2)

Fig. 32 The updated stored procedure (part 2)

The updated stored procedure (part 3)

Fig. 33 The updated stored procedure (part 3)

The updated stored procedure (part 4)

Fig. 34 The updated stored procedure (part 4)

and so on. Please, follow the link to see the entire listing of the stored procedure.

Renaming the stored procedure input parameters

Since we were in a hurry, the input parameters names chosen were not the best ones, so we need to rename them. When changing the name of the input parameter, SQL Complete suggests pressing F2 to rename the parameter throughout the entire stored procedure.

Renaming the input parameter

Fig. 35 Renaming the input parameter

In the same way as before, we’ll press F2 after changing the parameter name and then click the Apply button. This will rename all the occurrences of this parameter.

Changing the input parameter name throughout the entire stored procedure

Fig. 36 Changing the input parameter name throughout the entire stored procedure

Thus, we get the variable name changed from @minuteOld2 to @minute everywhere.

Now let’s rename @minute to @minuteOld and @countIsNotRequest2 to @countIsNotRequest in the same way.

Through this process, we get the final version of the srv.AutoKillSessionTranBagin stored procedure for deleting incomplete transactions. Please, follow the link to see its code.

You can read more about renaming with SQL Complete here и here.

Navigating the Stored Procedure code

The stored procedure turned out to occupy more than one window. The code is long enough and it would be nice to have some kind of navigation through it. Fortunately, SQL Complete has the Document Outline command.

Selecting the Document Outline command in the SQL Complete menu

Fig. 37 Selecting the Document Outline command in the SQL Complete menu

After selecting the command, the navigation tree for our stored procedure code appears on the right.

Stored Procedure code navigation

Fig. 38 Stored Procedure code navigation

Using the navigation tree, you can go to any block or to any statement.

Going to the right place in the stored procedure code

Fig. 39 Going to the right place in the stored procedure code

For more details, please refer to the Document outline window section in the SQL Complete product information.

Extending the solution

Now this solution can be extended to all necessary hosts. This can be done in many ways, but the fastest one is to use a comparator tool to compare schemas, as described here.

Conclusion

The article offers a worked example of the automatic deletion of incomplete open transactions on SQL Server. The SQL Complete tool allows for automating the process of deleting incomplete open transactions – it eliminates the attempts of increased blocking fluctuations that are caused by deleted transactions. As a consequence, SQL Server DBMS performance is protected from the impact of incomplete open transactions, which could become lost in the future. The solution is illustrated with code examples and descriptive screenshots and can be extended to any number of hosts.

Evgeniy Gribkov

Latest posts by Evgeniy Gribkov (see all)

Leave a Comment