Determining long-running transactions

Long-running transactions block the other transactions and in turn introduce new long-running transactions! This affects the performance of the database server.

As a DBA, you should regularly monitor your database transactions and should take necessary remedial steps whenever you identify such long-running transactions, as they can degrade the performance of the application drastically.

In this recipe, you will see how you can monitor the transactions by looking at their time duration. If you frequently find some transactions running for a long time, you may probably want to find if they are blocked by other transactions. You may also probably look into the query to investigate which statements of the transaction are taking more time and why, so that you can know which part of the T-SQL code should be modified accordingly.

Getting ready

This will be a very simple recipe that will show you how to track the transaction time. With the script that has been...