如何在Server中旋转跟踪日志

Question

我们在Server 2012中使用4032 & 3605跟踪标志来获得预期的日志输出。

现在，我们希望每天旋转日志文件，将其存档在亚马逊冰川存储设施中。

任何关于日志轮转的指针都将不胜感激，

Answer 1

要旋转日志，只需执行

EXEC master.sys.sp_cycle_errorlog;

您可以每天安排执行，或者尽可能频繁地安排SQL Server代理作业的场景中的执行。

Answer 2

您永远不知道什么时候有另一个进程运行了master.sys.sp_cycle_errorlog，所以我认为有一个归档过程来读取所有的日志并应用必要的筛选来捕获您想要的东西是明智的。下面的示例确实使用无文档的存储过程读取日志。这只是一个例子--根据需要进行调整

--The following code uses 'undocumented' stored procedures - use at your own risk

--Initially create a table to track the last time you ran this archive process
--The following two lines are a one time thing
create table dbo.LastErrorLogArchiveProcessed (LastErrorLogArchiveProcessedDateTime DateTime)
insert into dbo.LastErrorLogArchiveProcessed (LastErrorLogArchiveProcessedDateTime) VALUES('2016-09-04 00:01:00.570')

--The following code could be scheduled in a job that runs daily at midnight
DECLARE @errorlog TABLE (LogDate DATETIME,[source] VARCHAR(max),[message] VARCHAR(max))
DECLARE @BeginDateTime DATETIME = (SELECT LastErrorLogArchiveProcessedDateTime FROM dbo.LastErrorLogArchiveProcessed)
DECLARE @EndDateTime DATETIME
DECLARE @LogArchive INT = 1
DECLARE @NumErrorLogs INT

--Cycle the errorlog
EXEC sp_cycle_errorlog

--Capture the current timestamp
SET @EndDateTime = sysdatetime()

--Get the number of configured ErrorLogs
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'NumErrorLogs'
    ,@NumErrorLogs OUTPUT

--Loop through all ErrorLogs
WHILE @LogArchive <= @NumErrorLogs
BEGIN

    INSERT INTO @errorlog
    EXEC xp_readerrorlog @LogArchive,1,NULL,NULL,@BeginDateTime,@EndDateTime

    SET @LogArchive += 1
END

--Update dbo.LastErrorLogArchiveProcessed
--reflecting the DateTime just processed
UPDATE dbo.LastErrorLogArchiveProcessed SET LastErrorLogArchiveProcessedDateTime = @EndDateTime

--Execute DELETES from @errorlog to apply filtering
--NOTE: The 3rd and 4th parameters of xp_readerrorlog allow some filtering
--It's whatever you want to do
SELECT * FROM @errorlog

--@errorlog now contains the data you want to save and archive

SELECT * FROM dbo.LastErrorLogArchiveProcessed