Microsoft SQL Server: Monitoring SQL Server Error Log

Microsoft SQL Server: Monitoring SQL Server Error Log

Monitoring SQL Server Error Log

Reviewing SQL server ERROR.LOG can be a long and time consuming task ,if you are managing multiple SQL Server installations. However you usually need to see only the error information and not the informational entries. This article will help you with tracking the important information from the errorlog.

To accomplish this task here is what I've done.

1. Create a table to hold all the data from current ERROR.LOG
2. Truncate table and upload the current ERROR.LOG
3. Create Store procedure and Execute to analyze the data and produce an output with only error type information

-- Create a table

USE [DBAmantanence]

GO

CREATE TABLE [dbo].[ErrLogData](

 [LogID] [int] IDENTITY(1,1) NOT NULL,

 [LogDate] [datetime] NULL,

 [ProcessInfo] [nvarchar](50) NULL,

 [LogText] [nvarchar](4000) NULL,

 [SQLServerName] [nvarchar](150) NULL,

PRIMARY KEY CLUSTERED

(

 [LogID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Now we create store procedure to clear the table and insert the ERROR.LOG data into the table.

USE [DBAmantanence]

GO

 SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 Create PROCEDURE Proc_ErrLogData

          

AS

BEGIN

               

                SET NOCOUNT ON;

 IF EXISTS (SELECT * FROM [dbo].[ErrLogData])

 BEGIN

 TRUNCATE TABLE [dbo].[ErrLogData]

 END

 DECLARE @SQLCmd VARCHAR(1024)

 SELECT @SQLCmd = 'Insert Into dbo.ErrLogData (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog '

 EXEC (@SQLCmd)

-- Cycle through the ErrLogData table and insert the server's name

DECLARE SrvName_Cursor CURSOR FOR

 SELECT [SQLServerName] FROM [dbo].[ErrLogData] WHERE [SQLServerName] IS NULL

 OPEN SrvName_Cursor

 FETCH NEXT FROM SrvName_Cursor

 WHILE @@FETCH_STATUS = 0

 BEGIN

 UPDATE [dbo].[ErrLogData] SET [SQLServerName] = @@servername

 FETCH NEXT FROM SrvName_Cursor

 END

CLOSE SrvName_Cursor

DEALLOCATE SrvName_Cursor

END

GO

Now execute store procedure

EXEC [dbo].[Proc_ErrLogData]

Now we can analyze the data collected with a query. Modify the WHERE clause to show more or less data.

USE [DBAmantanence]

GO

SELECT [LogID]

 ,[LogDate]

 ,[ProcessInfo]

 ,[LogText]

 ,[SQLServerName]

FROM [dbo].[ErrLogData]

WHERE ([logtext] NOT LIKE '% found 0 errors %'

 AND [logtext] NOT LIKE '%\ERRORLOG%'

 AND [logtext] NOT LIKE '%Attempting to cycle errorlog%'

 AND [logtext] NOT LIKE '%Errorlog has been reinitialized.%'

 AND [logtext] NOT LIKE '%found 0 errors and repaired 0 errors.%'

 AND [logtext] NOT LIKE '%without errors%'

 AND [logtext] NOT LIKE '%This is an informational message%'

 AND [logtext] NOT LIKE '%Setting database option ANSI_WARNINGS%'

 AND [logtext] NOT LIKE '%Error: 15457, Severity: 0, State: 1%'

 AND [logtext] NOT LIKE '%finished without errors%')

ORDER BY [SQLServerName] ,[LogID]

GO

Here is a sample output from the script above.

You can create a scheduled job to automatically populate the table with error logs of all your SQL servers, which helps when you to review an issue that effects other SQL Servers.

I hope this helps in your ability to review the ERROR.LOG file for your SQL servers.