Read Your Error Log’s T-SQL Tuesday #025

Posted: December 11, 2011 in SQLServerPedia Syndication, T-SQL Tuesday

I really try to take part in as many T-SQL Tuesdays as I can, but to be honest I just forget about it.  So as I try to figure out how to remind myself I ponder what my favorite tip is.  I think this is a great Question asked by Allen White.  If you don’t know about the whole T-SQL Tuesday thing you can either follow the Logo or check it out on twitter using the hash tag #TSQL2sday. Allen had mentioned that in the early days of SQL Server that some of the best tips and tricks for managing SQL Server came from the community.  The key here is that you need to make sure that you were taking part in the community to get all that you could from the tips people had to share.  

What Is My Tip?

I think it should go without saying that you need to be checking your error logs on all your SQL Servers every day. If you fail to do this then you are just waiting for your servers to fail on you.  With the ever growing popularity of SQL Server (I know this surprises you), there are so many of these servers that we need to manage.  This means that each day you should be opening the error log on each of the SQL Servers that you manage.  But what happens when you have 10 or even 100 even more?  Obviously the time involved becomes that much more intrusive.  Try telling your supervisor that you could not get any work on your projects done because you were reviewing error logs, see how far that gets you.  So how do you make this easier?  How can you complete this action without taking all your day reading logs?

Use the extended stored procedure that Microsoft provides you to help you automate your task. 

EXEC sp_executesql N’xp_readerrorlog’

Now you can see that you have your error log in a result set that you can manage.  I will be going much more in depth with this during in my Utility Database Session SQLSaturday #104 Pre-Conference What’s in your Utility Belt. Learn how you can use this information to help you even more.  Considering putting your record set into a table, this can be done by:

DECLARE @Errorlog TABLE (LogDate datetime, ProcessorInfo VARCHAR (100),ErrorMSG VARCHAR(2000))

INSERT INTO @Errorlog

EXEC sp_executesql N’xp_readerrorlog’

Once you have the data in a table I think that this is where it is the most powerful. You can filter the data and remove the rows that you don’t want to see, you can look for keywords and take action on issues that you see in your error log.  If you add the code to a SQL Server job, you can automate these items.  My favorite part is that I can use SQL Mail to send me results or I can format it in a SSRS report with a subscription.

End result, I use the methods that I am most familiar with, when the data is in a table. I know how to move through it, act on it, report on it and make sure that each of the issues have been addressed.

 

 

About these ads
Comments
  1. Greg Lucas says:

    Chris, it may not be Tuesday but I like this tip. Nice and simple but very useful. It would simple to runs this as a job and set up alerts based on what’s found in the results. Thanks.

  2. [...] Read Your Error Log’s T-SQL Tuesday #025 I really try to take part in as many T-SQL Tuesdays as I can, but to be honest I just forget about it.  So as I try to figure out how to remind myself I ponder what my favorite tip is.  I think t… Source: chrisshaw.wordpress.com [...]

  3. I was just looking into easier ways to read log files. Thanks for the tip

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