Category: sp_LogHunter

Introducing sp_LogHunter: Quickly Search For Important Error Log Messages From SQL Server

Posted on by Erik Darling

Sick Of It All

SQL Server has incredibly verbose error logs, and sifting through them for the highlight reel can be a pain. They’re often full of messages that you aren’t actionable.

  • Failed logins
  • Successful logins
  • Successful backups

Making matters worse is that when you open large log files, the response time for loading all those messages can be painful, and even time out.

Then you have the nice folks who create a new log file every day and keep months of log files around.

But probably the worst thing is that you can’t really search for multiple things without losing all of the other messages.

If you’re like me, and you need to know when interesting or critical events happened, you’re going to love sp_LogHunter.

Right now, it has these parameters:

@days_back int = -7 /*How many days back you want to look in the error logs*/
@custom_message nvarchar(4000) = NULL /*If there's something you specifically want to search for*/
@custom_message_only bit = 0 /*If you only want to search for this specific thing*/
@language_id int = 1033 /*If you want to use a language other than English*/
@first_log_only bit = 0 /*If you only want to search the first log file*/

Along with the usual version, help, and debug parameters I add in to my procedures.

A sample execution looks something like this:

EXEC dbo.sp_LogHunter
    @days_back = -30,
    @custom_message = N'he''s dead, jim';

I’ll have a video walkthrough tomorrow to talk about the code and show some results in action.

If you run into any issues or would like to see any enhancements to the procedure, head over to GitHub to open an issue.

You get sp_LogHunter and raise any issues here.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.