Cycling Those Error Logs

Introduction

If you're servers are like mine, and hopefully they are, you get long periods of uptime on your SQL Servers. In fact, for many of my servers, the uptime is measured in months. And my error logs are fairly clean with very few exceptions or errors raised. However, the logs get a lot of entries in them, mainly due to frequent transaction log backups.

Over time I accumulate some rather large logs, like tens of MBs. If you even see one of these in Enterprise Manager, a word of warning. Don't click them. They take substantial amounts of time to open. Usually more time than you'll want to spend looking at a frozen Enterprise Manager. I've had it "hang" on me more times than I like, delaying the troubleshooting that I needed to do and forcing me to try another tactic for viewing the log information.

Now many things entered into the SQL error log, but not necessarily all, are written to the Windows Application Event log, but that's not always someplace I look. Often I'm already in Enterprise Manager and being able to check the log from the tool I'm in has been handy, saving some and frustration. Not to mention not having to eat up more system resources by starting up another application.

My Solution

So I started down the path of looking for a method of cycling my error logs periodically. A little digging discovered the DBCC ERRORLOG command (thanks to Brian Knight's book on SQL Server 2000 for Experienced DBAs). This is a nice little undocumented utility that will cycle your error logs.

It works very simply, in the same way that a restart of the SQL Server service redoes your error logs. It closes the current log, renames it as errorlog.1, moves errorlog.1 to errorlog.2, etc. Actually it happens in the reverse order, 5 becomes 6, 4 becomes 5, etc. as anyone who has performed this process in a program will recognize. Most of us actually write it the first way, test it, and realize we made a small error in judgement.

It then opens up a new errorlog and starts writing events there. And it does this with a simple command:

dbcc errorlog

which returns

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

That's it. That's all you do. This ends up resetting your log.

The Downsides

Now this isn't just simple and dandy. There are a few things that you should be aware of if you implement something like this. First of all, your errorlogs will not reflect the SQL Server startup sequence which shows you the version, registered DLLs, etc. Some of that is rewritten, but not all of it. However you will get this nice message to let you know that a manual cycling occurred:

Errorlog has been reinitialized.  See previous log for older entries.

There also is the potential that you will lose information that you did not want. After all, by default there are only 6 logs kept. I know you can change this, but most of us don't and it's one more thing to do. Since I prefer simple solutions, I tend to minimize my impact where I can so things will run automatically.

But automatically means I have this scheduled somehow. And I do. I've written a short procedure that checks to see if the current log is larger than some size. I guesstimated around 5MB, which takes a couple minutes in EM to open. The procedure is here:

create procedure dbspCheckErrorLog
@limit float = 5000000
as
create table #logs
( Lognum int
, Logdate datetime
, Size float
)
insert into #logs exec master..xp_enumerrorlogs
if ( select size from #logs where LogNum = 0) > @limit
 begin
dbcc errorlog
exec master.dbo.xp_sendmail @recipients=@to
, @subject = 'Error logs cycled'
 end
drop table #logs
return
go

I decided to schedule this once a weekday for a few reasons. One is that if some process goes crazy and I start getting tons of log entries, I don't want the logs to cycle 6 times and I lose potential source information when the issue started. By scheduling this once a day, the most I can lose is one log (errorlog.6) and hopefully this can assist me with troubleshooting as well as manage the log size better.

Conclusions

A pretty simple solution to an annoying problem. At least annoying to me. Hopefully this helps some of you out there and I'd like to hear about ant other solutions you have or enhancements to this one.

Steve Jones

Return to Steve Jones Home

Cycling Those Error Logs