Thursday, April 28, 2011

SQL Server: How to Manage Error Log File on Production Server

In one of my early post “How to Avoid Big Single Error Log File on Production Servers”, I have tried to explain that how error log file growth can be controlled to avoid very large files on production servers.
Though the post was very short but response was really great. Out of  received comments, today I would like to share one of SQL Expert Martin C. suggestions on this topic.
If you are monitoring your log on a daily basis for specific errors then the need to backup and keep old logs simply becomes dependent upon any compliance regulations. If you are not constrained by any compliance (e.g. SOX etc) then once you have examined the logs for any useful messages they become less useful for anything else.

You could make use of tools to parse the log entries and store anything significant in a database table so you retain these for later analysis, but messages that regularly appear (such as a backup completed) are really of no further use once you have confirmed the backup was successful especially as you have the information also stored in MSDB.

I'd suggest you ensure you have sufficient monitoring and checking of the error logs and perhaps even manually issue sp_cycle_errorlog after you have checked them.

No comments:

Post a Comment

All suggestions are welcome