I wanted to see when our restores were completing.
The job to restore did not log anything and job history didn't go back as far as I needed it to.
We have 9 months of "errorlogs" (a misnomer), so I thought to look there.
In the end, I pieced together this script to help me search all the logs (both Current and Archived).
The cool thing is, you may use it to do all sorts of complicated filtering. :)
--Took some inspiration and scripts from here:
-- Read a single Error Log File: http://www.sqlservercentral.com/blogs/steve_jones/2011/07/28/when-did-that-restore-finish_3F00_/
-- List all Error Log Files: https://ask.sqlservercentral.com/questions/99484/number-of-error-log-files.html
-- Combine all Error Log Files: https://blogs.msdn.microsoft.com/askjay/2011/10/10/searching-through-the-sql-server-errorlogs/
-- xp_readererrorlog Parameters: https://sqlserver-help.com/2014/12/10/sql-internals-useful-parameters-for-xp_readerrorlog/
--Works! Combined Sources found online to append results from all Log Files: - 01/06/2017 - MCR.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @LogType Int = 1--"1" = ERRORLOG, "2" = SqlAgent.
DECLARE @PrimaryFilter nVarChar(4000) = 'Restore is complete on database'--NULL--'Database backed up.' --This is searching ONLY the Message field. - 01/06/2017 - MCR.
DECLARE @SecondaryFilter nVarChar(4000) = NULL --WARNING: This 2nd Filter is ignored if the 1st Filter is null-or-empty.
DECLARE @StartDate DateTime = NULL--'01/01/1900'--'01/06/2017 00:40'-- --A NULL here will skip BOTH @StartDate and @EndDate Filters.
DECLARE @EndDate DateTime = NULL--GETDATE() --'01/06/2017 04:00'-- --A NULL here will skip BOTH @StartDate and @EndDate Filters.
DECLARE @FileList Table
subdirectory nVarChar(450) NOT NULL,--I do not know wha the max length is.
depth Int NOT NULL,
[file] Int NOT NULL
DECLARE @ErrorLogFileName nVarChar(4000) = CAST(SERVERPROPERTY(N'errorlogfilename') as nVarChar(4000))
DECLARE @ErrorLogPath nVarChar(4000) = SUBSTRING(@ErrorLogFileName, 1, LEN(@ErrorLogFileName) - CHARINDEX(N'\', REVERSE(@ErrorLogFileName))) + N'\'
INSERT INTO @FileList
EXEC xp_dirtree @ErrorLogPath, 0, 1;
DECLARE @NumErrorLogs Int = (SELECT COUNT(*) FROM @FileList as F WHERE F.subdirectory LIKE (CASE @LogType WHEN 1 THEN 'ERRORLOG%' WHEN 2 THEN 'SQLAGENT%' ELSE NULL END) )
DECLARE @ArchiveID Int = 0
DECLARE @ErrorLog Table
Process nVarChar(25), --Longest I've seen is 8.
Message nVarChar(1000),--Longest I've seen is 578.
ArchiveID Int NULL
WHILE(@ArchiveID < @NumErrorLogs)
INSERT INTO @ErrorLog(LogDate, Process, Message)
EXEC xp_readerrorlog @ArchiveID, @LogType, @PrimaryFilter, @SecondaryFilter, @StartDate, @EndDate, 'DESC'
UPDATE @ErrorLog SET ArchiveID = @ArchiveID WHERE ArchiveID IS NULL
SET @ArchiveID = @ArchiveID + 1
SELECT --SUBSTRING(L.Message, 34, (CHARINDEX ('''', L.Message, 34)-34))[DatabaseName],
DATENAME(WEEKDAY, L.LogDate)[DayOfWeek], L.*
FROM @ErrorLog as L
--Add More advanced Filtering in the Where-Clause here.
ORDER BY L.LogDate DESC
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
The "Log File Viewer" is no longer necessary!
Maybe you want to look for multiple mutually exclusive things while filtering out other things.
Just add whatever complicated Where-Clause you'd like towards the end of the Script.
You could save different Variable values for things you commonly look up while troubleshooting.
Sending automated alerts is a snap with this (just add a script to email if any matches are found).
Easily log to another table specific things you’re looking for, for reporting later.
I'd suggest wrapping this in a Sproc or Table-Function for reuse, using nothing but pure T-SQL.
Not a bad script to add to your DBA Utility Belt!