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.