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?

Answer 1

Yes, reading logs takes a long time in the log file viewer. A few things to fix it:

Try using xp_readerrorlog with filtered parameters to get just the data you want:

• @p1 is the log file you want to look at (0 is current, 1 is the first archive, 2 is second, etc)
• @p2 is null for the error log, 2 for Agent
• @p3 and @p4 are strings to search for in the output

That way you just get the rows you want.

If you find yourself doing this kind of thing frequently, run a job to cycle the error log periodically (I like weekly) so that you don't have to sift through so much stuff. Plus, make sure you're not logging successful backups or successful logins to the error log.

Answer 2

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

Answer 3

You should definitely roll your SQL Error log and SQL Agent Error logs as @AMtwo suggested in comment above.

Since the question was regarding reading maintenance plan log I will explain how you can keep the size manageable and open quickly.

For maintenance plan logging you want to create a new file each time Maintenance plan is executed.

Click that icon and you get following window. 'Create a new file' should be default and confirm that. Do not check 'Append to file' because that will create a single file for all execution.

You can have another maintenance task to clean up log files older than xx days to avoid disk filling up.

Answer 4

Maintenance Plans generate their own log files if you choose that option. When looking at MP history, it is reading from [msdb].[dbo].[sysmaintplan_log] and [msdb].[dbo].[sysmaintplan_logdetail], not those text files. Purge this with a History Cleanup Task, as this will clean up the tables and the files.

SQL Server ERRORLog and Agentlog should be maintained properly, but MP is a separate conversation/effort

In grand MS tradition...no indexes on those tables, other than clustered on [msdb].[dbo].[sysmaintplan_log]