Long running transactions can have a significant impact on the performance and stability of a SQL Server database. When a transaction remains active for an extended period of time, it prevents the transaction log from being truncated, leading to potential log full errors and disruptions to database operations. Therefore, it is crucial to have a proper monitoring mechanism in place to identify and address long running transactions.

In SQL Server, one way to discover long running transactions is by using the dynamic management view (DMV) called “sys.dm_tran_database_transactions”. This DMV provides information about active transactions in the database. However, it requires the VIEW SERVER STATE permission on the server to run. Another option is to use the DBCC OPENTRAN command, which displays information about the oldest active transaction in the database.

While these commands can provide details about long running transactions when executed, a more proactive approach is to set up a SQL Server Agent job to run at regular intervals, such as every minute. This job can help identify long running transactions in a timely manner. However, it is important to consider the potential performance impact of frequent job execution and ensure that the scheduling meets the specific requirements of the database.

To further enhance the proactive monitoring of long running transactions, SQL Server Agent alerts can be utilized. Here is a step-by-step procedure to set up these alerts:

1. Create a new alert with a descriptive name.
2. Set the Type to “SQL Server performance condition alert”.
3. Select “Transactions” as the Object and set the Counter to “Longest Transaction Running Time”.
4. Set the Alert to fire if the counter rises above a specific threshold, such as 15 seconds. The actual value should be determined based on the environment.

It is important to note that the “Longest Transaction Running Time” counter only shows activity when the database is under the read committed snapshot isolation level. It does not log any activity if the database is in any other isolation level.

In response to the alert, actions can be triggered, such as sending an email to a designated operator and executing a job. The following code can be used in a job to retrieve information about long running transactions and send the results via email:

SELECT b.session_id 'Session ID',
       CAST(Db_name(a.database_id) AS VARCHAR(20)) 'Database Name',
       c.command,
       Substring(st.TEXT, ( c.statement_start_offset / 2 ) + 1,
       ( (
       CASE c.statement_end_offset
        WHEN -1 THEN Datalength(st.TEXT)
        ELSE c.statement_end_offset
       END 
       -
       c.statement_start_offset ) / 2 ) + 1)                                                             
       statement_text,
       Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(
       Object_schema_name(st.objectid,
                st.dbid)) +
                N'.' + Quotename(Object_name(st.objectid, st.dbid)), '')    
       command_text,
       c.wait_type,
       c.wait_time,
       a.database_transaction_log_bytes_used / 1024.0 / 1024.0                 'MB used',
       a.database_transaction_log_bytes_used_system / 1024.0 / 1024.0          'MB used system',
       a.database_transaction_log_bytes_reserved / 1024.0 / 1024.0             'MB reserved',
       a.database_transaction_log_bytes_reserved_system / 1024.0 / 1024.0      'MB reserved system',
       a.database_transaction_log_record_count                           
       'Record count'
FROM   sys.dm_tran_database_transactions a
       JOIN sys.dm_tran_session_transactions b
         ON a.transaction_id = b.transaction_id
       JOIN sys.dm_exec_requests c
           CROSS APPLY sys.Dm_exec_sql_text(c.sql_handle) AS st
         ON b.session_id = c.session_id
ORDER  BY 'MB used' DESC

By including this code in the job, the results of the query will be sent via email using the “sp_send_dbmail” stored procedure.

It is also possible to include a custom message in the email alert to provide additional context and instructions for the DBA. Additionally, a delay between alert responses can be introduced to avoid excessive notifications. The alert count can be monitored in the History page to ensure that the alert is functioning as expected. If the alert is no longer required, it can be disabled.

With a well-designed monitoring system in place, long running transactions can be promptly identified and appropriate actions can be taken to mitigate their impact on the database. By proactively managing long running transactions, the overall performance and stability of the SQL Server environment can be significantly improved.