Recycling SQL Error Log and increasing max number of Error logs Stored

Recycling Error log is another important thing in case if you are storing login auditing with login success to Error log. With Recycling error log to avoid losing important data one should increase the number of Error logs stored.

For Example: If Error log is growing at a rapid level then it can be recycled every two days and its setting can be changed to keep 50 error log files or higher which would account around 100 days.
1. Set the number of log files to 50 from the default of 6 to do this:
• SQL 2005 – Right click on SQL Server logs folder in Managment studio and choose Configure from the pop up menu.
• Click the Check box Limit the number of errorlogs before they are recycled and we set it to 50 to allow us to keep potentially useful data if we have to do multiple re-starts.

2. To Create a SQL Job that executes
EXEC sp_cycle_errorlog
to run every two days.(Monday, Wednesday, Friday, Sunday)
3. Optional:-

--to create a folder with todays date as 20-02-2012
exec xp_cmdshell 'md E:Rest%date:/=-%'
--to copy all Error log files to the created folder
exec xp_cmdshell 'copy "C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLog"Errorlog*.* E:Rest%date:/=-%'

4. Optional:- To change the defualt location for Error Log in Startup parameters. This will need a restart of the SQL Services after change.
Note: Be extra careful to check the folder struction exists on New location for error logs.
Monthly to archive all error logs to some other location which is named after the date of archiving.