Monitor MS SQL server health for non-DBA - SQL monitoring tools?


I have a couple of SQL servers I need to take care of. I'm no DBA, but have done well doing the basics for many years.

One thing that does seem to catch me is that if there is a problem with a database, I usually don't figure it out until someone tells me there is something wrong. Often by that time there has been something wrong for a while so restoring from a backup isn't a great option.

While I try to be religious about looking through logs, it's tough sometimes to pick the problem through all the noise.

What are some good techniques and easy to use (hopefully) tools that will do a reasonable job of letting me know when there is a real problem with something, without me having to scour logs and sort through tons of irrelevant notifications?

Who is Participating?

Improve company productivity with a Business Account.Sign Up

Racim BOUDJAKDJIConnect With a Mentor Database Architect - Dba - Data ScientistCommented:
Use a monitoring tool.  My preference goes for Redgate SQL Monitor.
Paul MacDonaldDirector, Information SystemsCommented:
Depends on what kind of problems you need to trap.
willp2Author Commented:
In this case, specifically problems with the databases themselves. So backups or maintenance not completing correctly because of problems and to a lesser extent machine problems, like problems with Windows or the VM hardware its running on.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Paul MacDonaldDirector, Information SystemsCommented:
"... specifically problems with the databases themselves."
What problems specifically?  How are they discovered?

Are we talking problems with indexes?  Data corruption?  Backups not completing?  

You can schedule some routine maintenance under Management -> Maintenance Plans, but there's no way to know if that will help since we don't know what the problem is.
willp2Author Commented:
Sorry for being vague as I'm not talking about something specific exactly.

We do run maintenance and backups. In the most recent case of a database issues, maintenance started kicking errors after a server reboot followed later by problems backing up the database. In retrospect it was easy to spot, but with all the info emails I get about various things, it got lost in the mix.

I guess I'm hoping to get something in place that basically says "Hey Dumb Ass, this is important!) without telling me that everything is important.

So I was hoping there was some reasonable SQL monitoring tool that will look at things like maintenance, backups and overall health and let me know if it looks like something is out of whack.
Paul MacDonaldDirector, Information SystemsCommented:
You can have the maintenance plan notify you if it fails at any step.  You can also look at the Maintenance Logs to see if maintenance is having problems.  Lastly, if your databases backup, they should be okay, so check those periodically.

The tools listed by [lcohan] may be of some use, but I've never used them so don't know.
lcohanDatabase AnalystCommented:
For SQL Jobs only you could use "Operations Manager"

If you do not have too many servers to manage you could use undocumented proc SP_GET_COMPOSITE_JOB_INFO like in example below:
I'm going to +1 lcohan's suggestion about using SCOM (the artist formerly known as MOM).  

I will just add one warning: if you are wanting to get tuning information out of the monitoring solution, MOM (I refuse to call it SCOM) doesn't have much right out of the gate.  Even with the SQL Server management pack added, there is still a fair bit of work that you must do to bring her up to date with SQL monitoring.  Most people create a custom management pack to pick up a lot of the metrics that MOM is missing.  For example, a lot of metrics should really be compared as a function of batches/sec, which doesn't exist in MOM's SQL Server Management Pack - goofy, I know.

The stuff that it does have, as mentioned above, is the SQL Server agent job monitors, as well as some other straightforward ones - % space used within the data/log files, memory/cpu used by sql server instance(s), etc.  It has those and it you can lay it all out in a pretty dash too.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.