SQL Server Error Log retention

Question

SQL Server Error Log retention

chewychewy

Hall of Fame

Points: 3126
More actions
July 14, 2013 at 9:14 pm

#275986

Hi Guys,
Understand that by default SQL Server has 7 error logs.
The oldest error log records will be deleted each time after SQL Server restarts.
I'm looking for a solution that will retain the error logs for one year. (due to some security policy)
Increasing the number of error logs doesn't help much as I won't be able to determine how many times SQL Server will be restarted a year.
Anyone have solution to it on how can I have a rule to only remove error logs older than one year?
thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew G SSChampion Points: 12809 More actions · Answer 1

Just untick the max number of error log files so they aren't recycled. Then create a SQL agent task that deletes log files older than 1 year using powershell or cmd.

Unticking the option generates this code:

USE [master]

GO

EXEC xp_instance_regdeletevalue N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'

GO

chewychewy Hall of Fame Points: 3126 More actions · Answer 2

Hi Andrew,

thanks for ur help.

I'll take a look.

Understand that it can be set by:

under management -> sql server logs - right click - configure

How about for SQL Server Agent error logs?

I don't find any setting with regards to it.

It has a total of 10 logs. (while sql server error log has 7)

thanks

Andrew G SSChampion Points: 12809 More actions · Answer 3

Just looked that one up, unfortunately looks like it cannot be changed. You'd need to setup a SQL agent job to copy out the log files to a seperate location and delete older than 1 year.

chewychewy Hall of Fame Points: 3126 More actions · Answer 4

thanks!

By chance, do u have any script for: SQL agent task that deletes log files older than 1 year using powershell

Andrew G SSChampion Points: 12809 More actions · Answer 5

There's plenty of examples on the net for that, eg

http://www.howtogeek.com/131881/how-to-delete-files-older-than-x-days-on-windows/