Retention for SQL Error Log

  • WhiteLotus

    SSChampion

    Points: 11932

    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

  • Erland Sommarskog

    SSC-Insane

    Points: 23894

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

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

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

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720371

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

  • BrainDonor

    SSCoach

    Points: 19230

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

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

     

  • Andrey

    Right there with Babe

    Points: 723

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

    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

    Right there with Babe

    Points: 723

    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:

    1. restart of SQL instance
    2. 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 :

    1. create cycle job (SQL Agent) with schedule "once a night"
    2. 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: 11932

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

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

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • WhiteLotus

    SSChampion

    Points: 11932

    Thanks guys . Much appreciated

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

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