Why does it take so long to view logs on a server?

Question

I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no active filters besides that in this example. Then I play the waiting game…..

I had it take almost 20 minutes to get the logs to show. So I thought that it might just taking the time to load all logs from the beginning of this servers time. I reduced the filter to about 3 days since that is all I needed anyway. That came up faster but still took 4 minutes to show. Note this is the first time on my computer that I have been trying to view these logs so that might also play a factor. I also tried to look at the logs directly on the server but was getting similar time results.

Is this par for the course? Should I expect viewing the logs to be an experience like this? Is there something that I should be doing or checking? I do plan to check the log age and see if it can be purged but would that still affect looking at logs for only a short X Day period?

asked 2017-04-20 by Matt

---

Answer

By default, SQL Server will only roll over the error log when the instance restarts. If you have excellent server uptime (perhaps you only patch & reboot a few times per year), the error log could become pretty huge (I’ve seen it reach many GB in certain situations).

The error log is stored as a text file, so opening it in the log viewer essentially requires SQL Server to open & parse the entire text file–if you’ve ever opened a 10GB text file, you know this can take be slow.

I usually have a job that will roll over the SQL Server error log once a week, so that the log stays small enough that it’s quick to open–and also goes back far enough a single file has what I need.

To roll over your log, simply run this stored procedure (and schedule an Agent Job to run it weekly:

EXEC sp_cycle_errorlog;

You will still see the old archived logs in the Object Explorer:

And you can view multiple logs in the Log Viewer:

You may also wish to limit the number of archive log files you keep on your server. To do this, Right-Click on the “SQL Server Logs” folder in the Object Explorer and select “Configure”. Then mark the checkbox, and set a maximum number of log files.

You should generally err on the side of setting this higher than you think you need. If you ever have a problem where the server suddenly reboots multiple times or the SQL Server service is recycled repeatedly, the error log will turn over each restart, and your logs won’t go back as many weeks as you want.

You can also set the log retention via xp_instance_regwrite. In the example below, it is setting this value to retain 10 error logs:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer', 
    N'NumErrorLogs', 
    REG_DWORD, 
    10
GO

answered 2017-04-20 by Andy Mallon