0

I run this every month to extract database logs, and I have to change the dates as well.

DECLARE @start DATETIME
SET @start = CONVERT(DATETIME, '2020-08-01 00:00');

DECLARE @end DATETIME
SET @end   = CONVERT(DATETIME, '2020-08-31 23:59');

DECLARE @searchString1 NVARCHAR(256) = 'BACKUP';
DECLARE @searchString2 NVARCHAR(256) = '';

EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 2, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 3, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 4, 1, @searchString1, @searchString2, @start, @end;
EXEC xp_readerrorlog 5, 1, @searchString1, @searchString2, @start, @end;

However, I get multiple results that I have to manually sift through and combine

How can I do something like this:

SELECT * FROM (EXEC xp_readerrorlog 0, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
SELECT * FROM (EXEC xp_readerrorlog 1, 1, @searchString1, @searchString2, @start, @end)
UNION ALL
etc

Obviously, this does not work. I get

Incorrect syntax near the keyword 'EXEC'.

Is there a better way?

2 Answers 2

2

I don't think you can combine several logs in one EXEC, so you are most likely stuck with several executions. As suggested nu Mo64, you can put it all in a temp table and then query that temp table. I have a utility that does exactly that, here.

1

For the dates use dateadd to go back a specific number of days from todays date (getdate()) You can use xp_enumerrorlogs to get the metadata about how many log files there are and then write a loop to iterate through them storing the results in a temp table.

as explained here :-

https://sqlmatters.com/Articles/Searching-ALL-SQL-Server-Logs-using-TSQL.aspx

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.