Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 14932 | Views in the last 30 days: 13
 
Related Articles
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones