Sql-server – Notify on SQL trace event starting

If you need the data changes themselves then you will probably need to use Triggers. You could convert all values to string, build an HTML table of those changes, and then send that via sp_send_dbmail. Please note that while sp_send_dbmail does accept a query to include the results of in the email (either in the body or as an attached file), it runs in a separate session so it won't have access to the INSERTED or DELETED pseudo-tables, nor even local temporary tables (and global temporary tables either might not exist when the query is run or it would at least interfere with multiple DML operations happening at the same time, even if those are unlikely). You could also include the login information from SESSION_USER and ORIGINAL_LOGIN().

Regarding performance and Triggers, there doesn't seem to be much reason for concern:

• These are lookup tables and there aren't that many changes coming through.
• The sp_send_dbmail command runs asynchronously via Service Broker.

Another option is to track the data automatically in your version control system (e.g. TFS). This does not mean that the changes are introduced automatically into release scripts, but the changes are tracked so that you can investigate what changed and then figure out how to best get it scripted into your release process. I wrote an article on SQL Server Central several years ago about doing just this: Exporting and Versioning Lookup Data: A Real-World Use of the CLR (free registration is required to read content on that site). That article discusses using a SQLCLR Stored Procedure to do a daily/nightly export of the lookup tables into delimited files. Please note that the SQLCLR Stored Procedure, DB_BulkExport, is found in the SQL# library (that I created), and was available in the Free version when the article was written, but is now only in the Full version and no longer available for free. However, there is always the option of writing ones own SQLCLR Stored Procedure to do the same thing.

The system that I developed and discussed in that article is based upon running via a SQL Server Agent job once per night. But we had hundreds of lookup tables. The effects of running nightly are that you miss incremental changes made during the day, and you aren't able to capture who made those changes. If you need each individual change and who did it, then you can rethink that approach to get kicked off from a Trigger that exports the data and calls whatever external program handles the commit into the versioning system. A SQLCLR Trigger might be able to accomplish the export and calling the "commit" program all at the same time since it does have access to the INSERTED and DELETED tables.

Well, if you're going to pull this information from the default trace, you need to (1) combine the info such as database name, size of growth, etc. into a single string to pass to xp_logevent, and (2) deal with multiple rows (since multiple events may have happened since the last time you checked). I'll assume that in the case of (2) you want a separate event logged for each growth event rather than one event per polling period.

DECLARE @tracepath nvarchar(500), @msg nvarchar(4000), @cr char(2) = CHAR(13)+CHAR(10);

SELECT @tracepath = substring(path, 0, charindex('\log_', path,0)+4) + '.trc'
FROM sys.traces WHERE is_default = 1;

DECLARE @db sysname, @fn nvarchar(max), @type varchar(10), @etype varchar(4),
  @eg varchar(20), @et char(17), @ed varchar(20), @cag varchar(32), @cfs varchar(20);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR 
SELECT 
  g.DatabaseName, 
  mf.physical_name, 
  FileType = CASE mf.[type] WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 
    'FILESTREAM' WHEN 4 THEN 'Full-text' END, 
  [type] = CASE g.EventClass WHEN 92 THEN 'Data' ELSE 'Log' END,
  EventGrowthMB  = CONVERT(varchar(20),convert(decimal(19,2),g.IntegerData*8/1024.)), 
  EventTime = CONVERT(char(8), g.StartTime, 112) + N' ' 
    + CONVERT(char(8), g.StartTime, 108), 
  EventDurSec = CONVERT(varchar(20),convert(decimal(19,2),g.Duration/1000./1000.)),
  CurrentAutoGrowthSet= CASE WHEN mf.is_percent_growth = 1
     THEN CONVERT(char(2), mf.growth) + '%'
     ELSE CONVERT(varchar(30),convert(decimal(19,2),mf.growth*8./1024.)) END,
  CurrentFileSizeMB = convert(varchar(20),convert(decimal(19,2),mf.size* 8./1024.))
FROM fn_trace_gettable(@tracepath, DEFAULT) AS g
INNER JOIN sys.master_files AS mf
ON mf.database_id = g.DatabaseID AND mf.name = g.FileName
WHERE g.StartTime >= DATEADD(MINUTE, -360, GETDATE()) AND g.EventClass in (92,93)
ORDER BY g.StartTime; -- want to log the newest one *last*, right?

OPEN c; 
FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @msg = @cr + N'Autogrow occurred (' + @et + N'): for '
      + @db + N' (' + @etype + N')' + @cr + @fn + N' ('
      + @type + N')' + @cr + N'Duration (sec): ' + @ed 
      + @cr + N'Growth (MB):' + @eg + @cr + N'CurrentGrowthSetting (MB): '
      + @cag + @cr + N'CurrentSize (MB): ' + @cfs;

  EXEC sys.xp_logevent 60001, @msg, error;
  FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
END
CLOSE c; DEALLOCATE c;

Result on my system: