將自動增長的結果從預設跟踪發送到應用程序事件日誌

每當有自動增長事件時，我被要求在事件查看器中生成一個條目，而不是電子郵件通知。我遇到的問題是將預設跟踪參數中的數據獲取到事件查看器的消息部分。

要生成事件查看器條目，我正在使用：

EXEC xp_logevent 60001, @msg, error

如果我嘗試設置@msg說EventName我收到一條錯誤消息：

無效的列名 ‘EventName’

現在在事件查看器中創建了一個條目，但它沒有說明自動增長發生的位置、數量等。沒有這些數據，通過 azure 日誌分析獲取警報的人將不得不登錄到伺服器進行檢查每次都出來。這是我目前的查詢：

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

好吧，如果您要從預設跟踪中提取此資訊，您需要 (1) 將數據庫名稱、增長大小等資訊組合成一個字元串以傳遞給 xp_logevent，以及 (2) 處理多行（因為自上次檢查以來可能發生了多個事件）。我假設在 (2) 的情況下，您希望為每個增長事件記錄一個單獨的事件，而不是每個輪詢週期一個事件。

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;

我的系統上的結果：

引用自：https://dba.stackexchange.com/questions/164197