gotchasqlMinor
Why does it take so long to view logs on a server?
Viewed 0 times
whylogstakelongviewdoesserver
Problem
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?
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?
Solution
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:
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.
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.
Context
StackExchange Database Administrators Q#171510, answer score: 9
Revisions (0)
No revisions yet.