Often the things I post about are the items that I am using that day, or recently. Many of the topics, are simply based on how I may have made using SQL Server a bit easier for me to use one way or another. In another SQL Server Error Log post I had mentioned a few of the basic things I do when configuring a server to make the Error Log a little easier for me to navigate, or get the information I need quicker. It occurred to me after a question from a well-respected community member that an example on how I use xp_readerrorlog, could add to the usefulness of the post. Thanks for the suggestion.
I am fond of basing all my work on the negative, the term working with the negative for me means working on the things that I am not expecting. For example, I expect that every hour my transaction logs are going to be backed up, I don’t need an alert or an email each time that the transaction log completes without any issues. I do however want the alarms blasting every time they don’t execute as planned. I apply the same logic to my error logs, however even if I could remove entries from my error logs I always want them as they are. In order for me to have the best of both worlds, I like to take all the entries that I am not expecting and store them in a table so I can filter them with a query, join them to other tables to get other relevant events that were occurring at that time and perform other tasks that may be a bit difficult with the error logs as they stand.
Where to start?
We need to create a working table and when I have the opportunity I like to create a database I call dbUtilities. After that database has been created, or you have picked another location to store your table, start with a table that is similar to this one. I create this table so I can store my information long term. You may want to add some additional fields such as a PK.
On a quick side note, you should customize this code to meet your needs; the posted version here is in the simplest form. Make sure you look at the added notes at the bottom of this post before executing this script on your servers.
Create Table MyErrorlog (LogDate datetime, ProcessorInfo VARCHAR (100),ErrorMSG VARCHAR(2000))
Once I have my final destination created, I want to insert all my errors that exist in the error log into a temp table where I can start the filtering process.
DECLARE @Errorlog TABLE (LogDate datetime, ProcessorInfo VARCHAR (100),ErrorMSG VARCHAR(2000))
INSERT INTO @Errorlog
EXEC sp_executesql N’xp_readerrorlog’
From this point I start to remove the entries that I expect to see, for each server this is on, the filtering could be different. I leave each of the delete statements as an individual statement, for ease of understanding and customizing. If performance is of any concern it would be better to combine the statements.
Delete
FROM
@Errorlog
WHERE
ErrorMSG
LIKE
‘%Log was backed up%’;
Delete
FROM
@Errorlog
WHERE
ErrorMSG
LIKE
‘%Setting database option COMPATIBILITY_LEVEL%’;
Delete
FROM
@Errorlog
WHERE
ErrorMSG
LIKE
‘%were backed up%’;
Delete
FROM
@Errorlog
WHERE
ErrorMSG
LIKE
‘%DBCC TRACEON%’;
Delete
FROM
@Errorlog
WHERE
ErrorMSG
LIKE
‘%without errors%’;
Once the filtering is done I insert these rows into a final table. (I create this all as one stored procedure, and execute it once a day. I am really only concerned with the last days’ worth of information.)
INSERT INTO MyErrorlogSELECT Logdate, ‘Error Log’, SUBSTRING(ErrorMSG, 1, 2000)FROM @ErrorlogWHERE LogDate > DATEADD(dd, -1, GETDATE())
Some additional notes:
- Consider cycling your error log to keep it small and manageable. This can be key when executing xp_readerrorlog. Large logs could impact the performance on your system, and the way I use the code I am expecting a smaller log.
- In my case, I normally complete an additional step where I insert the rows into a table that I truncate and rebuild each day. The purpose of this table is so I can use it as a base for a reporting services report and pull information from that table and then subscribe to the report. This way I can other items such as failed jobs, replication status, and job executions that have failed to name a few. This is why I use the ‘ProcessorInfo’ column in the tables. It gives me the space to identify the source of the data in my final table.
Don’t forget to have an archive plan for your old data. I am a pack rat, and hate the idea of getting rid of info, I often get values from trends. So I will copy the data on occasion and store it somewhere offline if I have to.