Setting Up Alerts for Long-Running Transactions

Ref:

http://sqlmag.com/blog/setting-alerts-long-running-transactions

The code below is something you can use to easily set up an alert for long-running transactions:

/* NOTE: You have to configure/set the following 3 variables */
DECLARE @AlertingThresholdMinutes int = 10;
DECLARE @MailProfileToSendVia sysname = 'General';
DECLARE @OperatorName sysname = 'Alerts';

-------------------------------------------------------------
SET NOCOUNT ON;

DECLARE @LongestRunningTransaction int;
SELECT
        @LongestRunningTransaction =
                MAX(DATEDIFF(n, dtat.transaction_begin_time, GETDATE()))
FROM
        sys.dm_tran_active_transactions dtat
        INNER JOIN sys.dm_tran_session_transactions dtst
                ON dtat.transaction_id = dtst.transaction_id;

IF ISNULL(@LongestRunningTransaction,0) > @AlertingThresholdMinutes BEGIN

        DECLARE @Warning nvarchar(800);
        DECLARE @Subject nvarchar(100);

        SET @subject = '[Warning] Long Running Transaction On ' + @@SERVERNAME;
        SET @Warning = 'Check SSMS > Server > Reports > Top Transactions By Age.';
       
        EXEC msdb..sp_notify_operator
                @profile_name = @MailProfileToSendVia,
                @name = @OperatorName,
                @subject = @subject,
                @body = @warning;
END

Where you’ll just need to specify the name of the operator you’d like to notify (along with the Mail Profile to use to do so—all of which you can learn about via Books Online and from my previous post on favoring notification of operators over sending emails directly). You’ll also want to specify how many minutes constitute a 'long-running transaction.' This'll obviously vary from one server/workload to the next and from environment to environment. My recommendation though (with this and with all forms of alerting) is to make sure you don’t set this value so low that you’re constantly getting alerts or notifications—alerts and notifications are useless if you train yourself to ignore them because they occur to frequently.

Otherwise, once you’ve specified all of the parameters as needed you’ll then want to create a new SQL Server Agent job that runs every few minutes (where the actual frequency will depend upon your @AlertingThresholdMinutes value) so that you’ll be notified if/when something goes over your specified threshold.

Why Would I Want to Set Up Alerts for Long-Running Transactions

Of course, knowing how to set up alerts for long running transactions (and I’m SURE there are multiple ways to tackle this need) doesn’t exactly explain why you’d want to do so—or what the benefits are.

Simply put, there are a number of benefits you can receive by setting up alerts for long running transactions—simply because long-running transactions can either cause so many potential (locking/blocking) problems in many cases and/or because the presence of long-running transactions on many severs can be an indication of problems or issues. For example, I had a similar alert set up on a client’s box—which recently started sending alerts (during the middle of the night).

A bit of investigation revealed that the culprit was a batch-processing job being run late at night to get rid of non-valid users on an e-commerce site. The job in question typically ran for about 2-3 minutes most nights (and had done so for a very long time). But, recent changes to the logic to determine non-valid users had included a JOIN against a site-log tracking table to review which pages each user had visited as part of establishing their validity. That table, in turn, was missing some key indexes, had been experiencing serious growth, and had—in turn—caused this job to start taking over 3 hours because of the missing index and large amount of data. All of which was made ‘visible’ by virtue of a long-running transaction alert. (Consequently, and with a bit of index tuning, the job was back down to running at a more respectable time of 5-7 minutes every night.)

The point, however, is that the process and server in question wasn’t a huge priority or concern—but aspects of these related operations HAD managed to start bloating to the point where they were impacting other operations. And a simple alert for long-running transactions made this all visible.