July 7, 2010 at 9:46 am
I would appreciate if the DBA's on here can share there experience and suggest me with a good alerting system product. Would be also helpfull if links can be provided for licensing cost. Some of my requirements are mentioned below.
i) Should send alert whenever a job fails.
ii) Should send an alert when job runs for more than usual time
iii) Should send an email when a backup doesn't happen for more than one day
iv) IF any blocking is occuring.
v) Special alerts for replication.
Thanks in advance !!
July 7, 2010 at 9:54 am
You should be able to do all of those alerts using SQL server agent alerts.
also for number (IV) are you sure you want an alert on blocking and not dead-locking?
July 7, 2010 at 11:56 am
Pretty much, you can all this with SQL Server. SQL Agent can generate emails when jobs fail and you can generate emails when you get errors. The only trick would be identifying more than a day old backups, but that's pretty easy too. You can just query msdb to get the info and raise an error based on the results. No cost involved.
You might want to look at SQL Server 2008 and the Policy Based Management tool. That can add a huge chunk of behavior into your monitoring and alerting, again, for little to no cost.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2010 at 12:19 pm
thanks. One of the issues what i can forsee by using sql agent is that ,lets say i have 200 jobs then i will need to manually add agent operator to each one of these....
July 7, 2010 at 12:20 pm
As the others have said, this is all built into SQL Server already. And if there's an alert you can't find, but want, you can make your own.
And on jobs, you don't need alerts. You can use the notification tab of each job or build your own T-SQL monitoring tool.
For instance, my workplace has Operator notifications on all our jobs. It pages or emails us (depending on the job) if a job fails. In addition, we created our own little Job Monitor job that sends us a morning update that includes a list of the jobs that failed over the past 24 hours and archives the logs of the failed jobs.
There's a lot you can do with the tools you have without spending additional money. But, if you want to spend that money, you can start by checking out RedGate, Quest Solutions (www.quest.com), and PragmaticWorks (a plug for Microsoft MVP Brian Knight's company) for their monitoring tools. I think PragmaticWorks has a monitoring tool, anyway. I don't know for sure.
July 7, 2010 at 12:36 pm
For a low-end system, not lots of server or lots of requirements for monitoring & alerting, you really can't go wrong with Red Gate SQL Alert. I do still think it's only doing things you can do for yourself, but you can say that about alot of monitoring software.
Another product, that's frankly a little more high-end, but worth looking at is Confio Ignite. I wrote up a few mini-reviews on them over at my blog. Nice software, but it might be over-kill for what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2010 at 12:41 pm
iqtedar (7/7/2010)
thanks. One of the issues what i can forsee by using sql agent is that ,lets say i have 200 jobs then i will need to manually add agent operator to each one of these....
Or you could write a T-SQL loop to do an sp_update_job and add the operator / notification step there.
Just do it once in the GUI, script out the job as a "create", copy that part of the code into your WHILE loop, then run.
July 8, 2010 at 4:19 pm
thanks...i would try and let you guys know..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply