Setting Up Alerts for Long-Running Transactions

Microsoft-SQL-Server-2012

If you have a long running transaction, you might want to send out send out an alert. You will need to define values for what amount of time constitutes a long running transaction (shown as 10 minutes in the example script), who is to get the email (sent to the General email address in the example script) and what operator is to be notified (Alerts in the operator in the example script). 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. Don’t forget to test, test, test.

/* 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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s