Easy error Log checking

At first at my current job as a DBA, I was not checking SQL Server error logs every day.   Then I started checking production daily to crack down on deadlock issues.  But I was not checking all of the other miscellaneous servers.  As you know, it’s very inconvenient to connect SSMS to each server and open the error logs.  Then there is sometimes a lot of useless information to sift through, like “IO has been frozen on database X”, and “IO has resumed on database X”.

My solution was to use SSRS to check all of the server error logs,  and to filter out the records.   I’m sure there have been other solutions out there that may be better, but I wanted to make my own.

I’ll start with the completed product….so you know whether this is something you want to keep reading or not!

When you hit preview, the report is controlled by the Database Server drop down.   The report will show the error log for whichever server you choose.

Image

Here are examples of reports from these three servers.  Forgive me for the black splotches, better safe than sorry with company information.

Image

Image

Image

Ok, so are you still interested in doing this?

Here are some steps to do it.  It’s assumed that you know how to open SSRS, and do a few things.  This example has three servers named SERVER01, SERVER02, and SERVER03.  You can substitute with your actual server names.

First create a Parameter named @DBServer of type text.

Image

Set the default values for the servers.

Image

Set up data sources.  Connecting to the master database is fine.  I just use Windows security, because I run this manually.

Image

The next step is to create the datasets.  Here is what the Report Data panel will look like:

Image

Inside of the data sets, you’ll have this query.

IF @DBServer = 'Server01'

BEGIN

DECLARE @wErrorLog TABLE
(
LogDate DATETIME ,
ProcessInfo NVARCHAR(50) ,
ErrorText NVARCHAR(MAX)
);

INSERT INTO @wErrorLog
exec sp_readerrorlog 0

INSERT INTO @wErrorLog
exec sp_readerrorlog 1

DELETE @wErrorLog WHERE ErrorText like '%0 errors%'
DELETE @wErrorLog WHERE ErrorText like '%I/O is frozen%'
DELETE @wErrorLog WHERE ErrorText like '%I/O was resumed%'
DELETE @wErrorLog WHERE ErrorText like 'This instance of SQL%'
DELETE @wErrorLog WHERE ErrorText like 'DBCC TRACE%'
DELETE @wErrorLog WHERE ErrorText like 'SQL TRACE%dgras%'
DELETE @wErrorLog WHERE ErrorText like '%informational message only%'
DELETE @wErrorLog WHERE ErrorText like 'The error log has been%'
DELETE @wErrorLog WHERE ErrorText like 'Logging SQL Server messages in file%'
DELETE @wErrorLog WHERE ErrorText like '%Database backed up%'
DELETE @wErrorLog WHERE ErrorText like 'Log was backed up%'

SELECT  *
FROM @wErrorLog
WHERE LogDate >= DATEADD(DAY, -2, GETDATE() )
ORDER BY LogDate DESC

END

ELSE
BEGIN

SELECT
'2013-1-1' AS LogDate,
'' as ProcessInfo,
'' as ErrorText

END
  • The IF statement is so that the intensive query only runs when you pick a that server in the drop down.
  • The ELSE statement returns a dummy record, for when you don’t pick this server in the drop down.
  • The query loads the two most recent error log files, as indicated by exec sp_readerrorlog 0 and exec sp_readerrorlog 1
  • The query filters out common messages that are not important to investigate
  • It returns the errors from the past two days, just so I’m not reading the same messages a week later.

These screen shots may be helpful in setting up the data sets.  You need to hook up the parameter as shown in the 2nd screenshot.

Query screen:

Image

Parameter screen

Image

Once you’ve set up your datasets, if you’re still with me, then finally design your report.

Make a title, then drag a Tablix from the toolset to the design pane.

Point the Server01 tablix to the Server01 data source.

Image

Then design the tablix as so:

Image

Hit preview, and see what happens.  Let me know how it goes.

Downsides may be that you need proper security to access every server, but don’t have it due to firewalls or your server security. That is why I run it within VS right now.  If I deploy it to a report server, it doesn’t have access to everything.

Upside is that I can quickly check error logs for every server, without connecting SSMS to every server.  Also, it filters out unimportant messages.

Advertisements
This entry was posted in Business Intelligence, Database Administration and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s