Skip to content

Instantly share code, notes, and snippets.

@HenrikSPoulsenBaader

HenrikSPoulsenBaader/query errorlog for important stuff

Created October 23, 2018 13:33
Show Gist options
  • Save HenrikSPoulsenBaader/782c8538156585a4c3b31fdb668f9c26 to your computer and use it in GitHub Desktop.
Save HenrikSPoulsenBaader/782c8538156585a4c3b31fdb668f9c26 to your computer and use it in GitHub Desktop.
Download ZIP
How to query the Errorlog for important stuff
Raw
query errorlog for important stuff
SET NOCOUNT ON
-- Table variable to hold intermediate data
BEGIN TRY
DROP TABLE #ReportSQLErrorLogs
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #ReportSQLErrorLogs (
row_id BIGINT NOT NULL IDENTITY(1,1),
log_date datetime NULL,
processinfo nvarchar(16) NULL,
ProcessText NVARCHAR(2048) NULL,
CurrentLogNum TINYINT NULL
)
DECLARE @NumErrorLogs INT, @CurrentLogNum INT, @DateFrom DATE, @NewestLogEntry DATETIME
SET @DateFrom=GETDATE()
SET @DateFrom=DATEADD(DAY, -1, @DateFrom)
IF DATEPART(WEEKDAY, @datefrom) = 1 SET @DateFrom=DATEADD(DAY, -2, @DateFrom) /* report since yesterday midnight or Friday midnight */
SET @CurrentLogNum = 0
-- Get total number of log files from registry
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs',
@NumErrorLogs OUTPUT
SELECT @NumErrorLogs = ISNULL(@NumErrorLogs, 6)
-- NULL in registry entry for Error Log files mean default of 6 value
IF NOT EXISTS(SELECT * FROM #ReportSQLErrorLogs) BEGIN
WHILE @CurrentLogNum < @NumErrorLogs BEGIN
INSERT INTO #ReportSQLErrorLogs ( log_date, processinfo, ProcessText )
EXEC master..xp_readerrorlog @CurrentLogNum
RAISERROR ('at %i', 10, 1, @CurrentLogNum) WITH NOWAIT
SELECT @NewestLogEntry = MAX(log_date) FROM #ReportSQLErrorLogs RSEL WHERE CurrentLogNum IS NULL
UPDATE #ReportSQLErrorLogs SET CurrentLogNum=@CurrentLogNum WHERE CurrentLogNum IS NULL
SELECT @CurrentLogNum = @CurrentLogNum + 1
IF @NewestLogEntry < DATEADD(DAY, -1, @DateFrom) BREAK /* stop looping the log files, we have enough data */
END
END
DECLARE @Body VARCHAR(MAX), @Usual VARCHAR(MAX), @TableHead2 VARCHAR(max), @TableHead3 VARCHAR(max), @BLogic VARCHAR(MAX),
@TableHead VARCHAR(1000), @EmailHead VARCHAR(max),
@TableTail VARCHAR(1000),
@Subject VARCHAR(max)
, @Importance VARCHAR(6) ='Normal'
/* figure out if we have serious errors in the log */
SELECT TOP 1 @Importance = 'High'
FROM #ReportSQLErrorLogs I
WHERE (
I.ProcessText LIKE 'Error: 5180, Severity: 22%'
OR I.ProcessText LIKE 'Could not open FCB for invalid file ID % in database%'
OR I.ProcessText LIKE 'Database Instant File Initialization: disabled. For security and %'
OR I.ProcessText LIKE 'DBCC CHECKDB (%) WITH all_errormsgs, no_infomsgs, data_purity executed by % found [1-9]%'
OR I.ProcessText LIKE 'Error: 824, Severity: 24, State: 2.%'
)
AND I.log_date > @DateFrom
SET @TableTail = '</body></html>' ;
SET @EmailHead = '<html><head>' + '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} '
+ '</style>' + '</head>'
+ '<body>'
SET @TableHead = '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr><td align=center bgcolor=#E6E6FA><b>Row ID</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>File ID</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Log UTC Date</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Process Info</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Process Text</b></td></tr>' ;
---Create HTML mail Body
SELECT @Body = ( SELECT td = I.row_id,
'',
td = I.CurrentLogNum,
'',
td = CONVERT(VARCHAR(19), I.log_date, 121),
'',
td = ISNULL(I.processinfo, ''),
'',
td = ISNULL(I.ProcessText, ''),
''
FROM #ReportSQLErrorLogs I
WHERE I.ProcessText NOT LIKE '%error log%'
AND I.ProcessText NOT LIKE '%Database backed up%'
AND I.ProcessText NOT LIKE '%Logging SQL Server messages in file %'
AND I.ProcessText NOT LIKE '%Authentication mode%'
AND I.ProcessText NOT LIKE '%System Manufacturer%'
AND I.ProcessText NOT LIKE '%All rights reserved.%'
AND I.ProcessText NOT LIKE '%(c) 2005 Microsoft Corporation.%'
AND I.ProcessText NOT LIKE '%Microsoft SQL Server 2008 %'
AND I.ProcessText NOT LIKE '%SQL Trace ID%'
AND I.ProcessText NOT LIKE '%full-text catalog%'
AND I.ProcessText NOT LIKE '%Server process ID is%'
AND I.ProcessText NOT LIKE '%starting up database%'
AND I.ProcessText NOT LIKE '%found 0 errors%'
AND I.ProcessText NOT LIKE 'DBCC TRACE%3604%'
AND I.ProcessText NOT LIKE 'DBCC TRACE%610%'
AND I.ProcessText NOT LIKE 'This instance of SQL Server has been using a process ID of%'
AND I.ProcessText NOT LIKE 'The service account is%'
AND I.ProcessText NOT LIKE 'Default collation%'
AND I.ProcessText NOT LIKE 'Microsoft SQL Server 2014%'
AND I.ProcessText NOT LIKE 'Microsoft SQL Server 2016%'
AND I.ProcessText NOT LIKE 'Microsoft SQL Server 2017%'
AND I.ProcessText NOT LIKE 'UTC adjustment: %'
AND I.ProcessText NOT LIKE '(c) Microsoft Corporation.%'
AND I.ProcessText NOT LIKE 'Login failed for user%'
AND I.ProcessText NOT LIKE 'Error: 18456, Severity: 14%'
AND I.ProcessText NOT LIKE 'Error: 18470, Severity: 14%'
AND I.ProcessText NOT LIKE 'Recovery completed for database %'
AND I.ProcessText NOT LIKE 'BACKUP DATABASE successfully processed%'
AND I.ProcessText NOT LIKE 'CHECKDB for database % finished without errors on %(local time). This is an informational message only; no user action is required.%'
AND I.ProcessText NOT LIKE '%Parallel redo is started for database%with worker pool size%'
AND I.ProcessText NOT LIKE '%Parallel redo is shutdown for database%with worker pool size%'
AND I.ProcessText NOT LIKE '%transactions rolled back in database % action is required.'
AND I.ProcessText NOT LIKE '%transactions rolled forward in database % action is required.'
AND I.ProcessText NOT LIKE 'Recovery is writing a checkpoint in database % action is required.'
AND I.ProcessText NOT LIKE '%No user action is required.'
AND I.ProcessText NOT LIKE 'Using ''dbghelp.dll'' version %'
AND I.ProcessText NOT LIKE 'Service Broker manager has started.'
AND I.ProcessText NOT LIKE 'The tempdb database has 4 data file(s).'
AND I.ProcessText NOT LIKE 'The Service Broker endpoint is in disabled or stopped state.'
AND I.ProcessText NOT LIKE 'The Database Mirroring endpoint is in disabled or stopped state.'
AND I.ProcessText NOT LIKE 'Polybase feature disabled.'
AND I.ProcessText NOT LIKE 'Clearing tempdb database.'
AND I.ProcessText NOT LIKE 'The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) % for the SQL Server service.'
AND I.ProcessText NOT LIKE 'A self-generated certificate was successfully loaded for encryption.'
AND I.ProcessText NOT LIKE 'Server is listening on % <ipv%> 143%'
AND I.ProcessText NOT LIKE 'Dedicated admin connection support was established for listening remotely on port 1434.'
AND I.ProcessText NOT LIKE 'Resource governor reconfiguration succeeded.'
AND I.ProcessText NOT LIKE 'Common language runtime (CLR) functionality initialized using CLR version %'
AND I.ProcessText NOT LIKE 'CLR version % loaded.'
AND I.ProcessText NOT LIKE 'Software Usage Metrics is enabled.'
AND I.ProcessText NOT LIKE 'Query Store settings initialized with enabled = 1, '
AND I.ProcessText NOT LIKE 'In-Memory OLTP initialized on lowend machine.'
AND I.ProcessText NOT LIKE 'The maximum number of dedicated administrator connections for this instance is %1%'
AND I.ProcessText NOT LIKE 'InitializeExternalUserGroupSid failed. Implied authentication will be disabled.'
AND I.ProcessText NOT LIKE 'Implied authentication manager initialization failed. Implied authentication will be disabled.'
AND I.ProcessText NOT LIKE 'Buffer pool extension is already disabled. No action is necessary. '
AND I.ProcessText NOT LIKE 'Using conventional memory in the memory manager.'
AND I.ProcessText NOT LIKE 'Registry startup parameters: %'
AND I.ProcessText NOT LIKE 'Command Line Startup Parameters%'
AND I.ProcessText NOT LIKE '%NET Framework runtime has been stopped%'
AND I.ProcessText NOT LIKE 'Service Broker manager has shut down.'
AND I.log_date > @DateFrom /* since yesterday midnight or Friday midnight */
ORDER BY I.log_date DESC
FOR
XML RAW('tr'),
ELEMENTS
)
/* report the ususal suspects */
SET @TableHead2 = '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr>'
+ '<td align=center bgcolor=#E6E6FA><b>Text Group</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Row Count</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Min text</b></td>'
+ '<td align=center bgcolor=#E6E6FA><b>Max text</b></td>'
;
---Create HTML mail Body
SELECT @Usual = ( SELECT td = CASE
WHEN I.ProcessText LIKE '%error log%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Database backed up%' THEN 'Database backed up'
WHEN I.ProcessText LIKE '%Authentication mode%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Logging SQL Server messages in file %' THEN 'Noise'
WHEN I.ProcessText LIKE '%System Manufacturer%' THEN 'System Manufacturer'
WHEN I.ProcessText LIKE '%All rights reserved.%' THEN 'Noise'
WHEN I.ProcessText LIKE '%(c) 2005 Microsoft Corporation.%' THEN 'Microsoft Corporation.'
WHEN I.ProcessText LIKE '%Microsoft SQL Server 2008 %' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE '%SQL Trace ID%' THEN 'Noise'
WHEN I.ProcessText LIKE '%full-text catalog%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Server process ID is%' THEN 'Noise'
WHEN I.ProcessText LIKE '%starting up database%' THEN 'starting up database'
WHEN I.ProcessText LIKE '%found 0 errors%' THEN 'found 0 errors'
WHEN I.ProcessText LIKE 'DBCC TRACE%3604%' THEN 'DBCC TRACExx 3604'
WHEN I.ProcessText LIKE 'DBCC TRACE%610%' THEN 'DBCC TRACExx 610'
WHEN I.ProcessText LIKE 'The service account is%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Default collation%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2014%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2016%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2017%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'UTC adjustment: %' THEN 'Noise'
WHEN I.ProcessText LIKE '(c) Microsoft Corporation.%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'This instance of SQL Server has been using a process ID of%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Login failed for user%' THEN 'Login failed'
WHEN I.ProcessText LIKE 'Error: 18456, Severity: 14, State: 38.%' THEN 'Error: 18456/14/38'
WHEN I.ProcessText LIKE 'Error: 18456, Severity: 14%' THEN 'Error: 18456'
WHEN I.ProcessText LIKE 'Error: 18470, Severity: 14%' THEN 'Error: 18470'
WHEN I.ProcessText LIKE 'Recovery completed for database %' THEN 'Database restore'
WHEN I.ProcessText LIKE 'BACKUP DATABASE successfully processed%' THEN 'Database backed up'
WHEN I.ProcessText LIKE 'CHECKDB for database % finished without errors on %(local time). This is an informational message only; no user action is required.%' THEN 'Database backed up'
WHEN I.ProcessText LIKE '%Parallel redo is started for database%with worker pool size%' THEN 'Startup'
WHEN I.ProcessText LIKE '%Parallel redo is shutdown for database%with worker pool size%' THEN 'Startup'
WHEN I.ProcessText LIKE '%transactions rolled back in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE '%transactions rolled forward in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Recovery is writing a checkpoint in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.' THEN ' Rebooted'
WHEN I.ProcessText LIKE '%No user action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Using ''dbghelp.dll'' version %' THEN 'Startup'
WHEN I.ProcessText LIKE 'Service Broker manager has started.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The tempdb database has 4 data file(s).' THEN 'Startup'
WHEN I.ProcessText LIKE 'The Service Broker endpoint is in disabled or stopped state.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The Database Mirroring endpoint is in disabled or stopped state.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Polybase feature disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Clearing tempdb database.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) % for the SQL Server service.' THEN 'Startup'
WHEN I.ProcessText LIKE 'A self-generated certificate was successfully loaded for encryption.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Server is listening on % <ipv%> 143%' THEN 'Startup'
WHEN I.ProcessText LIKE 'Dedicated admin connection support was established for listening remotely on port 1434.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Resource governor reconfiguration succeeded.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Common language runtime (CLR) functionality initialized using CLR version %' THEN 'Startup'
WHEN I.ProcessText LIKE 'CLR version % loaded.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Software Usage Metrics is enabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Query Store settings initialized with enabled = 1, ' THEN 'Startup'
WHEN I.ProcessText LIKE 'In-Memory OLTP initialized on lowend machine.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The maximum number of dedicated administrator connections for this instance is %1%' THEN 'Startup'
WHEN I.ProcessText LIKE 'InitializeExternalUserGroupSid failed. Implied authentication will be disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Implied authentication manager initialization failed. Implied authentication will be disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Buffer pool extension is already disabled. No action is necessary. ' THEN 'Startup'
WHEN I.ProcessText LIKE 'Using conventional memory in the memory manager.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Registry startup parameters: %' THEN 'Startup'
WHEN I.ProcessText LIKE 'Command Line Startup Parameters%' THEN 'Startup'
WHEN I.ProcessText LIKE '%NET Framework runtime has been stopped%' THEN 'Startup'
WHEN I.ProcessText LIKE 'Service Broker manager has shut down.' THEN 'Startup'
ELSE 'unknown'
END,
'',
td = COUNT_BIG(*),
'',
td = Min(ProcessText),
'',
td = case when COUNT_BIG(*) = 1 then '' else MAX(ProcessText) end,
''
FROM #ReportSQLErrorLogs I
WHERE (I.ProcessText LIKE '%error log%'
OR I.ProcessText LIKE '%Database backed up%'
OR I.ProcessText LIKE '%Logging SQL Server messages in file %'
OR I.ProcessText LIKE '%Authentication mode%'
OR I.ProcessText LIKE '%System Manufacturer%'
OR I.ProcessText LIKE '%All rights reserved.%'
OR I.ProcessText LIKE '%(c) 2005 Microsoft Corporation.%'
OR I.ProcessText LIKE '%Microsoft SQL Server 2008 %'
OR I.ProcessText LIKE '%SQL Trace ID%'
OR I.ProcessText LIKE '%full-text catalog%'
OR I.ProcessText LIKE '%Server process ID is%'
OR I.ProcessText LIKE '%starting up database%'
OR I.ProcessText LIKE '%found 0 errors%'
OR I.ProcessText LIKE 'DBCC TRACE%3604%'
OR I.ProcessText LIKE 'DBCC TRACE%610%'
OR I.ProcessText LIKE 'The service account is%'
OR I.ProcessText LIKE 'Default collation%'
OR I.ProcessText LIKE 'Microsoft SQL Server 2016%'
OR I.ProcessText LIKE 'Microsoft SQL Server 2017%'
OR I.ProcessText LIKE 'Microsoft SQL Server 2014%'
OR I.ProcessText LIKE 'UTC adjustment: %'
OR I.ProcessText LIKE '(c) Microsoft Corporation.%'
OR I.ProcessText LIKE 'This instance of SQL Server has been using a process ID of%'
OR I.ProcessText LIKE 'Login failed for user%'
OR I.ProcessText LIKE 'Error: 18456, Severity: 14%'
OR I.ProcessText LIKE 'Recovery completed for database %'
OR I.ProcessText LIKE 'Error: 18470, Severity: 14%'
OR I.ProcessText LIKE 'BACKUP DATABASE successfully processed%'
OR I.ProcessText LIKE 'CHECKDB for database % finished without errors on %(local time). This is an informational message only; no user action is required.%'
OR I.ProcessText LIKE '%Parallel redo is started for database%with worker pool size%'
OR I.ProcessText LIKE '%Parallel redo is shutdown for database%with worker pool size%'
OR I.ProcessText LIKE '%transactions rolled back in database % action is required.'
OR I.ProcessText LIKE '%transactions rolled forward in database % action is required.'
OR I.ProcessText LIKE 'Recovery is writing a checkpoint in database % action is required.'
OR I.ProcessText LIKE '%No user action is required.'
OR I.ProcessText LIKE 'Using ''dbghelp.dll'' version %'
OR I.ProcessText LIKE 'Service Broker manager has started.'
OR I.ProcessText LIKE 'The tempdb database has 4 data file(s).'
OR I.ProcessText LIKE 'The Service Broker endpoint is in disabled or stopped state.'
OR I.ProcessText LIKE 'The Database Mirroring endpoint is in disabled or stopped state.'
OR I.ProcessText LIKE 'Polybase feature disabled.'
OR I.ProcessText LIKE 'Clearing tempdb database.'
OR I.ProcessText LIKE 'The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) % for the SQL Server service.'
OR I.ProcessText LIKE 'A self-generated certificate was successfully loaded for encryption.'
OR I.ProcessText LIKE 'Server is listening on % <ipv%> 143%'
OR I.ProcessText LIKE 'Dedicated admin connection support was established for listening remotely on port 1434.'
OR I.ProcessText LIKE 'Resource governor reconfiguration succeeded.'
OR I.ProcessText LIKE 'Common language runtime (CLR) functionality initialized using CLR version %'
OR I.ProcessText LIKE 'CLR version % loaded.'
OR I.ProcessText LIKE 'Software Usage Metrics is enabled.'
OR I.ProcessText LIKE 'Query Store settings initialized with enabled = 1, '
OR I.ProcessText LIKE 'In-Memory OLTP initialized on lowend machine.'
OR I.ProcessText LIKE 'The maximum number of dedicated administrator connections for this instance is %1%'
OR I.ProcessText LIKE 'InitializeExternalUserGroupSid failed. Implied authentication will be disabled.'
OR I.ProcessText LIKE 'Implied authentication manager initialization failed. Implied authentication will be disabled.'
OR I.ProcessText LIKE 'Buffer pool extension is already disabled. No action is necessary. '
OR I.ProcessText LIKE 'Using conventional memory in the memory manager.'
OR I.ProcessText LIKE 'Registry startup parameters: %'
OR I.ProcessText LIKE 'Command Line Startup Parameters'
OR I.ProcessText LIKE '%NET Framework runtime has been stopped%'
OR I.ProcessText LIKE 'Service Broker manager has shut down.'
)
AND I.log_date > @DateFrom /* since yesterday midnight or Friday midnight */
GROUP BY CASE
WHEN I.ProcessText LIKE '%error log%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Database backed up%' THEN 'Database backed up'
WHEN I.ProcessText LIKE '%Authentication mode%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Logging SQL Server messages in file %' THEN 'Noise'
WHEN I.ProcessText LIKE '%System Manufacturer%' THEN 'System Manufacturer'
WHEN I.ProcessText LIKE '%All rights reserved.%' THEN 'Noise'
WHEN I.ProcessText LIKE '%(c) 2005 Microsoft Corporation.%' THEN 'Microsoft Corporation.'
WHEN I.ProcessText LIKE '%Microsoft SQL Server 2008 %' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE '%SQL Trace ID%' THEN 'Noise'
WHEN I.ProcessText LIKE '%full-text catalog%' THEN 'Noise'
WHEN I.ProcessText LIKE '%Server process ID is%' THEN 'Noise'
WHEN I.ProcessText LIKE '%starting up database%' THEN 'starting up database'
WHEN I.ProcessText LIKE '%found 0 errors%' THEN 'found 0 errors'
WHEN I.ProcessText LIKE 'DBCC TRACE%3604%' THEN 'DBCC TRACExx 3604'
WHEN I.ProcessText LIKE 'DBCC TRACE%610%' THEN 'DBCC TRACExx 610'
WHEN I.ProcessText LIKE 'The service account is%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Default collation%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2014%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2016%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'Microsoft SQL Server 2017%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'UTC adjustment: %' THEN 'Noise'
WHEN I.ProcessText LIKE '(c) Microsoft Corporation.%' THEN 'Microsoft Corporation'
WHEN I.ProcessText LIKE 'This instance of SQL Server has been using a process ID of%' THEN 'Noise'
WHEN I.ProcessText LIKE 'Login failed for user%' THEN 'Login failed'
WHEN I.ProcessText LIKE 'Error: 18456, Severity: 14, State: 38.%' THEN 'Error: 18456/14/38'
WHEN I.ProcessText LIKE 'Error: 18456, Severity: 14%' THEN 'Error: 18456'
WHEN I.ProcessText LIKE 'Error: 18470, Severity: 14%' THEN 'Error: 18470'
WHEN I.ProcessText LIKE 'Recovery completed for database %' THEN 'Database restore'
WHEN I.ProcessText LIKE 'BACKUP DATABASE successfully processed%' THEN 'Database backed up'
WHEN I.ProcessText LIKE 'CHECKDB for database % finished without errors on %(local time). This is an informational message only; no user action is required.%' THEN 'Database backed up'
WHEN I.ProcessText LIKE '%Parallel redo is started for database%with worker pool size%' THEN 'Startup'
WHEN I.ProcessText LIKE '%Parallel redo is shutdown for database%with worker pool size%' THEN 'Startup'
WHEN I.ProcessText LIKE '%transactions rolled back in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE '%transactions rolled forward in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Recovery is writing a checkpoint in database % action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.' THEN ' Rebooted'
WHEN I.ProcessText LIKE '%No user action is required.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Using ''dbghelp.dll'' version %' THEN 'Startup'
WHEN I.ProcessText LIKE 'Service Broker manager has started.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The tempdb database has 4 data file(s).' THEN 'Startup'
WHEN I.ProcessText LIKE 'The Service Broker endpoint is in disabled or stopped state.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The Database Mirroring endpoint is in disabled or stopped state.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Polybase feature disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Clearing tempdb database.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) % for the SQL Server service.' THEN 'Startup'
WHEN I.ProcessText LIKE 'A self-generated certificate was successfully loaded for encryption.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Server is listening on % <ipv%> 143%' THEN 'Startup'
WHEN I.ProcessText LIKE 'Dedicated admin connection support was established for listening remotely on port 1434.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Resource governor reconfiguration succeeded.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Common language runtime (CLR) functionality initialized using CLR version %' THEN 'Startup'
WHEN I.ProcessText LIKE 'CLR version % loaded.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Software Usage Metrics is enabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Query Store settings initialized with enabled = 1, ' THEN 'Startup'
WHEN I.ProcessText LIKE 'In-Memory OLTP initialized on lowend machine.' THEN 'Startup'
WHEN I.ProcessText LIKE 'The maximum number of dedicated administrator connections for this instance is %1%' THEN 'Startup'
WHEN I.ProcessText LIKE 'InitializeExternalUserGroupSid failed. Implied authentication will be disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Implied authentication manager initialization failed. Implied authentication will be disabled.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Buffer pool extension is already disabled. No action is necessary. ' THEN 'Startup'
WHEN I.ProcessText LIKE 'Using conventional memory in the memory manager.' THEN 'Startup'
WHEN I.ProcessText LIKE 'Registry startup parameters: %' THEN 'Startup'
WHEN I.ProcessText LIKE 'Command Line Startup Parameters%' THEN 'Startup'
WHEN I.ProcessText LIKE '%NET Framework runtime has been stopped%' THEN 'Startup'
WHEN I.ProcessText LIKE 'Service Broker manager has shut down.' THEN 'Startup'
ELSE 'unknown'
END
FOR
XML RAW('tr'),
ELEMENTS
)
-- Replace the entity codes and row numbers
SET @Usual = REPLACE(@Usual, '_x0020_', SPACE(1))
SET @Usual = REPLACE(@Usual, '_x003D_', '=')
SET @Usual = REPLACE(@Usual, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Usual = REPLACE(@Usual, '<TRRow>0</TRRow>', '')
--SELECT @Usual
-- Replace the entity codes and row numbers
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
SET @Body = REPLACE(@Body, '_x003D_', '=')
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')
SELECT @Body = @EmailHead +
+ '<h2><br>Un-usual Errorlog event<br></h2>'
+ COALESCE(@TableHead + @Body + '</table>' , '')
+ '<h2><br>Plus the usual suspects<br></h2>'
+ @TableHead2
+ @Usual + '</table>'
+ '<h2><br>Plus what we logged in BLogic<br></h2>'
+ @TableTail
SELECT @Subject = 'Logs Report since ' + CONVERT(VARCHAR(10), @DateFrom, 121)
IF @Body IS NOT NULL BEGIN --- Send HTML mail
DECLARE @Recipients VarChar(Max) = 'myemail@mail.com'
EXEC msdb.dbo.sp_send_dbmail @recipients = @Recipients,
@subject = @subject,
@profile_name = 'GMail', -- Change profile name according to your own
@body = @Body,
@body_format = 'HTML' --Mail format should be HTML
, @Importance = @Importance;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment