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

Deleting Lost Transactions in MS SQL Server (Part 3)

DZone 's Guide to

Deleting Lost Transactions in MS SQL Server (Part 3)

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

· Database Zone ·
Free Resource

In two previous articles (Part 1, Part 2), we have reviewed a solution for deleting incomplete open transactions in SQL Server. 

By incomplete transaction, we shall basically understand an active (running) transaction that doesn’t have any active (running) queries for some long period of time T.

The general algorithm for deleting incomplete transactions:

  1. Create two tables: one table to store and analyze information about current incomplete transactions and the second one to archive the transactions selected from the first table according to the delete actions for subsequent analysis.
  2. Gather information about transactions and their sessions that have no queries, i.e., transactions launched and left forgotten within a certain timespan T.
  3. Update the table containing current incomplete transactions from step 1 (if an incomplete transaction acquires an active request, then such a transaction is no longer considered to be incomplete and is deleted from the table).
  4. Identify the sessions to be killed (a session has at least one incomplete transaction mentioned in the table from step 1 and there are no queries running for that session as well).
  5. Archive the information you are going to delete (details about the sessions, connections, and transactions that are to be killed).
  6. Kill the selected sessions.
  7. Delete the processed entries along with those that cannot be deleted and have been in the table from step 1 for too long. 

We also reviewed the process of creating a CRUD stored procedure.

Let’s now consider a number of settings in SQL Complete tool that help you work more efficiently.

Marking Execution Environment Using Colors

Let’s create two windows: one for the testing stand and one for the production environment. Now let’s paint every window in proper colors (green for the testing environment, red for the production environment). To do this, we need to right-click on the proper window, select Tabs Color in a drop-down menu, and select a color.

Changing the document window colors

Now we get the colored tabs depending on colors that we selected:

The result of changing the script windows colors of the documents

This functionality can also be changed in SQL Complete\Options\Tabs Color: 

Tabs and instances of color settings

Here you can change, add, or delete colors for specific hosts. 

Tabs Coloring functionality not only allows us to discern environment types (testing, production, etc.) but does the same job for important MS SQL instances.

Importing, Exporting, and Resetting to Default Settings in SQL Complete

Among other settings, SQL Complete lets you adjust import and export parameters:

SQL Complete settings import and export

After you click on “Import and Export Settings…”, the following window pops up:

SQL Complete settings export

This window allows for selecting either import, or export, or reset to default settings. In our case, we go for settings export and press the “Next” button. Then we check proper sections and press the “Next” button:

Selecting the exported data

Then we select the “save” folder, make the file’s name, and press the “Execute” button:

Setting up the file’s destination for exported settings and launching the export process

Once the export is done, we receive a message saying that it’s over and press the “Finish” button.

Finishing the settings export process

Let’s now return to our new stored procedure in two windows created above.

You can read more info about importing, exporting, and resetting to default your SQL Complete settings here

Restoring Documents

To restore your sessions based on windows with scripts, you should use the SQL Complete\Document Sessions command:

Selecting the “Documents Sessions” command in the SQL Complete Main Menu

Once you selected it, the restorable sessions window pops up.

Selecting the sessions to restore

Right-click on the necessary document to restore it:

Restoring the selected document

You can find more information about the Documents Sessions functionality here.

If we accidentally close a window with a script, this won’t be a problem. By using the SQL Complete\Restore Last Closed Document command, you can re-open that window and not lose any of your important scripts:

Selecting the “Restore Last Closed Document” command in the SQL Complete Main Menu

By using the SQL Complete\Recently Closed Documents command, we can do the same trick of restoring any recently closed file:

Selecting the “Recently Closed Documents” command in the SQL Complete Main Menu

In this article, we reviewed a number of productivity features that help you work more efficiently in the process of implementing algorithms for deleting incomplete transactions in SQL Server with the help of SQL Complete.

Read also:

Deleting Lost Transactions in MS SQL Server (Part 1)

Deleting Lost Transactions in MS SQL Server (Part 2)

Topics:
database administration ,databases ,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 }}