Retention for SQL Error Log

Question

Retention for SQL Error Log

WhiteLotus

SSChampion

Points: 12122
More actions
September 2, 2019 at 6:29 am

#3676262

Hi All ,
I would need to create a retention policy for SQL error Log . I only want to keep the error log less than 3 MONTHS old.
How do I achieve this ?
Thank you

Viewing 15 posts - 1 through 14 (of 14 total)

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

Planned restarts twice a month? Well, sp_cycle_errorlog twice should be enough.

There is no setting for this. You can set how many errorlog files to retain, I believe, but there is no setting based on time.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

BrainDonor SSCoach Points: 19254 More actions · Answer 2

Erland is correct, there is no time-based setting for retaining the error logs.

However, I'm looking at exactly the same issue right now (but I need to keep mine for a minimum of 12 months) and there is a PowerShell command in the 'SQLServer' PowerShell module, which can extract error log details based upon a variety of parameters .

Get-SQLErrorLog may be of use to locate the files greater than 3 months old and then remove them. I haven't got that far yet but the idea seems sound.

Steve Hall
Linkedin
Blog Site

tripleAxe SSCertifiable Points: 5706 More actions · Answer 3

The maximum number of logs you can configure to keep is 99. It is set to 6 by default but you can increase it in the Configure SQL Server Errors Logs dialog in SQL Server Management Studio. Why not set it to 90 and then create a job to run sp_cycle_errorlog every day?

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

A bit tricky really, unless you want to take the 90 separate log files approach.

For example, if you set a job to cycle the log every 15 days, then the 6 logs would contain 90 days maximum, but could contain as few as 75 when the log first switches. If you used 18 days, then the 5 oldest logs would have 90 days, but the current log would get you up to max of 118 days.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

I'd set to 90 and cycle daily. Easiest and simplest way to find a file for a date.

BrainDonor SSCoach Points: 19254 More actions · Answer 6

Oh go on Steve - pick an easy solution!

Doesn't help for my 12 month task (the joys of a PCI audit), but I do wish I'd thought of that.

Steve Hall
Linkedin
Blog Site

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

LOL, Steve, for you I'd just keep logs and only delete those that had an end stamp > 12months old 😉

Andrey SSC Eights! Points: 840 More actions · Answer 8

BrainDonor wrote:

Oh go on Steve - pick an easy solution!
Doesn't help for my 12 month task (the joys of a PCI audit), but I do wish I'd thought of that.

Another possible approach is to use xp_readerrorlog regularly and save output in a table with needed history depth, if its complies with PCI audit restrictions.

WhiteLotus SSChampion Points: 12122 More actions · Answer 9

Thanks for the response .

How about if I want to cycle the log every week ?

Does it mean I need to set up the maximum of error log = 12 ?

as I want to keep 3 months worth of log

Thank you

Andrey SSC Eights! Points: 840 More actions · Answer 10

WhiteLotus wrote:

How about if I want to cycle the log every week ?
Does it mean I need to set up the maximum of error log = 12 ?
as I want to keep 3 months worth of log

Errorlog file can/will be recycled in two cases:

restart of SQL instance
execution of sp_cycle_errorlog procedure

Setting "maximum of error log = 12" will tell SQL server how many files to keep on disk.

If you restart your SQL server 2-3 times (patching, ad-hoc restart, other reasons) , appropriate amount of old errorlog files will be deleted by SQL server.

So, if you want to cycle errorlogs on weekly basis, you have to either schedule cycle job with schedule "once a week" or restart your instance on a weekly basis.

As it was recommended already, the common approach is :

create cycle job (SQL Agent) with schedule "once a night"
set max amount of errorlog files to 90+x (x = expected amount of restarts of the instance during 3 months)

Remember, if you set max files to 90, each planned/unplanned restart of SQL server will steal one of your errorlog files from disk.

WhiteLotus SSChampion Points: 12122 More actions · Answer 11

Thanks for the response .

please kindly answer to my question :

How about if I want to cycle the log every week ?

Does it mean I need to set up the maximum of error log = 12 ? Or probably 15

as I want to keep 3 months worth of log

Thank you

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

How often do your instances reboot/restart? For patching or anything? Take some guess here or use historical data. 12 is good for 3 months with no restarts. If you restart once a month, then 15, though I'd assume something breaks and likely pad out to 20-25.

Jeff Moden SSC Guru Points: 1004355 More actions · Answer 13

One of the attacks that a hacker will sometimes make on you is accompanied by brute forcing rollovers of the SQL Server Error log so you can't figure out what happened. A lot of times they'll figure that you kept the default of 6 and so force 6 rollovers. My take on it all is to set the error logs so that you keep 99 rollovers. It doesn't take that much space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WhiteLotus SSChampion Points: 12122 More actions · Answer 14

WhiteLotus

SSChampion

Points: 12122

September 18, 2019 at 4:19 am

#3680933

Thanks guys . Much appreciated