How to rotate trace logs in SQL Server

Posted on

Question :

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,

Answer :

To rotate the logs you just need to execute

EXEC master.sys.sp_cycle_errorlog;

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 @LogArchive INT = 1

--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'
    ,@NumErrorLogs OUTPUT

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

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

    SET @LogArchive += 1

--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

Leave a Reply

Your email address will not be published. Required fields are marked *