The stored procedure exposes the following parameters:
p1: numeric value identifying the index of the log file: 0 = current, 1 = archive #1, 2 = archive #2, ...
p2: log file type: 1 or NULL = SQL Server log, 2 = SQL Agent log
p3: the string to search for (e.g. Error)
p4: a second string t to search for (allows refining the results)
p5: start time of the events to be considered
p6: end time of the events to be considered
p7: sorting order for results: N'asc' = ascending, N'desc' = descending
The only required parameter is the first, thus the stored procedure can be called as follows:
-- reading the current SQL Server log (to be run individually)
EXEC xp_readerrorlog 0 EXEC xp_readerrorlog 0, 1 EXEC xp_readerrorlog 0, 1, N'Login failed' -- filters for failed logins EXEC xp_readerrorlog 0, 1, N'DBCC CHECKDB' -- filters for database consistency checks EXEC xp_readerrorlog 0, 1, N'Error' -- filters the Errors EXEC xp_readerrorlog 0, 1, N'Error', N'Severity: 14' -- filters the Errors with severity 14 EXEC xp_readerrorlog 0, 1, N'', N'', '20200601', NULL, NULL -- start date EXEC xp_readerrorlog 0, 1, N'', N'', '20200601', '20200607', NULL -- start date & end date EXEC xp_readerrorlog 0, 1, N'', N'', NULL, NULL, N'asc' -- ascending sorting -- reading the last SQL Server log EXEC xp_readerrorlog 1, 1 -- reading the current SQL Agent log EXEC xp_readerrorlog 0, 2
Especially when filtering for certain information, the output can be displayed easily into a SSRS report. Besides errors typically I'm looking for failed logins or the results of consistency checks.
The number of log files available depends on how the SQL Server Logs were configured.
Even if the stored procedure provides the needed information fast, the error and the corresponding description are shown in separate records with the same timestamp. To process the records one can use a temporary table matching output's definition. To facilitate the selection of consecutive records a primary key can be added as well. One can dump into the table some or all of the log files and perform various searches or analyses.
-- dropping the table --DROP TABLE IF EXISTS #tbl -- creating a temporary table CREATE TABLE #tbl ( id int IDENTITY(1,1) NOT NULL , LogDate datetime2(3) , ProcessInfo nvarchar(50) , LogText varchar(max)) -- reading the current log into it INSERT INTO #tbl EXEC sys.xp_readerrorlog 0, 1 -- reading the last archive log into it INSERT INTO #tbl EXEC sys.xp_readerrorlog 1, 1 -- retrieving the errors together with their descriptions SELECT A.LogDate , A.ProcessInfo , A.LogText , B.LogText FROM #tbl A JOIN #tbl B ON A.Id = B.Id - 1 WHERE A.LogText LIKE '%Error:%'
Here's the output of the last query (it will look differently on your server):
The temporary table can be used for further analyses (e.g. displaying the first, respectively last occurrence of the error together with a count):
-- first/last occurence of an error SELECT Min(A.LogDate) FirstOccurence , Max(A.LogDate) LastOccurence , count(*) NoRecords , A.LogText , B.LogText FROM #tbl A JOIN #tbl B ON A.Id = B.Id - 1 WHERE A.LogText LIKE '%Error:%' GROUP BY A.LogText , B.LogText
Warnings:
Do not forget to dump the temporary table when finished!
The code is provided only for exemplification purposes. You can use the above code on your own risk!
Be careful when the number of records in the logs is too big, because reading all the records can create temporary performance issues!
Undocumented features can be deprecated in future versions of SQL Server, therefore they should be used with precaution in long-term solutions.
Happy coding!
Happy coding!
No comments:
Post a Comment