Solved

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

Posted on 2013-06-04
11
623 Views
Last Modified: 2014-08-02
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
Comment
Question by:willp2
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 33

Expert Comment

by:paulmacd
ID: 39219920
Depends on what kind of problems you need to trap.
0
 
LVL 1

Author Comment

by:willp2
ID: 39219930
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
 
LVL 39

Expert Comment

by:lcohan
ID: 39219934
0
 
LVL 33

Expert Comment

by:paulmacd
ID: 39219961
"... 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
 
LVL 1

Author Comment

by:willp2
ID: 39220034
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:paulmacd
ID: 39220064
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
 
LVL 39

Expert Comment

by:lcohan
ID: 39220157
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
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 250 total points
ID: 39220167
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39220228
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
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 250 total points
ID: 39220416
Use a monitoring tool.  My preference goes for Redgate SQL Monitor.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now