Infrastructure at your Service

Stéphane Haby

Check SQL Server Error Log size with a policy

In many blogs or articles, we can read that it is recommended to recycle the error log to be sure that the size is not too big
In the Best Practice Analyzer (BPA) for SQL Server, this is a rule.
But what is a good size for the error log file?
I have often heard “it depends”, but in my opinion, this is not a good answer
The first step is to find out what is a good size for the error log file.

Step 1: Identify the error log size threshold

To find an acceptable limit, I first checked the server. The easiest way is to go to the windows explorer on the server, open the error log path and look at the size by file as showed on the following picture:

DBI_errorlog_09.png

You can also use the undocumented stored procedure xp_enumerrorlogs, which will be useful in my case because I will create a policy that will verify each error log file size against a configured threshold:

DBI_errorlog_10.png

xp_enumerrorlogs provides the following information:

  • Archive Number
  • Creation Date (recycling date)
  • Size in byte

On my server, I noticed I have 13 archive files with different sizes, but I have not a strategy to recycle the error log file. Thus, it is recycled during the SQL Server Engine service restart.

For a production server, this can be long to read and display the error log file. Furthermore the error log can become very very big.
At the end of this article, I will give you a dbi best practice for the error log.

In my sample, I will focus with 4 archives: 2 small size archive files with number 2 and 11 and 2 big size archive files with number 3 & 4.
I will run SQL Server Management Studio (SSMS) to display one archive after the other and in parallel, I will run the resource manager to see the memory used by SSMS.

Result for Archive Number 2

DBI_errorlog_02.png

Result for Archive Number 11

DBI_errorlog_06.png

Result for Archive Number 3

DBI_errorlog_05.png

Result for Archive Number 4

DBI_errorlog_01.png

The following table summarizes the results of each error log files reading:

DBI_errorlog_08.png

Time result

We can notice that between the archive number 11 with 45459 records and the archive number 3 with 761039 records, the time to read is approximately 30s.
The archive number 4 run more than 1 minutes… I can easily take a coffee!:-o

Memory result

The private memory column gives us an important information here: we can see that for the archive number 11, the memory has doubled in size.
Furthermore, for the Archive number 3, we have 4 times more and for the Archive number 4 we have 11 times more.
As the reminder, this metric is typically used to measure the memory impact of an application.

According to these information, I can deduce that a good error log file size is between 33MB and 164MB.
To be comfortable, I choose a policy threshold value of 50MB.

Step2: Creating the script

I create a table with the information provided from the xp_enumerrorlogs.
I divide the size per 1024 to have kB and again to have MB.

DBI_errorlog_04.png

In this picture, you can see that I have 2 archive sizes greater than the  50Mb threshold value.
So, to create my policy condition I have to count the number of files whose size is greater than this threshold value.

DBI_errorlog_03.png

And of course in my sample, I get 2. Perfect!:-)

Step3: Create the policy

I create the new policy “dbi services – SQL Server Error log too big”

DBI_errorlog_11.png

I create a new condition “SQL Server Error log size”

DBI_errorlog_12.png

If it is greater than 0, the condition is not meet and it implies that I have an error log file greater than 50Mb.

DBI_errorlog_13.png

And now I can evaluate the policy against my error log files on my server as following:

DBI_errorlog_14.png

DBI_errorlog_15.png

Click on evaluate and…

DBI_errorlog_16.png

On this test server, the condition is met because I have not a big error log file (> 50MB).
Then, I export the policy on the sample server that has 2 error log files with a size bigger than 50MB.
So, now if I evaluate my policy against this server…

DBI_errorlog_17.png

… as expected it fails because we have two error log files that does not meet the condition.
I can easily deploy this policy on all servers or by using Enterprise Policy Management (EPM) on codeplex and the Central Management Server feature (CMS).

Conclusion

I recommend to use this policy to check the error log file size.
My best practice here is to recycle the error log every day with a cycle of 30 files (30 days) per default in a maintenance job.
If this policy failed with this best practice, then you can be sure that you have a lot of errors or too much information (like trace flag activation).
I hope this can help you and click here dbi-services—SQL-Server-Error-log-too-big.zip to download the policy to try it.

2 Comments

  • Aijaz says:

    Hi,

    Thanks for the policy, I want to create a policy through which I can check the severity:17-19 erros from the sql server error log , can you please help me with the correct facet to choose and condition-filed, operator and value or if there is a condition which can show top 30 erros from errorlog file, it will be fine.

    • Stéphane Haby says:

      Hi,
      Thank you for your interesting question.
      To control the severity in a policy, you can use a code like this:
      DECLARE @NumberofError INT
      CREATE TABLE #read_error_log
      (
      logdate DATETIME,
      processinfo VARCHAR(200),
      errorlogtext VARCHAR(max)
      )

      INSERT INTO #read_error_log
      EXEC master.dbo.Xp_readerrorlog
      0,
      1,
      N”,
      N”,
      NULL,
      NULL

      SELECT @NumberofError=count(*)
      FROM #read_error_log where errorlogtext like ‘%Severity: 17%’ OR errorlogtext like ‘%Severity: 18%’ OR errorlogtext like ‘%Severity: 19%’

      TRUNCATE TABLE #read_error_log
      DROP TABLE #read_error_log

      Select @NumberofError

      if @NumberofError is more than 0, you have errors with the severity 17,18 or 19 in the error log.
      The best is to use the facet “server” in this case and the ExecuteSQL for the condition.

      Regards,
      Stéphane

Leave a Reply

Stéphane Haby
Stéphane Haby

Delivery Manager