2

I'm slowly collecting and trying to analyze lot of information on all of our instances (2008R2 SP1). I'm now going through the Error Logs and I've couple of question. There is a lot about error log size and found different recommendations on using sp_cycle_errorlog to recycle the logs on say, weekly basis, to avoid having very big error logs and be able to handle them adequately. So...

  1. When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows. edit: one has 2 034 546 rows
  2. I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?
  3. I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?
9

When an error log is considered to be "big"? Some of our logs take long to load, close to a million of rows.

This is going to be different for certain scenarios, but with your description ("Some of our logs take long to load"), that's my definition of big. When file operations (copying, moving, etc.) start to take a little longer than instant, or parsing an individual error log becomes an actual time consumer, then that's when I'd start to work on cycling my error log regularly to keep that size down. I know I'm not giving you a "X MB is too large of an error log file" statement, but hopefully this gives you insight. Outside of extremely abnormal and corner cases, the error log size shouldn't be a pain point in troubleshooting.

I'm trying to find some tsql to get error log size, but can't find it, it is possible to do it?

As far as T-SQL is concerned, outside of a call to xp_cmdshell, I can't think of a way to get this. And because I wouldn't open up that security vulnerability for this type of operation, my recommendation would be to use PowerShell, consuming SMO:

$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server("YOUR_SQL_SERVER_NAME")

$SqlServer.EnumErrorLogs() |
    Select-Object Name, @{Name = "SizeMB"; Expression = {[System.Math]::Round($_.Size/1024/1024,2)}}

I see the recommendation to recycle the logs and to configure more than just the default number of 6. Some authors recommend 50, others 10. I guess it depends on the environment, but is there a recommended number?

There's nothing worse than not having historical data due to rollover, no matter what the source of troubleshooting is (SQL Server error logs, XEvents logs, etc. etc. etc.). You need to really see what your cycling schedule is, and then determine how many days you want to retain. That may be different from server to server. It's hard to give a default retention value here, as there are very different factors such as available hard disk space, average error log generation size (normal conditions) during a 24-hour period, how far you care to go back with logs, and a few others. If you have the disk space with a comfortable buffer (always needs to be a buffer when planning out storage consumption), I personally like to err on the side of too many archived error logs. A conservative bet would be a retention duration of 4 weeks, but again there's too many factors to just making a hard and fast rule based off of. That's really a determination that you need to make, though. As a DBA and a troubleshooter, how far back do you want your error logs to go?

Side Note: I see in your original question that you referenced sp_cycle_agent_errorlog. It seemed to me like your question may have been more geared towards the instance's error log cycling, in which case it would be the sp_cycle_errorlog stored procedure.

3
  • Perfect answer, just what I was looking for. As usual, everything depends on each environment and configured parameters. So I was not specting a "X Mb is too large". And indeed I was talking about error log, will edit my question to replace sp_cycle_agent_log by sp_cycle_errorlog. – Yaroslav Jan 23 '14 at 13:40
  • 2
    Cool, I'm glad my answer helped! I know it can get frustrating, but this is one gigantic "it depends". Hopefully my answer has shown why it depends, and what those factors are. – Thomas Stringer Jan 23 '14 at 13:45
  • Yes, your answer is helpful. By the way, the error log files are all around 100Mb. Just one is almost 500Mb. – Yaroslav Jan 23 '14 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.