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.
- Creating a CRUD stored procedure
- Finding SQL Server incomplete transactions
- Updating a table containing incomplete open transactions
- Implementing the remaining part of the stored procedure
To perform the remaining steps of the algorithm, we’ll implement the stored procedure [srv]. [AutoKillSessionTranBegin].
Creating a CRUD stored procedure
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.
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.
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.
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.
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.
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:
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.
Fig. 5-2 Customizing CRUD in SQL Complete
Now you need to right-click the table and select the Script Table as CRUD command on the shortcut menu that appears.
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
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.
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).
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.
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.
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 on the SQL Complete menu.
Fig. 11 Executing the code snippet
Let’s make sure the selection works correctly.
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 on the SQL Complete menu.
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.
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.
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.
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.
Fig. 17 Formatting a code snippet
As a result, we will get a more comprehensible script.
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.
Fig. 19 Updating the alias name
Having changed the alias name, press the F2 button and then, after prereviewing the changes, click Apply.
Fig. 20 Updating the alias name throughout the entire query
It is easy to see that aliases have been renamed successfully throughout the query.
Fig. 21 The result of updating the alias name throughout the stored procedure code
You can also rename the alias using the Rename command on the SQL Complete menu.
Fig. 22 Selecting the Rename command on 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.
Fig. 23 Updated Stored Procedure (Part 1)
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 on the SQL Complete menu.
Fig. 25 Selecting the Insert Semicolons command from the SQL Complete menu
Fig. 26 The result of inserting the semicolon character at the end of each statement (part 1)
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 on 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.
Fig. 29 Going to object definition
You can also do this by selecting the Go To Definition command on 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).
Fig. 31 The updated stored procedure (part 1)
Fig. 32 The updated stored procedure (part 2)
Fig. 33 The updated stored procedure (part 3)
Fig. 34 The updated stored procedure (part 4)
and so on. 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.
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.
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:
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.
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.
Fig. 38 Stored Procedure code navigation
Using the navigation tree, you can go to any block or to any statement.
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.
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.