Microsoft SQL Server Error Log Scanning

SQL Server has an abundance of information in the ERROR.LOG file, from restart information to errors that are logged. Reviewing the ERROR.LOG can be a long and time consuming task especially 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 to help.

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

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DVW9naE9BZFZQNkE/edit?usp=sharing

This article will walk you through these steps.

First we need to create a database and a table to hold the error log data

-- Create a database
USE [master]
GOCREATE DATABASE [ErrorLogStorage] ON PRIMARY ( NAME = N'SQLSystemsSupport', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON ( NAME = N'SQLSystemsSupport_log', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport_log.ldf' , SIZE = 64128KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- Create a table
USE [ErrorLogStorage]
GOCREATE 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

Next we will clear the table and insert the ERROR.LOG data into the table. The script below will also add the SQL Server's name to the table. This is done so that you can have data from many SQL Servers inserted into the same table.

USE [ErrorLogStorage]
GO
-- Remove older data 
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
 BEGIN
 TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData] 
 END
 DECLARE @SQLCmd VARCHAR(1024)
 SELECT @SQLCmd = 'Insert Into [ErrorLogStorage].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 [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL
 OPEN SrvName_Cursor
 FETCH NEXT FROM SrvName_Cursor
 WHILE @@FETCH_STATUS = 0
 BEGIN
 UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername
 FETCH NEXT FROM SrvName_Cursor
 END
CLOSE SrvName_CursorDEALLOCATE SrvName_Cursor
GO

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

SELECT [LogID]
 ,[LogDate]
 ,[ProcessInfo]
 ,[LogText]
 ,[SQLServerName]
FROM [ErrorLogStorage].[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

The line '%Error: 15457, Severity: 0, State: 1%' does not actually indicate an error but is more informational, which is why I do not show this data.

Here is a sample output from the script above.

25021 2010-09-01 12:01:23' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25022 2010-09-01 12:01:25' spid12 REVOKE ALL ON [sp_dump_dtstasklog] FROM PUBLIC DEVSRV1
25023 2010-09-01 12:01:26' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25024 2010-09-01 12:01:28' Logon Error: 18401, Severity: 14, State: 1. DEVSRV1
25025 2010-09-01 12:01:29' Logon Login failed for user 'DBATester'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.10.6.215] DEVSRV1

There are many ways to look for specific information in the error log and you can customize the script to show specific information. Personally, I have created a scheduled job to automatically populate the table with error logs of all my SQL servers, which helps when I need to review an issue that effects other SQL Servers. With the error information in a table you can also create a report in SSRS to show this data in a nicer format.

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

Thanks,
Rudy