If you have a massive, giant, huge, big (throwing the words in there, for folks on Google to find this post 🙂 SQL ERRORLOG file, you will want to do TWO things (don’t just perform step #2, it will likely be back!)

SQL ERRORLOG growth, when it starts, will grow quickly. I’ve seen it fill 40GB on a systems C: in less than a day, due to challenges with a Database finding its associated logfile. Because the file is so large, one would think that analyzing it would be difficult (but it isn’t so bad, and loads easily – see ‘Analyze the ERRORLOG file’). But the key is, find out what is wrong, deal with it and then clean up the big file. Unless the location has been overridden from the defaults, you are likely to find the ERRORLOG file in: C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG

Analyze the ERRORLOG file

To open the latest ERRORLOG file, fire up SQL Query Analyzer and perform the following:

sp_readerrorlog

And if the current one is small and has nothing of interest, append the ‘next number’ to the end of the instruction, to instruct SQL to open the previous version (ERRORLOG.1, etc.), such as the example below which would be looking at the previous (1^st version since being reset) ERRORLOG file.

sp_readerrorlog 1

In our most recent discovery of a massive ERRORLOG file, it became pretty clear what was being logged, over and over and over again (to 40GB!)

Then … Cycle the ERRORLOG file

After you have discovered the problem and are working towards resolution, you will want to get rid of the ERRORLOG file and here’s two ways:

1. Stop the SQL Service – Every time SQL Server is started, the current error log is renamed to errorlog.1; errorlog.1 becomes errorlog.2, errorlog.2 becomes errorlog.3, and so on.
2. Perform a sp_cycle_errorlog – this enables you to cycle the error log files without stopping and starting the server. If you have difficulty resolving the problem causing this inflated ERRORLOG file, considering scheduling this until such time that you can