What are the best alerts to set up on a SQL server?

Question

What are the best alerts to set up on a SQL server?

Duran

SSChampion

Points: 13259
More actions
April 24, 2012 at 8:16 am

#254639

Hello everyone,
I would like to set up some alerts on my SQL server 2008 machine, and I was wondering if there were any specific ones that would be good to set up. Ive had a look on the web but I was wondering if there were any real world ones to look out for. I'd imagine there are a few standards, and perhaps a few that are not so well known. Be great to hear from anyone on this, and any opinions.
Thank you for reading,
Regards,
D.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Duran (4/24/2012)
Hello everyone,
I would like to set up some alerts on my SQL server 2008 machine, and I was wondering if there were any specific ones that would be good to set up. Ive had a look on the web but I was wondering if there were any real world ones to look out for. I'd imagine there are a few standards, and perhaps a few that are not so well known. Be great to hear from anyone on this, and any opinions.
Thank you for reading,
Regards,
D.

Well... Start with alerting for failed jobs. Especially the backups 🙂 You can also scan the error log and send emails every 5 or 10 minutes, depending on your business requirements.

Jared
CE - Microsoft

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 2

I would also suggest creating alerts for all fatal errors so you can be notified immediately.

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

All errors of severity 19 and higher and error number 825

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jared SSC Guru Points: 61793 More actions · Answer 4

GilaMonster (4/24/2012)
All errors of severity 19 and higher and error number 825

I assume that since you said 19 and higher, but then specifically 825 that it is not included for some reason? Why is this one special from the others?

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

SQLKnowItAll (4/24/2012)
GilaMonster (4/24/2012)
All errors of severity 19 and higher and error number 825
I assume that since you said 19 and higher, but then specifically 825 that it is not included for some reason? Why is this one special from the others?

Is google down? 😉

It's listed separately, because it it not severity 19 or above, it's a severity 10.

http://sqlinthewild.co.za/index.php/2008/12/06/when-is-a-critical-io-error-not-a-critical-io-error/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thomas Stringer SSCarpal Tunnel Points: 4038 More actions · Answer 6

It's severity 10. Here's a great blog from Paul Randal why this could be worse than it sounds: http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx

Twitter: @SQLife
Email: sqlsalt(at)outlook(dot)com

Jared SSC Guru Points: 61793 More actions · Answer 7

GilaMonster (4/24/2012)
SQLKnowItAll (4/24/2012)
GilaMonster (4/24/2012)
All errors of severity 19 and higher and error number 825
I assume that since you said 19 and higher, but then specifically 825 that it is not included for some reason? Why is this one special from the others?
Is google down? 😉
It's listed separately, because it it not severity 19 or above, it's a severity 10.
http://sqlinthewild.co.za/index.php/2008/12/06/when-is-a-critical-io-error-not-a-critical-io-error/

Ah ha... Should have realized one was a severity level and one was an error number. Sorry, still too early. I also suppose I am getting too dependent on the forums here vs google.

Jared
CE - Microsoft

Geoff A SSChampion Points: 11511 More actions · Answer 8

SQLKnowItAll (4/24/2012)
Duran (4/24/2012)
Hello everyone,
I would like to set up some alerts on my SQL server 2008 machine, and I was wondering if there were any specific ones that would be good to set up. Ive had a look on the web but I was wondering if there were any real world ones to look out for. I'd imagine there are a few standards, and perhaps a few that are not so well known. Be great to hear from anyone on this, and any opinions.
Thank you for reading,
Regards,
D.
Well... Start with alerting for failed jobs. Especially the backups 🙂 You can also scan the error log and send emails every 5 or 10 minutes, depending on your business requirements.

do you find that scanning the SQL logs every 5 minutes is resource intensive or do you rotate them often?

Jared SSC Guru Points: 61793 More actions · Answer 9

Geoff A (4/24/2012)
SQLKnowItAll (4/24/2012)
Duran (4/24/2012)
Hello everyone,
I would like to set up some alerts on my SQL server 2008 machine, and I was wondering if there were any specific ones that would be good to set up. Ive had a look on the web but I was wondering if there were any real world ones to look out for. I'd imagine there are a few standards, and perhaps a few that are not so well known. Be great to hear from anyone on this, and any opinions.
Thank you for reading,
Regards,
D.
Well... Start with alerting for failed jobs. Especially the backups 🙂 You can also scan the error log and send emails every 5 or 10 minutes, depending on your business requirements.
do you find that scanning the SQL logs every 5 minutes is resource intensive or do you rotate them often?

We scan every 5 minutes on each of our servers. I did not write the scanlog scripts, but implement them on every new server.

Jared
CE - Microsoft

Geoff A SSChampion Points: 11511 More actions · Answer 10

if you have a high use server with multiple databases in full backup mode with trans logs getting backed up alot, and you do not rotate the logs often, reading the SQL log can be tough on a system. that log does not rotate on its own. only on reboots, restarts and running the following command.

EXEC sp_cycle_errorlog ;

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 11

Geoff A (4/24/2012)
if you have a high use server with multiple databases in full backup mode with trans logs getting backed up alot, and you do not rotate the logs often, reading the SQL log can be tough on a system. that log does not rotate on its own. only on reboots, restarts and running the following command.
EXEC sp_cycle_errorlog ;

Have you seen Trace Flag 3226? If you do not have much need for the log backup entry it can keep your logs tidy.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Geoff A SSChampion Points: 11511 More actions · Answer 12

opc.three (4/24/2012)
Geoff A (4/24/2012)
if you have a high use server with multiple databases in full backup mode with trans logs getting backed up alot, and you do not rotate the logs often, reading the SQL log can be tough on a system. that log does not rotate on its own. only on reboots, restarts and running the following command.
EXEC sp_cycle_errorlog ;
Have you seen Trace Flag 3226? If you do not have much need for the log backup entry it can keep your logs tidy.

i have not and i thank you!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Geoff A (4/24/2012)
if you have a high use server with multiple databases in full backup mode with trans logs getting backed up alot, and you do not rotate the logs often, reading the SQL log can be tough on a system. that log does not rotate on its own. only on reboots, restarts and running the following command.
EXEC sp_cycle_errorlog ;

Add to that if you are logging failed and successful logins.

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 14

I alert for sev 17 or higher, plus a handful of specific numbers including 825.

Database Engine Error Severities

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato