I have seen a number of environments concerned with performance issues, and jump to looking at performance metrics when these performance problems are in fact part of the symptom, and not the actual underlying issue at hand. Anytime SQL has an issue with stability, performance issues are usually not that far behind. It is alarming that in most environments I have observed, the error logs are one of the last things that are checked. In fact what I usually see is when someone experiences performance issues the first thing in the troubleshooting toolbox is to start a profiler trace, or sometimes even perfmon.
Both of these tools take up resources and put performance more at risk. To be clear, I’m not saying these tools are not extremely valuable assets in anyone’s toolbox, but a quick look at the system for errors could go a long way in targeting the principle issue. Then, if additional research is needed, use profiler or perfmon. Targeting events would be more surgical instead of opening the events up to look for anything.
A quick look at the error logs…yeah right! That is an issue and maybe why the error logs are one of the last places to look. First, there are six different log files, and going through the Management directory to pull each file individually can be tedious to say the least. Second, searching for anything in the log file is like looking for a needle in a proverbial haystack. Last, with the log files there is no way to really do a search to trend how many occurrences of an error has happened, and when, without doing so manually. These issues with the error log files makes them an unproductive tool when one is under the gun to target a problem quickly.
Check for Errors with T-SQL
Luckily, there is a way to bypass the manual search of the log files all together and use T-SQL to quickly and efficiently find any errors in the six log files.
I wrote a query (find it below) that would quickly load the files in a temp table, create a temp table that I could append “include” and “exclude” keywords into, and then scrub out all the noise from the errorlog files. That leaves me with a small result set of errors that I could analyze and then start looking for a solution to the problem. Scrubbing the errorlogs in this way also allows me some flexibility in how I want my result set. I can add “include” or “exclude” keywords, or I can comment everything out and start issuing select statements against the errorlogs in its raw form.
The first part of the query creates a temp table to hold the errorlogs and then uses xp_readerrlog to populate the temp table. After that it creates a second temp table and inserts the keywords. Some words like “failed”, “hung”, and “error” are set with a type of “INCLUDE” which I want to be part of the result set. Other keywords like “backed up”, “differential”, and “AppDomain” should be excluded from the final result set and will therefore have a type of “EXCLUDE”.
Next, the query loops through the exclude keywords and removes those records. Last, one more temp table is created and a loop through the include words starts an action to insert into the final temp table. Both temp tables (#ErrLog and #ErrLog2) are still available and can be searched, it’s just that the temp table #ErrLog2 will be more streamlined for just errors remaining in that table.
This could be extended to a long-term approach where new records are appended to a table on a nightly basis, executed in a job so that errors can be analyzed, and trended on a regular basis, or the script can be run just as it is when one needs to look at the error log in a timely fashion. Once the query window that holds the script is removed, all objects will be deleted without leaving anything behind.
CREATE TABLE #ErrLog
(
[LogDate] DATETIME
,[ProcessInfo] NVARCHAR(255)
,[Text] NVARCHAR(MAX)
);
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 0;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 1;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 2;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 3;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 4;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 5;
INSERT #ErrLog
EXEC master.dbo.xp_readerrorlog 6;
CREATE TABLE #KeyWord
(
[KeyWord_ID] INT IDENTITY(1,1)
,[KeyWord] NVARCHAR(255)
,[Type] NVARCHAR(255)
)
INSERT #KeyWord
(KeyWord, Type)
SELECT 'attempting to unlock unowned', 'INCLUDE'
UNION
SELECT 'cannot find', 'INCLUDE'
UNION
SELECT 'cannot obtain a LOCK', 'INCLUDE'
UNION
SELECT 'CImageHelper', 'INCLUDE'
UNION
SELECT 'Could not allocate new page', 'INCLUDE'
UNION
SELECT 'Error:', 'INCLUDE'
UNION
SELECT 'Error', 'INCLUDE'
UNION
SELECT 'Expire', 'INCLUDE'
UNION
SELECT 'failed', 'INCLUDE'
UNION
SELECT 'hung', 'INCLUDE'
UNION
SELECT 'is full', 'INCLUDE'
UNION
SELECT 'Login failed for user', 'EXCLUDE'
UNION
SELECT 'stack', 'INCLUDE'
UNION
SELECT 'Unable', 'INCLUDE'
UNION
SELECT 'Victim Resource', 'INCLUDE'
UNION
SELECT 'WARNING', 'INCLUDE'
UNION
SELECT 'DEADLOCK', 'INCLUDE'
UNION
SELECT '1204', 'INCLUDE'
UNION
SELECT '823', 'INCLUDE'
UNION
SELECT '824', 'INCLUDE'
UNION
SELECT '825', 'INCLUDE'
UNION
SELECT 'A read of the file', 'INCLUDE'
UNION
SELECT '1073759', 'EXCLUDE'
UNION
SELECT '15457', 'EXCLUDE'
UNION
SELECT '50000', 'EXCLUDE'
UNION
SELECT '60000', 'EXCLUDE'
UNION
SELECT 'Log was backed up', 'EXCLUDE'
UNION
SELECT 'Log backed up:', 'EXCLUDE'
UNION
SELECT 'differential changes were backed up', 'EXCLUDE'
UNION
SELECT 'AppDomain', 'EXCLUDE'
UNION
SELECT '18456', 'EXCLUDE'
UNION
SELECT 'Database differential changes backed up', 'EXCLUDE'
UNION
SELECT 'Errorlog', 'EXCLUDE'
UNION
SELECT 'Logging', 'EXCLUDE'
UNION
SELECT 'DBCC', 'EXCLUDE'
UNION
SELECT 'The error log has been reinitialized', 'EXCLUDE'
UNION
SELECT 'This is an informational message only', 'EXCLUDE'
DECLARE @v_MinINT INT
,@v_MaxINT INT
,@v_KeyWord NVARCHAR(255)
,@v_SQL NVARCHAR(MAX)
/* Delete reords in the exclude list */
SELECT @v_MinINT = MIN([KeyWord_ID])
,@v_MaxINT = MAX([KeyWord_ID])
FROM #KeyWord
WHERE [type] = 'EXCLUDE';
WHILE @v_MinINT <= @v_MaxINT
BEGIN -- remove records in exclude list
SELECT @v_KeyWord = [KeyWord]
FROM #KeyWord
WHERE [KeyWord_ID] = @v_MinINT;
SELECT @v_SQL =
N'DELETE #ErrLog ' + CHAR(13) + CHAR(10) +
' WHERE [Text] LIKE ''%' + CONVERT(VARCHAR(MAX), @v_KeyWord) + '%''';
EXEC [dbo].[sp_executesql] @v_SQL;
SELECT @v_MinINT = MIN([KeyWord_ID])
FROM #KeyWord
WHERE [type] = 'EXCLUDE'
AND [KeyWord_ID] > @v_MinINT;
END; -- remove records in exclude list
CREATE TABLE #ErrLog2
(
[LogDate] DATETIME
,[ProcessInfo] NVARCHAR(255)
,[Text] NVARCHAR(MAX)
);
/* Get Just The records left in the include list */
SELECT @v_MinINT = MIN([KeyWord_ID])
,@v_MaxINT = MAX([KeyWord_ID])
FROM #KeyWord
WHERE [type] = 'INCLUDE';
WHILE @v_MinINT <= @v_MaxINT
BEGIN -- remove records in exclude list
SELECT @v_KeyWord = [KeyWord]
FROM #KeyWord
WHERE [KeyWord_ID] = @v_MinINT;
SELECT @v_SQL =
'INSERT #ErrLog2 ' + CHAR(13) + CHAR(10) +
'SELECT [LogDate], [ProcessInfo], [Text] ' + CHAR(13) + CHAR(10) +
' FROM #ErrLog ' + CHAR(13) + CHAR(10) +
' WHERE [Text] LIKE ''%' + @v_KeyWord + '%''';
EXEC [dbo].[sp_executesql] @v_SQL;
SELECT @v_MinINT = MIN([KeyWord_ID])
FROM #KeyWord (NOLOCK)
WHERE [type] = 'INCLUDE'
AND [KeyWord_ID] > @v_MinINT;
END; -- Insert just the keywords in the include list
SELECT *
FROM #ErrLog2
ORDER BY [LogDate];
/*
DROP TABLE #ErrLog;
DROP TABLE #KeyWord;
DROP TABLE #ErrLog2;
*/
