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