We are using 4032 & 3605 trace flags in SQL Server 2012 to get the expected log output.
Now, we would like to rotate the log files on daily basis to archive it in Amazon Glacier storage facility.
Any pointers on log rotation would be appreciated,
To rotate the logs you just need to execute
You can schedule the execution daily or as often as it makes sense in your scenario with a SQL Server agent job.
You never know when another process has run the master.sys.sp_cycle_errorlog, so I think it’s prudent to have an archive process that reads through all of the logs and apply the necessary filtering to capture what you want. The following example DOES use undocumented stored procedures to read the logs. It’s only an example – tweak as needed
--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'SoftwareMicrosoftMSSQLServerMSSQLServer' ,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