-1

There are various methods you can use to control SQL error logs files.

Related SQL Server : log folder too large

While there is never a one size fits all solution in for anything in SQL, After googling around most of the solutions I am finding are listing 5MB or 10MB in their solutions. Example How to manage the SQL Server error log @ microsoft.com You can have 5 error logs at 1GB each or 99 error logs at 5MB each, and have about the same history.

I don't find anyone making actual "recommendations" on SQL error log size.

I imagine the place to start is at a percentage of your RAM, but I really don't know.

SQL Error Log, how do I determine the most appropriate error log size?

James Jenkins
  • 6,228
  • 6
  • 45
  • 87
  • @close_voter, On readying my last sentence I realized the question was opinion based, I have re-written and think it is ok now, if I missing something please point it out. – James Jenkins Nov 08 '18 at 16:56

1 Answers1

3

SQL Error Log, how do I determine the most appropriate error log size?

Whatever is above the "normal" for each individual environment. Some environments require the logging of successful and unsuccessful logins to the errorlog, obviously that's going to bloat the log if it's a highly chatty server. Other environments are required to have backup information stamped in the log, again with frequent backups this might bloat the log more.

I'd take the approach of:

  • Roll the log over each day, this will let you easily search if you need historicals that are phased out.
  • Find the average size of the log, then investigate it to see what can be paired down
  • Pair down as much as you can while still keeping it useful (i.e. if you don't need logins stamped, don't do it)
  • Watch the log sizes over a few weeks and track the average (can be done with a job). Find your average, then maybe report if you're 1 standard deviation away or such, so that you can investigate.

I imagine the place to start is at a percentage of your RAM, but I really don't know.

RAM doesn't come into play here, this is on disk only and stores information that may or may not be helpful to you. I've found the log to be a treasure trove of information and extremely useful for figuring out "what happened" after the fact.

Sean Gallardy
  • 32,288
  • 3
  • 40
  • 77