I can make your logs clap…

The SQL error log has this nasty habit of getting big when I am not looking.  There are only two ways to keep is at a normal size. One is to stop and start your SQL instance (Reboot, Restart, Stop and Start) and the other is to run this handy little script:


EXEC sp_cycle_errorlog;

This will end the current log and start a new one.  Why does this matter?  The SQL Error log holds information about your backups, failed logins, SQL errors, edition information and other fun stuff.  The bigger it is, the longer it will take SQL to load it into memory so that you can read it.  Usually when you need to read it, you are in trouble so the slower it is, the more stressed you will be.

What is a good size?  I usually try to get it to roll over around 10 MB.  I use a monitoring tool and when the large error log alert is triggered, I have it run sp_cycle_errorlog for me so mine always stay a healthy size.  You don’t need fancy tools to do this though.  If you know about how fast your logs grow, you can set up a SQL Agent job to run it on a schedule to keep your logs healthy.

How many logs should I keep? This is completely up to you, but since I keep my logs so small, I try to keep 15 of them.  Why so many? I do it so I can go back and see issues further back if needed. You can adjust the amount you keep by right clicking on SQL Server Logs in SSMS and selecting “Configure”

Configure SQL Error Logs

Super cool, but what about the Agent error logs? There is a script for them as well!


USE msdb;
GO
EXEC sp_cycle_agent_errorlog;

See? Healthy and Happy Logs! Your Logs will be clapping with joy.

About andreaallred

SQL Server and helping people is my passion. If I can make someone laugh, I know I have made a difference.

3 thoughts on “I can make your logs clap…

  1. waldenl says:

    Nice idea. I rotate mine daily (it’s automatc, why not?) but then pipe the just disconnected log through grep which filters everything I expect to see and emails me what’s left. Typically an empty email other than the headers I keep to know which server I’m looking at. But every once in a while I bounces out something worth reviewing. Takes about 30 seconds in the morning to hit delete on the normal emails, time well spent.

  2. jxhyao says:

    To make the log file recycled once a specified threshold is reached, you can check my previous article (using PowerShell) https://www.mssqltips.com/sqlservertip/3229/cycle-sql-server-error-logs-based-on-size-using-powershell/

    HTH,
    Jeffrey

  3. Goher Naushahi says:

    DBCC ERRORLOG is another way of recycling error log.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s