如何维护SQL Defalt Trace历史信息

Default Trace用途

默认跟踪可确保数据库管理员在问题首次出现时即具有诊断该问题所需的日志数据，从而为数据库管理员提供了故障排除帮助。

由于Default Trace保存了大量有用的信息，可以快速帮助我们做Audit或者Troubleshooting,所以保留历史信息也是很有用的。

解决方法：

1.首先创建数据表用来存储Trace信息，然后将现有的Defatult trace信息保存入新建的表中：

DECLARE @path NVARCHAR(260);SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),  CHARINDEX('\', REVERSE([path])), 260)) + N'log.trc'  FROM sys.traces WHERE is_default = 1;SELECT   TextData = CONVERT(NVARCHAR(MAX), TextData), DatabaseID, HostName, ApplicationName, LoginName, SPID, StartTime, EndTime, Duration, ObjectID, ObjectType, IndexID, EventClass, [FileName], RowCounts, IsSystem, SqlHandle = CONVERT(VARBINARY(MAX), SqlHandle)INTO dbo.DefaultTrace_HistoryFROM sys.fn_trace_gettable(@path, DEFAULT);CREATE CLUSTERED INDEX IX_StartTime ON dbo.DefaultTrace_History(StartTime);

2.定期的获得Trace信息并且保存到上一步新建的表中：

DECLARE @maxDT DATETIME;SELECT @maxDT = MAX(StartTime)  FROM dbo.DefaultTrace_History;INSERT dbo.DefaultTrace_HistorySELECT   TextData = CONVERT(NVARCHAR(MAX), TextData), DatabaseID, HostName, ApplicationName, LoginName, SPID, StartTime, EndTime, Duration, ObjectID, ObjectType, IndexID, EventClass, [FileName], RowCounts, IsSystem, SqlHandle = CONVERT(VARBINARY(MAX), SqlHandle)FROM sys.fn_trace_gettable(@path, DEFAULT)WHERE StartTime > @maxDT;

3.定期清理旧数据，比如你只保留60天历史数据：

DELETE dbo.DefaultTrace_History   WHERE StartTime < DATEADD(DAY, -60, CURRENT_TIMESTAMP);

完成之后你就可以查询历史记录了。