Just to be very clear, I’m talking about the SQL Server Log, not database logs. Sometimes this is called the Error Log (and in fact the text files containing the data are named ERRORLOG, ERRORLOG.1, etc) although it contains a lot more information than just errors. And just to make things even more nice and confusing the GUI calls it the “SQL Server Logs”.

I did a homework assignment a few months back to read the log. I’m going to assume that you either have done your homework or already know what was involved.

Now if you’ve reviewed the logs (typically because you have a problem, or maybe you actually check them periodically for possible issues) you’ve probably had occasions where it took forever for the information to load into the GUI. There are a couple of reasons for this. The log information is broken out into multiple text files. A new file is created each time the SQL Instance is cycled (re-started). Now imagine how large that file can get when your server has been up for a month? Or 6 months? It can take quite a while to load all of that information into a GUI.

So what’s the solution? Restart SQL Server on a regular basis? Uh, no. That’s a fairly significant step for something like this. The common practice that I’ve seen (I hesitate to call it “best”) is to recycle your log on a daily basis. Just create a job that runs sp_cycle_errorlog on a daily basis.

Now your the log files will contain a manageable amount of information. An added benefit is that if you had a problem on Thursday you can open the log for just that day. There is, however, a fairly important thing to remember here. By default SQL Server only keeps 6 log files! That means if you are cycling your log every day you have less than a week’s worth of information available. You can fix this by telling SQL Server to keep more logs.

You’ll note I have it set to keep 32 files. This way I have at least a months worth of information. It’s probably more than I need, but for my personal box, that’s what I chose. For your company, it’s going to depend on your needs. You can keep up to 99 files but that seems a bit excessive to me. Of course, these files aren’t all that large (usually) so it’s up to you.