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.
- Create a table
to hold all the data from current ERROR.LOG
- Truncate
table and upload the current ERROR.LOG
- 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.
