Managing your SQL Server logs

“I am getting an error. Something’s broke. My application works fine. It’s the database.”

We’ve heard that guy more than once. So what do you do? I usually open up my box of SQLs and check out the logs.

This is usually a pretty simple process. Open the management folder, open the SQL Server Logs folder, double click your log. It’s a nice place to start.

Now what happens when you have 500,000 records in there? It takes FOREVER!

Luckily SQL 2011 (Denali) has a feature in there to stop the whole log from loading. If you aren’t on SQL 2011 (Denali) then you have to sit there and wait…and wait…and wait…

I recently found this beauty – EXEC MASTER.sys.Sp_cycle_errorlog

This boots your current log to second place and creates a new one. Now you have a nice clean fresh log to fill up.

So what happens to the logs? Well the SQL default is to keeps 6. Almost every time you open up that folder, you see Current + 6 old ones. After 6, they are recycled.

You are able to configure the amount of logs you keep too, between 6 and 99. Just right click on the ‘SQL Server Logs’ –> Configure.

SQL creates a new log every time the server is restarted. For most people, this is way too far apart. On my servers, I set up a weekly job that run this command. That way I know I have 1 week worth of data in each of my logs.