Monitoring the health of your SQL Servers is a standard part of a DBA’s job. One of the most important parts, to tell you the truth. It’s tempting to think of databases as unchanging. You can check them once and, if they are healthy, your job is done. There is no need to check it again. But, just like people, their health can change over time.

I run my own health check scripts on my SQL Servers every quarter. I am amazed at what I find. I typically spent 2 to 3 weeks going through all my servers and correcting all the issues the script turns up. But what is even more amazing is how often I find problems cropping up again the next time I perform the health check.

One of the more common things I find are untrusted or disabled foreign keys and check constraints. Each quarter, these pop up in my checks and, each quarter, I re-enable them. They always seem to get disabled somewhere down the line. Sometimes it’s an in-house process that disables them. In that case, I can talk to the developers and correct the problem at the source. Sometimes, it’s a third party app that I have no control over.

I also find a lot of issues regarding jobs. One common one is jobs without error notifications. Often times, third party products install SQL jobs and they almost never set up a failure notification. I also find vendor jobs that perform index maintenance. Because I have my own index maintenance routines on my servers, I disable these jobs in favor of my own (after verifying my own will run as frequently as the vendor’s did). Because I don’t always know when new software is installed or upgraded, these issues come up each time I run my health checks, even if I have corrected the issue the last time I ran the check.

Bottom line: your SQL Servers are living things and they change over time. Even if you have them configured and running exactly they way you want them to right now, over time, they will change. Monitor them on a regular basis.

FYI, my health check script is a variation on Brent Ozar Unlimited’s sp_Blitz script. If you don’t have your own script to use, theirs is a great one to start with.