{{announcement.body}}
{{announcement.title}}

Deleting Lost Transactions in MS SQL Server (Part 2)

DZone 's Guide to

Deleting Lost Transactions in MS SQL Server (Part 2)

In this article, see part two of how to delete lost transactions in MS SQL Server.

· Database Zone ·
Free Resource

In the first part, 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.

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.

SQL
 







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

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.

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

Now, hover the mouse cursor over the table again.

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.

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.

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.

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

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

SQL
 




x


 
1
USE SRV;
2
GO
3
 
          
4
IF OBJECT_ID('srv.usp_SessionTran_Select') IS NOT NULL
5
BEGIN
6
    DROP PROC srv.usp_SessionTran_Select
7
END
8
GO
9
CREATE PROC srv.usp_SessionTran_Select 
10
    @SessionID int, 
11
    @TransactionID bigint
12
AS
13
    SET NOCOUNT ON
14
    SET XACT_ABORT ON
15
 
          
16
    BEGIN TRAN
17
 
          
18
    SELECT SessionID, TransactionID, CountTranNotRequest, CountSessionNotRequest, TransactionBeginTime, InsertUTCDate, UpdateUTCDate
19
    FROM   srv.SessionTran
20
    WHERE  SessionID = @SessionID AND TransactionID = @TransactionID
21
 
          
22
    COMMIT
23
 
          
24
GO
25
 
          
26
-- Insert procedure was excluded by the option
27
 
          
28
-- Update procedure was excluded by the option
29
 
          
30
-- 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.

SQL
 




xxxxxxxxxx
1
11


 
1
IF OBJECT_ID('srv.AutoKillSessionTranBegin') IS NOT NULL
2
BEGIN
3
     DROP PROC srv.AutoKillSessionTranBegin
4
END
5
GO
6
CREATE PROC srv.AutoKillSessionTranBegin
7
          @SessionID int,
8
          @TransactionID bigint
9
AS
10
     SET NOCOUNT ON
11
     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.

SQL
 




xxxxxxxxxx
1


 
1
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.

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).

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.

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

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.

Java
 




xxxxxxxxxx
1


 
1
declare @tbl table (
2
                                   SessionID int,
3
                                   TransactionID bigint,
4
                                   IsSessionNotRequest bit,
5
                                   TransactionBeginTime datetime
6
                                  );



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.


Let’s make sure the selection works correctly.

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.


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


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.


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.


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


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

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.


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


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


You can also rename the alias using 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:

SQL
 




xxxxxxxxxx
1
21


1
--update the table of running transactions that have no active queries
2
    ;merge srv.SessionTran as st
3
    using @tbl as t
4
    on st.[SessionID]=t.[SessionID] and st.[TransactionID]=t.[TransactionID]
5
    when matched then
6
        update set [UpdateUTCDate]          = getUTCDate()
7
                 , [CountTranNotRequest]    = st.[CountTranNotRequest]+1            
8
                 , [CountSessionNotRequest] = case when (t.[IsSessionNotRequest]=1) then (st.[CountSessionNotRequest]+1) else 0 end
9
                 , [TransactionBeginTime]   = coalesce(t.[TransactionBeginTime], st.[TransactionBeginTime])
10
    when not matched by target and (t.[TransactionBeginTime] is not null) then
11
        insert (
12
                [SessionID]
13
                ,[TransactionID]
14
                ,[TransactionBeginTime]
15
               )
16
        values (
17
                t.[SessionID]
18
                ,t.[TransactionID]
19
                ,t.[TransactionBeginTime]
20
               )
21
    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.

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.

The result of inserting the semicolon character at the end of each statement 

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.

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.

You can also do this by 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). 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.

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.

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.

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


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

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 how to get and delete incomplete open transactions on SQL Server with the help of the SQL Complete tool. The solution is illustrated with code examples and descriptive screenshots and can be extended to any number of hosts.

Topics:
database ,database administration ,sql complete ,sql server ,transaction ,tutorial

Published at DZone with permission of Evgeniy Gribkov . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}