• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

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

Hello,

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?

Thanks
0
willp2
Asked:
willp2
  • 3
  • 3
  • 2
  • +2
2 Solutions
 
Paul MacDonaldDirector, Information SystemsCommented:
Depends on what kind of problems you need to trap.
0
 
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.
0
SMB Security Just Got a Layer Stronger

WatchGuard acquires Percipient Networks to extend protection to the DNS layer, further increasing the value of Total Security Suite.  Learn more about what this means for you and how you can improve your security with WatchGuard today!

 
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.
0
 
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.
0
 
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.
0
 
lcohanDatabase AnalystCommented:
For SQL Jobs only you could use "Operations Manager"
http://blogs.technet.com/b/kevinholman/archive/2011/08/05/how-to-monitor-sql-agent-jobs-using-the-sql-management-pack-and-opsmgr.aspx


If you do not have too many servers to manage you could use undocumented proc SP_GET_COMPOSITE_JOB_INFO like in example below:

http://blog.sqltechie.com/2009/03/job-monitoring-part-1.html
0
 
didnthaveanameCommented:
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.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Use a monitoring tool.  My preference goes for Redgate SQL Monitor.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now