Deleting Lost Transactions in MS SQL Server
Deleting Lost Transactions in MS SQL Server
In this article, take a look at a tutorial that explains deleting lost transactions in MS SQL Server.
Join the DZone community and get the full member experience.Join For Free
Quite often, transactions in MS SQL Server are lost by those who launched them. It is not infrequent that one runs a script in SSMS, which starts an explicit transaction with the BEGIN TRANSACTION statement, but then an error occurs and COMMIT TRANSACTION or ROLLBACK TRANSACTION fails while the transaction initiator has left this query for some time. Transactions left forgotten for long periods of time can prevent users from accessing the locked resources (tables, server resources (RAM, CPU, I / O system).
In this article, we will consider how to delete lost transactions using SQL Complete.
By lost transaction, we shall basically understand an active (running) transaction that doesn’t have any active (running) queries for some long period of time T.
A General Algorithm for Deleting Lost Transactions
Here’s the general algorithm for deleting the lost transactions:
- Create two tables: one table to store and analyze information about current lost transactions and the second one to archive the transactions selected from the first table according to the delete actions for subsequent analysis.
- Gather information about transactions and their sessions that have no queries, i.e., transactions launched and left forgotten within a certain timespan T.
- Update the table containing current lost transactions from step 1 (if a lost transaction acquires an active request, then such a transaction is no longer considered to be lost and is deleted from the table).
- Identify the sessions to be killed (a session has at least one lost transaction mentioned in the table from step 1 and there are no queries running for that session as well).
- Archive the information you are going to delete (details about the sessions, connections, and transactions that are to be killed).
- Kill the selected sessions.
- Delete the processed entries along with those that cannot be deleted and have been in the table from step 1 for too long.
Now, let’s see how we can implement this algorithm.
You may also want to read: Recover Deleted Data From SQL Table Using Transaction Log and LSNs
Implementing the Algorithm for Deleting Lost Transactions
A Table to Store and Analyze Lost Transactions
Let's create a table to store and analyze information about current lost transactions using dbForge SQL Complete:
1. A drop-down suggestions list helps quickly make a table.
2. SQL commands are converted to uppercase:
Thus, we complete the script to create the table we need:
1) SessionID is a session identifier
2) TransactionID is a lost transaction identifier
3) CountTranNotRequest is the number of times the transaction was recorded as lost
4) CountSessionNotRequest is the number of times the session was recorded as having no active queries and containing a lost transaction
5) TransactionBeginTime is lost transaction start date and time
6) InsertUTCDate is UTC date and time when the record was created
7) UpdateUTCDate is UTC date and time when the record was changed.
Formatting the Document
Formatting the Current Document
If the text was typed in another editor or without the use of SQL Complete, you can format it with the help of the SQL Complete \ Format Document option:
Below, you can see how the text looked like before formatting:
This is how the code looks like after applying formatting:
You can easily notice the difference. The T-SQL keywords are now capitalized. The indentation was applied and each column now starts from a new line. That evidently provided a more readable code, which results in better perception and quicker decision-making.
Formatting External Documents
SQL Complete allows formatting the selected code fragment. The Format Selection option becomes active after you select the code fragment to be formatted:
In the picture below, you can see how the code fragment looked like before formatting:
After the formatting has been applied, the fragment looks as follows:
You can also format previously written scripts using the SQL Formatter option:
In the window that appears, you can select to format either files or directories. In our case, we select Files and click the Next button:
After that, you need to add the necessary files by clicking the Add button:
Next, we format the selected files by clicking the Format button:
After that, formatting starts:
After the formatting process finishes, a completion window will be displayed and the selected files with modified scripts will open in SSMS:
Similarly, we beautify the code for the entire Scripts directory.
To do this, after selecting the SQL Formatter option in the SQL Complete menu, choose Directories and click the Next button:
Then, you need to click the Add button again to select the necessary directories. It is also worth paying attention to the lower-left corner of the SQL Formatter Wizard, where you can select file extensions to be processed and whether to include sub-folders or not.
After the formatting process finishes, the files will also open in SSMS:
It is worth mentioning that after the formatting, the files are not saved, but opened in a modified form instead. To save the files, you need to save them in the SSMS environment. To change that, unselect the checkbox Keep modified files open after formatting (selected by default). If you disable this checkbox, then the formatting changes will be applied and saved immediately and files won’t open in SSMS.
For more advanced formatting options, you can refer to the SQL Complete options:
Then, you need to go to the Formatting tab:
The tab has two sub-tabs:
The General sub-tab contains basic formatting settings.
The Profiles sub-tab contains a list of formatting profiles in XML format. On this tab, you can also create a new profile, edit the existing one, activate the desired profile, and open the folder with profiles.
To edit a profile, you need to select the required profile and either click the Edit Profile button or double-click the profile itself. After that, you will have an opportunity to check the profile rules and make the necessary changes if needed.
The formatting feature of SQL Complete greatly simplifies both the code development and code maintenance.
Enabling and disabling SQL Complete
In some cases, the SQL Complete tool can be disabled to speed up the IDE. This can be easily done by clicking the Disable Code Completion option in the SQL Complete main menu:
You can enable SQL Complete again by clicking Enable Code Completion option in the SQL Complete main menu:
Now then, we’ve created a table for fixing killed sessions for lost srv.SessionTran transaction
Creating a Table for Archiving Lost Transactions by Delete Actions
Similarly, for further analysis, we need to create a table for archiving transactions selected from the first table according to the delete actions: The complete script on GitHub.
sys.dm_exec_sessions and sys.dm_exec_connections are the system views
InsertUTCDate is UTC date and time when the record was created
So, in this article, we considered the general algorithm for deleting the lost transactions in MS SQL Server and how this algorithm can be implemented using dbForge SQL Complete. As a result, we have created the table for fixing killed sessions and created the table of archived transactions for further analysis.
Opinions expressed by DZone contributors are their own.