2

I have some code to help automate checking the error logs daily on our SQL Server. This code calls xp_readerrorlog 0 to check the error log, and xp_readerrorlog 0,1 to check the SQL Server log.

Sometimes, however, there is more than one error log I need to scan.

I would like to be able to see, by T-SQL, the date of the log or, barring that, find the number of logs that exist.

I read that we can have up to 99 archive logs, but the server throws the following error message when trying to display an error log that doesn't exist.

"xp_readerrorlog() returned error 2, 'The system cannot find the file specified."

I know the date information and number exists since SSMS displays it( see the image below.)

enter image description here

  • Can you explain why it matters if a row you've read from the error log came from Archive #3 or Archive #5? (Since you can just grab the data from all the logs in one pass, and you can easily determine where in that data a log file rollover happened.) – Aaron Bertrand Dec 6 '17 at 18:42
  • It doesn't matter other in processing time. My problem is that I'm flying blind. I know log 0 exists, and probably 1 exists, but after that, I'm shooting in the dark. If I could know the number of archive logs, that would be enough. The link you sent only scans the first log (log 0) – Andrew Neely Dec 6 '17 at 21:10
  • @Aaron Bertrand >>>Since you can just grab the **data from all the logs in one pass**<<< That is wrong. The code from that topic (running xp_readerrorlog without parameters) will read ONLY LAST(current) error log file – sepupic Dec 7 '17 at 7:36
  • Senior moment I guess. But anyway, why use this undocumented/unsupported XP when you can just read the plain text log files that exist using Powershell or C#? – Aaron Bertrand Dec 8 '17 at 21:20
8

If you run EXEC sys.sp_enumerrorlogs; it will return one row for each log file, the oldest date in the log file, and the log file size.

Which means you can do something like this:

CREATE TABLE #error_log
(
    log_number INT,
    log_date DATE,
    log_size INT
);
INSERT #error_log ( log_number, log_date, log_size )
EXEC ( 'EXEC sys.sp_enumerrorlogs;' );

SELECT *
FROM   #error_log AS el;

Then set up a loop to grab the max log number, only grab logs after a certain date, or perhaps exclude logs based on size. Some small logs may be uninteresting, and some large logs may take too long to process.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.