Log and DB backups without writing infomsgs to error log?

I was wondering if anyone knows of a way that you can have your backups not
write info messages to the sql server error log in SQL SERVER 2k5. We have
500 databases per instance of sql server and when we run hourly tran and
nightly full's we end up with 24*500 = 12000 rows of data that I don't need
to see in my error logs every night. As you can see with that nightly
volume of log data it is a nightmare when you have to go searching through
the logs. I've written a script that parses the results of the undocumented
proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
however it's a memory hog when it goes through a week's worth of log data (I
see excessive waits in the mssql_xp type when it runs). I ideally would
like to be able to parse through the logs every 15 minutes or so and alert
back through e-mail to the DBA's when it sees something concerning. Can I
set up db alerts to do this? If not I guess the only other option I have is
writing a standalone external app that can parse the error log and email me
when it sees concerning information. Has anyone else encountered this
situation? Any help/insight would be appreciated.
-AbairThere's a trace flag...
http://sqlblog.com/blogs/andrew_kelly/archive/2007/10/29/successful-backup-messages-no-more.aspx
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Michael Abair" <mabair@.autotask.com> wrote in message
news:OUZx$lKSIHA.3316@.TK2MSFTNGP02.phx.gbl...
>I was wondering if anyone knows of a way that you can have your backups not
>write info messages to the sql server error log in SQL SERVER 2k5. We have
>500 databases per instance of sql server and when we run hourly tran and
>nightly full's we end up with 24*500 = 12000 rows of data that I don't need
>to see in my error logs every night. As you can see with that nightly
>volume of log data it is a nightmare when you have to go searching through
>the logs. I've written a script that parses the results of the
>undocumented proc 'sp_readerrorlog' and e-mails our DBA team any pertinent
>info it finds, however it's a memory hog when it goes through a week's
>worth of log data (I see excessive waits in the mssql_xp type when it
>runs). I ideally would like to be able to parse through the logs every 15
>minutes or so and alert back through e-mail to the DBA's when it sees
>something concerning. Can I set up db alerts to do this? If not I guess
>the only other option I have is writing a standalone external app that can
>parse the error log and email me when it sees concerning information. Has
>anyone else encountered this situation? Any help/insight would be
>appreciated.
> -Abair
>|||It is interesting that I have seen some Oracle DBAs in the past doing just
that: parsing the logs to find errors and send alerts.
In SQL Server that is the job of the SQL Server Agent and you can define
Alerts to do the same job.
Anyway, in my opinion, even with alerts set up, it is always a good idea to
look at the error log once in a while.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Michael Abair" wrote:
> I was wondering if anyone knows of a way that you can have your backups not
> write info messages to the sql server error log in SQL SERVER 2k5. We have
> 500 databases per instance of sql server and when we run hourly tran and
> nightly full's we end up with 24*500 = 12000 rows of data that I don't need
> to see in my error logs every night. As you can see with that nightly
> volume of log data it is a nightmare when you have to go searching through
> the logs. I've written a script that parses the results of the undocumented
> proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
> however it's a memory hog when it goes through a week's worth of log data (I
> see excessive waits in the mssql_xp type when it runs). I ideally would
> like to be able to parse through the logs every 15 minutes or so and alert
> back through e-mail to the DBA's when it sees something concerning. Can I
> set up db alerts to do this? If not I guess the only other option I have is
> writing a standalone external app that can parse the error log and email me
> when it sees concerning information. Has anyone else encountered this
> situation? Any help/insight would be appreciated.
> -Abair
>
>|||> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
This statement seems to validate the 'Oracle DBA' approach. I mean, you
can't possibly just manually 'look at the error log once in a while' unless
you only have one, two, or a very small number of servers. Even that, it's
boring to look at these errorlog files manually. It's much better to let a
program do the borign work for you.
BTW, that is not an Oracle DBA approach, but is a generally effective
practice in my opinion.
Linchi
"Ben Nevarez" wrote:
> It is interesting that I have seen some Oracle DBAs in the past doing just
> that: parsing the logs to find errors and send alerts.
> In SQL Server that is the job of the SQL Server Agent and you can define
> Alerts to do the same job.
> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Michael Abair" wrote:
> > I was wondering if anyone knows of a way that you can have your backups not
> > write info messages to the sql server error log in SQL SERVER 2k5. We have
> > 500 databases per instance of sql server and when we run hourly tran and
> > nightly full's we end up with 24*500 = 12000 rows of data that I don't need
> > to see in my error logs every night. As you can see with that nightly
> > volume of log data it is a nightmare when you have to go searching through
> > the logs. I've written a script that parses the results of the undocumented
> > proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
> > however it's a memory hog when it goes through a week's worth of log data (I
> > see excessive waits in the mssql_xp type when it runs). I ideally would
> > like to be able to parse through the logs every 15 minutes or so and alert
> > back through e-mail to the DBA's when it sees something concerning. Can I
> > set up db alerts to do this? If not I guess the only other option I have is
> > writing a standalone external app that can parse the error log and email me
> > when it sees concerning information. Has anyone else encountered this
> > situation? Any help/insight would be appreciated.
> >
> > -Abair
> >
> >
> >