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.
Here are examples of reports from these three servers. Forgive me for the black splotches, better safe than sorry with company information.
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.
Set the default values for the servers.
Set up data sources. Connecting to the master database is fine. I just use Windows security, because I run this manually.
The next step is to create the datasets. Here is what the Report Data panel will look like:
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.
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.
Then design the tablix as so:
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.