Quick Scan – Large Error Log Files
The default setting for SQL Server is that an extensive amount of information is written to the log file and the only time the logs get cycled or cleared is when your restart your SQL Server instance.
One common thing that fills up error logs is backup messages showing every database that was backed up. There is a separate page that describes dealing with that.
What can happen is that things go bad and you need to use the error log. You then go into the log file viewer with SSMS, or you manually attempt to query it, and it just times out while it is loading hundreds of thousands of log entries. I have seen some servers where it took more than an hour for the log viewer to load.
There are a couple of thing you can do to help with this to keep the logs from getting too large.
- Trace flag 3226 if you have your logs flooded with backup messages.
- You can add a job to cycle your error log frequently. I like to do this weekly unless weekly is not enough.
I usually will do both of those options in order to keep the error log in a usable state, and then I will also extend the number of error logs from the default of 6 and set it to a higher number like 30 which gives you more smaller logs that are easier to work with.