1

In SQL server instance(s) I manage; I see a lot of activity and messages in SQL server error log for e.g. backup or transaction log backup completed etc.. We have quite a few databases in one instance and amount of error log messages is sometimes quite a few to dig into.

As an accidental DBA, What approach or script can I use to filter the error log for date range specified or finding or filtering troubling errors out of lots of informational messags?

I meant to ask, is there is a T-SQL script or DMVs you can use to filter the error log?

3

You should use the system sp xp_ReadErrorLog to read the SQL Server error log. You can find some details in the following articles: Using xp_ReadErrorLog in SQL Server 2005 and Reading the SQL Server log files using TSQL.

  • thanks, I will look into it however that XP still not so flexible... I also found blog.waldenl.com/2010/04/… – SQL Learner Jan 8 '12 at 16:23
  • 1
    Yeah, I know that. That's why it's important to cycle the log file every day (or week..or whatever you feel comfortable on your server) and see the log each day. In the end the log file is just some kind of text file, so you can even read it using php and send emails.. The XP is nice 'cause you can integrate it in SQL Agent jobs that will fire every hour and will send emails in case some specific words will be read (like I/O, error..etc). – Marian Jan 8 '12 at 16:28
6

I suggest that you circumvent reading the error log directly and instead implement SQL Agent Alerting. Under this methodology, you'd simply create alerts for, say, when SQL Server throws an error (I recommend separate alerts for severity levels 19 -25) and tell SQL Server to email you when the error occurs.

You can then generally ignore error log except for specific troubleshooting situations.

General details are here. The details for setting up SQL Agent alerts specifically using SSMS are on TechNet are here and are the same in Books On-Line here.

Hope this helps, -Kevin

@kekline

2

You turn off the successful backup error log messages with Trace Flag 3226. Also the SQL Server Agent allows you to setup alerts based on either error number or severity, these can automatically email you when an issue occurs.

As you mentioned being an Accidental DBA there is a free eBook available which will help explain a lot of the issues you will face supporting SQL Server.

1

You can read the error log using xp_readerrorlog:

exec xp_readerrorlog 0, 1, 'search1', 'search2'

The first parameter is the error log number, use 0 for "errorlog", 1 for "errorlog.1", etc.

Second parameter is 1 for SQL Server and 2 for SQL Agent.

The optional third and fourth parameters contain strings to search for.

  • Edited... we prefix sp_ at work so my fingers type that without thinking :) – Andomar Jan 8 '12 at 17:00
  • I see, so maybe it's time you stop doing that ;). There's some evidence in just using the procedures name, not adding the sp_ prefix. Give a reading to the following articles: article1, article2, article3. Hope it's useful! – Marian Jan 8 '12 at 23:53
0

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
(
    LogDate DateTime,
    Process nVarChar(25),  --Longest I've seen is 8.
    Message nVarChar(1000),--Longest I've seen is 578.
    ArchiveID Int NULL
)
WHILE(@ArchiveID < @NumErrorLogs)
BEGIN
    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
END
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!

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.