How To Set up an Alert for Any User Traces Being Run on Your SQL Servers

It is common knowledge that running a trace on your production SQL Servers is a bad idea. Especially if they are being run via the SQL Profiler GUI.

What you can do is set up a simple SQL Agent job that does a check every 30 minutes or so for any user traces running, and sends you an email.

This will ensure that a junior DBA, or someone else that has access, cannot run a trace and then forget about it and leave it running without you finding out about it quickly.

The code below assumes that you already have the SQL Server default trace running. If you do not just change the > 1 to > 0.

--- set this up inside a SQL Agent job

IF (SELECT count(1) FROM sys.traces (nolock)) > 1 
    BEGIN 
    DECLARE @subjectstring VARCHAR(MAX) 
    SET @subjectstring = 'Alert User Trace Running could compromise performance [EOM] - ' + @@SERVERNAME 
     EXEC msdb.dbo.sp_send_dbmail 
          @profile_name = 'profile name', 
          @recipients = 'youremail@email.com', 
          @subject = @subjectstring, 
          @importance = 'HIGH';

    END

The more proactive measures you can set up like this, that are watching and monitoring your SQL Servers automatically, the faster you can act to reduce the likelihood of performance issues.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply