A long running transaction keeps the transaction log active from the virtual log file containing the first log record of the transaction. Truncation cannot occur from that virtual log file onward. It may leads to LOG FULL errors and finally impacts the complete database operations. So proper monitoring of long running transactions is critical.

We can discover long running transactions with DMV “sys.dm_tran_database_transactions”. For more info see http://technet.microsoft.com/en-us/library/ms186957%28v=sql.105%29.aspx

Note: To run this DMV Requires VIEW SERVER STATE permission on the server

We can also use DBCC command for the task

DBCC OPENTRAN

The above commands will only provide the details of a long running transaction when we execute them. If a proactive mechanism to monitor long running transactions is required, we need to set up a SQL Agent job to run for every minute. The scheduling can change and be based on user requirements.  The job setup and frequent execution might cause performance issues, but there might be also be a chance of missing out on a long running transactions if scheduling is too infrequent.

To have more proactive way of monitoring long running transactions, we can use SQL Server Agent alerts. Here is a step by step procedure to set up these alerts.

Firts, create a new alert, as shown below.

Enter a name for the Alert. Set the Type to “SQL Server performance condition alert”.

Select Transactions as the Object and set the Counter to Longest Transaction Running Time. Set Alert to fire if the counter rises above to 15 secs. The value of your environment will dictact where you set this value.

NOTE: This 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, we can trigger an email to an operator and execute a job.

To get information about transactions that are long running, create a job with code below and call it from alert. Send the results of this query in mail using sp_send_dbmail

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

We can also include a custom message in the email alert as shown below

We can include a delay between alerts responses as shown below.

We can verify the alert count in the History page

If alert is not required we can disable the alert.

Once the alert is set and in enabled state, it will get triggered when any transaction crosses the threshold set by this alert. Since email will be delivered to the DBA, so required action can be taken.