Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

In SQL server instance(s) I manage; I see a lot of activity and messages in SQL server error log for e.g. backup or transaction log backup completed etc.. We have quite a few databases in one instance and amount of error log messages is sometimes quite a few to dig into.

As an accidental DBA, What approach or script can I use to filter the error log for date range specified or finding or filtering troubling errors out of lots of informational messags?

I meant to ask, is there is a T-SQL script or DMVs you can use to filter the error log?

share|improve this question

migrated from stackoverflow.com Jan 8 '12 at 15:59

4 Answers

up vote 3 down vote accepted

You should use the system sp xp_ReadErrorLog to read the SQL Server error log. You can find some details in the following articles: Using xp_ReadErrorLog in SQL Server 2005 and Reading the SQL Server log files using TSQL.

share|improve this answer
thanks, I will look into it however that XP still not so flexible... I also found blog.waldenl.com/2010/04/… – SQL Learner Jan 8 '12 at 16:23
1  
Yeah, I know that. That's why it's important to cycle the log file every day (or week..or whatever you feel comfortable on your server) and see the log each day. In the end the log file is just some kind of text file, so you can even read it using php and send emails.. The XP is nice 'cause you can integrate it in SQL Agent jobs that will fire every hour and will send emails in case some specific words will be read (like I/O, error..etc). – Marian Jan 8 '12 at 16:28
up vote 6 down vote

I suggest that you circumvent reading the error log directly and instead implement SQL Agent Alerting. Under this methodology, you'd simply create alerts for, say, when SQL Server throws an error (I recommend separate alerts for severity levels 19 -25) and tell SQL Server to email you when the error occurs.

You can then generally ignore error log except for specific troubleshooting situations.

General details are here. The details for setting up SQL Agent alerts specifically using SSMS are on TechNet are here and are the same in Books On-Line here.

Hope this helps, -Kevin

@kekline

share|improve this answer
up vote 2 down vote

You turn off the successful backup error log messages with Trace Flag 3226. Also the SQL Server Agent allows you to setup alerts based on either error number or severity, these can automatically email you when an issue occurs.

As you mentioned being an Accidental DBA there is a free eBook available which will help explain a lot of the issues you will face supporting SQL Server.

share|improve this answer
up vote 1 down vote

You can read the error log using xp_readerrorlog:

exec xp_readerrorlog 0, 1, 'search1', 'search2'

The first parameter is the error log number, use 0 for "errorlog", 1 for "errorlog.1", etc.

Second parameter is 1 for SQL Server and 2 for SQL Agent.

The optional third and fourth parameters contain strings to search for.

share|improve this answer
1  
It's XP_ReadErrorLog, as in eXtended Proc :). – Marian Jan 8 '12 at 16:11
Edited... we prefix sp_ at work so my fingers type that without thinking :) – Andomar Jan 8 '12 at 17:00
I see, so maybe it's time you stop doing that ;). There's some evidence in just using the procedures name, not adding the sp_ prefix. Give a reading to the following articles: article1, article2, article3. Hope it's useful! – Marian Jan 8 '12 at 23:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.