First of all, I prepared the data in SQL, this is the statement:
--===============================================================
--// Created a table to save the last status
--===============================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ServerSQLErrorLog](
[ServerSQLErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ServerName] [varchar](100) NOT NULL,
[PerformanceIndicatorCategoryName] [varchar](100) NOT NULL,
[PerformanceIndicatorName] [varchar](100) NOT NULL,
[PerformanceAVGIndicatorValue] [float] NULL,
[PerformanceIndicatorValue] [float] NOT NULL,
[IsOperational] [bit] NULL,
[SQLErrorDescription] [varchar](1700) NULL,
[SQLErrorProcessInfo] [varchar](200) NOT NULL,
[SQLErrorChecked] [bit] NOT NULL,
[SQLErrorRunDateID] [int] NOT NULL,
[SQLErrorRunDate] [datetime] NOT NULL,
[DataHash] AS (hashbytes('MD5',concat([SQLErrorDescription],''))),
[CreatedDateOn] [datetime] NOT NULL,
CONSTRAINT [ServerSQLErrorLog_pk] PRIMARY KEY CLUSTERED
(
[ServerSQLErrorLogID] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ServerSQLErrorLog] ADD DEFAULT ((0)) FOR [SQLErrorChecked]
GO
--===============================================================
--// Created a SP to feed the table with only the relevant issues
--===============================================================
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_DBAWEB_SQLErrorLog]
As
/*****************************************************************************************************************
Name: dbo.sp_DBAWEB_SQLErrorLog
View all the SQL Job status.
*****************************************************************************************************************
Change History
*****************************************************************************************************************
Date: Author: Description:
----- ------- -------------------
10 Oct 2018 Leandro Buffone Created Initial Version
*****************************************************************************************************************
Usage:
set statistics io on
set statistics time on
Exec sp_DBAWEB_SQLErrorLog
--//Update to checked the logs
update [dbo].[ServerSQLErrorLog] set [SQLErrorChecked] = 1
select * from [dbo].[ServerSQLErrorLog]
*****************************************************************************************************************/
declare
@FirstLog smallint,
@LastLog smallint,
@BufferRecords smallint,
@SQL varchar(4000),
@Output varchar(4000),
@ServerName varchar(100) = @@ServerName,
@PerformanceIndicatorCategoryName varchar(100) = 'SQL Log',
@PerformanceIndicatorName varchar(100) = 'SQL Log',
@IsOperational bit = (Case When (datepart(hour, getdate()) Between 8 and 18) Then 1 Else 0 End);
select
@FirstLog = 0, --> Defaults to current logfile
@LastLog = 1, --> Defaults to logfile immediately preceding current
@BufferRecords = 4 --> Used to give a frame of reference to the error message
set nocount on
declare @count smallint,
@alert char(20)
select @count = 0,
@SQL = '',
@output = '',
@alert = '---->'
---------------------------------------------------------------------
-- Validate input parameters --
---------------------------------------------------------------------
IF (@FirstLog > @LastLog)
BEGIN
select @lastLog = @FirstLog + 1
END
---------------------------------------------------------------------
-- Create work tables --
---------------------------------------------------------------------
IF (object_id('tempdb..#ERRORLOG') IS NOT NULL)
drop table #ERRORLOG
CREATE TABLE #ERRORLOG
(
LogID int identity primary key clustered,
LogDate datetime NULL,
ProcessInfo varchar(60) NULL,
Alert char(10) default ' ',
LogEntry varchar(2000) NULL,
Row smallint NULL
)
---------------------------------------------------------------------
-- Import SQL Error Logs --
---------------------------------------------------------------------
select @count = @FirstLog
WHILE (@Count <= @LastLog)
BEGIN
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
select @output = '-- Processing: ERRORLOG' + case(@count) when 0 then ' ' else '.' + convert(char(3), @count) end + ' --'
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',@output)
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
select @SQL = 'exec master..sp_readerrorlog ' + convert(varchar(3), @count)
insert into #errorlog (LogDate, ProcessInfo, LogEntry)
execute (@SQL)
select @count = @count + 1
END
---------------------------------------------------------------------
-- Remove unwanted entries --
---------------------------------------------------------------------
delete
from #ERRORLOG
where LogEntry like '%Bypassing recovery for database%'
or LogEntry like '%Setting database option ANSI_WARNINGS%'
or LogEntry like '%Log backed up with following information:%'
or LogEntry like '%Login succeeded for user%'
or LogEntry like '%found 0 errors and repaired 0 errors%'
or LogEntry like '%Database differential changes%'
or LogEntry like '%The certificate was successfully loaded%'
or LogEntry like '%this is an informational message only%'
or LogEntry like '%logging sql server messages in file%'
or LogEntry like '%Recovery complete.%'
or LogEntry like '%Starting up database ''ERROR_LOGGING''%'
or LogEntry like '%Database backed up: Database: %'
or LogEntry like '%Log backed up: Database: %'
or LogEntry like '%BACKUP DATABASE successfully%'
or LogEntry like '%RESTORE DATABASE successfully%'
or LogEntry like '%Starting up database %TurnersGroup_MSCRM%'
or LogEntry like '%Database was restored: Database: TurnersGroup_MSCRM%'
or LogEntry like '%The database %TurnersGroup_MSCRM% is marked RESTORING %'
or LogEntry like '%An error occurred in the Microsoft .NET Framework while trying to load assembly id 65539%'
or LogEntry like '%Error: 10314, Severity: 16, State: 11.%'
or LogEntry like '%allocate%'
---------------------------------------------------------------------
-- Mark items of interest --
---------------------------------------------------------------------
update #ERRORLOG
set Alert = @alert
where (LogEntry like '%err%'
or LogEntry like '%exception%'
or LogEntry like '%violation%'
or LogEntry like '%warn%'
or LogEntry like '%kill%'
or LogEntry like '%dead%'
or LogEntry like '%encounter%'
or LogEntry like '%cannot%'
or LogEntry like '%could%'
or LogEntry like '%fail%'
or LogEntry like '%full%'
or LogEntry like '%not%'
or LogEntry like '%terminate%'
or LogEntry like '%bypass%'
or LogEntry like '%recover%'
or LogEntry like '%roll%'
or LogEntry like '%upgrade%'
or LogEntry like '%victim%'
or LogEntry like '%stop%'
or LogEntry like '%shut%'
or LogEntry like '%timed out%'
or LogEntry like '%truncate%'
or LogEntry like '%terminat%')
and (ProcessInfo <> ' ' or ProcessInfo IS NULL);
---------------------------------------------------------------------
-- Generate report --
---------------------------------------------------------------------
-- Show entries of interest + buffer records
Merge [dbo].[ServerSQLErrorLog] d
Using (
select
ServerName = @ServerName,
PerformanceIndicatorCategoryName = @PerformanceIndicatorCategoryName,
PerformanceIndicatorName = @PerformanceIndicatorName,
PerformanceAVGIndicatorValue = 1,
PerformanceIndicatorValue = 1,
IsOperational = @IsOperational,
SQLErrorRunDate = A.LogDate,
SQLErrorChecked = 0,
SQLErrorRunDateID = dbo.fnDateKey(A.LogDate),
SQLErrorProcessInfo = A.ProcessInfo,
SQLErrorDescription = left(A.logEntry, 1700),
DataHash = hashbytes('MD5',concat(left(A.logEntry, 1700),'')),
CreatedDateOn = getdate()
from #ERRORLOG A
join (select LogID from #ERRORLOG where Alert = @alert) B on (A.LogID >= (B.LogID - @BufferRecords)) and (A.LogID <= (B.LogID + @BufferRecords))
and A.LogDate > DateAdd(Day,-1,Getdate())
) s on d.DataHash = s.DataHash
when not matched then
Insert ( ServerName, PerformanceIndicatorCategoryName, PerformanceIndicatorName, SQLErrorChecked, PerformanceAVGIndicatorValue, PerformanceIndicatorValue, IsOperational, SQLErrorRunDate, SQLErrorRunDateID, SQLErrorProcessInfo, SQLErrorDescription, CreatedDateOn)
Values (s.ServerName, s.PerformanceIndicatorCategoryName, s.PerformanceIndicatorName, s.SQLErrorChecked, s.PerformanceAVGIndicatorValue, s.PerformanceIndicatorValue, s.IsOperational, s.SQLErrorRunDate, s.SQLErrorRunDateID, s.SQLErrorProcessInfo, s.SQLErrorDescription, s.CreatedDateOn)
;
--===============================================================
--// I scheduled a SQL Job to run it per hour
--===============================================================
--===============================================================
--// In Power BI I'm showing only the errors not checked yet (SQLErrorChecked = 0)
--===============================================================
--===============================================================
--// After checked the issues, I need to changed the SQLErrorChecked to 1
--===============================================================