Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-06-04
11
Medium Priority
?
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
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 40

Expert Comment

by:lcohan
ID: 39219934
0
Ready for your healthcare security check-up?

In the past few years, healthcare organizations have become a prime target for advanced attacks. Does your organization have what it needs to defend itself? Schedule your healthcare security check-up today and download our free Healthcare Security Resource Kit today!

 
LVL 34

Expert Comment

by:Paul MacDonald
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
 
LVL 34

Expert Comment

by:Paul MacDonald
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 40

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 40

Assisted Solution

by:lcohan
lcohan earned 1000 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 1000 total points
ID: 39220416
Use a monitoring tool.  My preference goes for Redgate SQL Monitor.
0

Featured Post

Looking for the Wi-Fi vendor that's right for you?

We know how difficult it can be to evaluate Wi-Fi vendors, so we created this helpful Wi-Fi Buyer's Guide to help you find the Wi-Fi vendor that's right for your business! Download the guide and get started on our checklist today!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

OnPage enhanced its integration with ConnectWise Manage to offer incident responders more control over the ticket and Incident Resolution Lifecycle.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

610 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