SQL Server Alerts - Best Practices

Question

What SQL Server Alerts do you always setup for every database? What do you always monitor regardless of the database?

Cocotte

Answer 1

You should monitor and be alerted for severity levels 17 to 25.

Severity levels from 17 through 19 will require intervention from a DBA, they're not as serious as 20-25 but the DBA needs to be alerted.
17 Insufficient Resources
18 Nonfatal Internal Error Detected
19 Error in Resource


These are serious errors that will mean SQL Server is no longer working
20 SQL Error in Current Process
21 SQL Fatal Error in Database dbid Processes
22 SQL Fatal Error Table Integrity Suspect
23 SQL Fatal Error: Database Integrity Suspect
24,25 Hardware Error

for more information on the severity levels see http://msdn.microsoft.com/en-us/library/aa937483(SQL.80).aspx

Roadwork

I also set up 9002 on tempdb. – Linette

Answer 2

I would also add alerts on Error 823, 824 and 832, since these errors indicate corruption.

For more information see http://www.sqlservercentral.com/articles/Memory+Corruption/93424/ and http://www.sqlskills.com/BLOGS/PAUL/post/Dont-confuse-error-823-and-error-832.aspx

Hesperidium

Answer 3

What do you always monitor regardless of the database?

We always turn on hardware alerts for all of our servers, in addition to log alerts. Hardware errors, such as inode errors, can take down servers just as fast as 5xx errors, for example. We've seen customers' PDF export capabilities fail when code on a server failed to delete old exports, filling up the disk space until exports failed altogether. Regular log alerts won't warn you of these things until it's too late. But monitoring the disk space would have.

Unfortunately, log management solutions don't set these alerts up for you automatically, so sometimes you find out you needed the alerts the hard way: when you've already got a problem.

We wrote a blog post about why it's important to pair hardware metric alerting with standard log alerts: https://blog.bluematador.com/posts/how-essential-alerts-could-have-saved-the-millennium-falcon/

Hasten