Send results of autogrowth from default trace to application event log

Question

I've been asked to generate an entry in the event viewer, as opposed to an e-mail notification, whenever there is an autogrowth event. The problem I'm having is getting data from the default trace parameters into the message part of the event viewer.

To generate the event viewer entry, I'm using:

EXEC xp_logevent 60001, @msg, error

If I try to set @msg to say EventName I get an error message:

Invalid colmun name 'EventName'

Right now an entry is created in the event viewer, but it doesn't state where the autogrowth happened, how much, etc. Without that data, someone who gets the alerts via azure log analytics will have to log in to the server to check it out every time. Here is my current query:

DECLARE @tracepath nvarchar(500)
SELECT @tracepath = path FROM sys.traces WHERE is_default = 1

--The trace automatically finds _n files, trim off the _nnn portion of the file name.
select @tracepath = substring(@tracepath, 0, charindex('\log_', @tracepath,0)+4) + '.trc'

print @tracepath

SELECT
DBName    = g.DatabaseName
, DBFileName   = 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
, EventName   = te.name
, EventGrowthMB  = convert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.
, EventTime   = g.StartTime
, EventDurationSec = convert(decimal(19,2),g.Duration/1000./1000.) -- Length of time (in milliseconds) necessary to extend the file.
, 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.)) + 'MB'
    END
, CurrentFileSizeMB = convert(decimal(19,2),mf.size* 8./1024.)
, @tracepath 
FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te 
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id AND DATEDIFF(mi, g.StartTime, getdate()) < 360
and  te.name in ('Data File Auto Grow','Log File Auto Grow')
order by StartTime desc
IF @@ROWCOUNT > 0
BEGIN
PRINT ERROR_Message()
DECLARE @msg VARCHAR(100)
SELECT @msg = ERROR_MESSAGE()
EXEC xp_logevent 60001, autogrowthEvent, error
END

Answer 1

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: