Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Error Log Scanning

By Rudy Panigas, (first published: 2011/02/14)

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

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]
GO
CREATE 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]
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

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_Cursor
DEALLOCATE 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

 
Total article views: 15105 | Views in the last 30 days: 3
 
Related Articles
BLOG

SQL Server Error Log Reader

Reading the SQL Server Error Log is miserable.  It contains very useful information you should addre...

FORUM

Does we should worry about informational message in error logs?

Informational messages in Error Log

FORUM

Sql Server Information

Gathering Server information

ARTICLE

Catching Deadlock Information in SQL Logs

This article presents a handy way to retrieve deadlock information in an ordered way from the error ...

FORUM

Error Serverity

error serverity

Tags
 
Contribute