forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathRead_Error_Log_File.sql
More file actions
125 lines (107 loc) · 4.45 KB
/
Copy pathRead_Error_Log_File.sql
File metadata and controls
125 lines (107 loc) · 4.45 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
/*
Created: 2019-03-05 by Max Vernon
Modified: 2019-03-26 by Konstantin Taranov
Original link: https://www.sqlserverscience.com/tools/error-log-analysis-script/
Source link: https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/Read_Error_Log_File.sql
Note: Shows the contents of the SQL Server Error Log files, up to a maximum of the most recent 9 files.
Certain "noisy" log messages are filtered out via the #exclusions temp table.
*/
SET NOCOUNT ON;
DECLARE @ErrorLogType tinyint;
SET @ErrorLogType = 1; --1 for SQL Server Error log, 2 for SQL Server Agent Log
IF OBJECT_ID(N'tempdb..#exclusions', N'U') IS NOT NULL DROP TABLE #exclusions;
CREATE TABLE #exclusions
(
LogTextToExclude VARCHAR(4000)-- COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
-- PRIMARY KEY CLUSTERED
, LogType tinyint NOT NULL
);
-- the pipe symbol, "|", should be used as the escape character
INSERT INTO #exclusions (LogTextToExclude, LogType)
VALUES ('%Using ''dbghelp.dll'' version ''4.0.5''%', 1)
, ('%informational message%no user action is required%', 1)
, ('Log was backed up%', 1)
, ('Database backed up%', 1)
, ('BACKUP DATABASE %', 1)
, ('DBCC %', 1)
, ('Setting database option RECOVERY to %', 1)
, ('This instance of SQL Server has been using a process ID of %', 1)
, ('Starting up database%', 1)
, ('Software Usage Metrics is enabled.', 1)
, ('|[184|] Job completion for % is being logged to sysjobhistory%', 2)
, ('|[177|] Job % has been requested to run by Schedule %', 2)
, ('|[248|] Saving NextRunDate/Times for all updated job schedules...%', 2)
, ('|[249|] % job schedule|(s|) saved%', 2)
, ('|[182|] Job completion for % is being logged to the eventlog%', 2)
, ('|[171|] There are % alert|(s|) in the alert cache%', 2)
, ('|[168|] There are 21 job(s) |[0 disabled|] in the job cache%', 2)
, ('|[170|] Populating alert cache...%', 2)
, ('|[473|] Database Mail profile DBA refreshed.%', 2)
, ('|[353|] Mail session started%', 2)
, ('|[273|] Mail dispatcher started%', 2)
, ('|[174|] Job scheduler engine started |(maximum user worker threads: 200, maximum system worker threads: 100|)%', 2)
, ('|[193|] Alert engine started |(using Eventlog Events|)%', 2)
, ('|[146|] Request servicer engine started%', 2)
, ('|[167|] Populating job cache...%', 2)
, ('|[133|] Support engine started%', 2)
, ('|[271|] Idle processor poller started%', 2)
IF OBJECT_ID(N'tempdb..#mverrlog', N'U') IS NOT NULL
DROP TABLE #mverrlog;
CREATE TABLE #mverrlog
(
RowNum INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ErrorLogFileNum INT NULL
, LogDate DATETIME NOT NULL
, ProcessInfo NVARCHAR(255) NOT NULL
, [Text] NVARCHAR(4000) NOT NULL
);
DECLARE @ErrorLogCount int;
DECLARE @cmd varchar(2000);
DECLARE @msg varchar(1000);
DECLARE @ErrorlogEnum TABLE(
[Archive #] varchar(3) NOT NULL PRIMARY KEY CLUSTERED
, [Date] datetime NOT NULL
, [Log File Size (Byte)] int NOT NULL
);
INSERT INTO @ErrorlogEnum ([Archive #], [Date], [Log File Size (Byte)])
EXEC sys.sp_enumerrorlogs;
SET @ErrorLogCount = COALESCE((
SELECT COUNT(1)
FROM @ErrorlogEnum eln
WHERE eln.[Log File Size (Byte)] > 0
), 0);
IF @ErrorLogCount > 9 SET @ErrorLogCount = 9;
DECLARE @FileNum INT;
SET @FileNum = 0
WHILE @FileNum < @ErrorLogCount
BEGIN TRY
SET @msg = 'Retreiving log ' + CONVERT(varchar(100), @FileNum);
RAISERROR (@msg, 0, 1) WITH NOWAIT; --send progress to the "Messages" tab
INSERT INTO #mverrlog (LogDate, ProcessInfo, [Text])
EXEC sys.xp_readerrorlog @FileNum, @ErrorLogType;
UPDATE #mverrlog
SET ErrorLogFileNum = @FileNum
WHERE ErrorLogFileNum IS NULL;
SET @FileNum = @FileNum + 1;
END TRY
BEGIN CATCH
PRINT 'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
', State: ' + CONVERT(varchar(5), ERROR_STATE()) +
', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') +
', Line: ' + CONVERT(varchar(5), ERROR_LINE()) +
', User name: ' + CONVERT(sysname, ORIGINAL_LOGIN());
PRINT(ERROR_MESSAGE());
PRINT('Change @FileNum to smaller value!');
END CATCH
SELECT FileNumber = el.ErrorLogFileNum
, el.LogDate
, el.Text
FROM #mverrlog el
WHERE NOT EXISTS (
SELECT 1
FROM #exclusions e
WHERE (el.Text LIKE e.LogTextToExclude ESCAPE '|')
AND e.LogType = @ErrorLogType
)
ORDER BY el.ErrorLogFileNum, el.RowNum DESC;