developer.* Blogs - SQL Server Job Success, Failure, and Everything In-Between - Comments

SQL Jobs

Steve — Tue, 29 Aug 2006 09:12:58 -0700

Have any of you guys tried sqlSentry? www.sqlsentry.net It sounds exactly like what you are talking about. It gives a color coded caldendar view of all the jobs and DTS packages on the server for successes and failures. It even sends emails on more conditions than I could even think up. We tried it at my job and management even loves the look of it. I hope they make the purchase. This thing will save hours of troubleshooting.

Distribution List

Daniel Read — Wed, 07 Jun 2006 10:15:07 -0700

Hi. Hopefully I can help: what I meant was that we created a distribution list on the Exchange server itself. This is simply an email account that you create on your Exchange server, like for example sql_errors@yourcompany.com. Then configure this address in Exchange to route any messages to however many email addresses you choose.

Managing this centrally in Exchange is preferrable to trying to keep up with all the different jobs and configuration points that have these kinds of notification email addresses. Even if you only have one person who is going to receive the notifications, I would still suggest creating an "alias" email address so that when employees come and go all you have to do is update the Exchange settings.

Hope that helps,
Dan

SQL Server Job Success, Failure, and Everything In-Between

Guest — Wed, 07 Jun 2006 09:05:53 -0700

Do you mind a basic question from a DBA new to Sql Server? I tried to get a job schedule's notifications set to an Outlook distribution list but wasn't able to get it to work. I noticed you said "I try to increase redundancy by using distribution lists for the emails rather than a single email address". Can you tell me what the secret is for creating a distribution list that the job scheduler will recognize as an operator?

Unattended Synergy

Daniel Read — Thu, 15 Dec 2005 09:54:18 -0800

Hi Donna,

Your post inspired me to get to work on a post of my own on this topic that has been brewing in my head for awhile now. Coincidentally a major responsibility of mine in my day job for the past few months has been the daily monitoring and upkeep of a somewhat complex and loosely connected hive of download/import/export/maintenance processes clustered around a set of operational databases. The system is a loosely connected (I wish I could say loosely coupled) mixture of scripts, DTS packages, SQL Server 2000 databases, .NET programs, plus the "SQL Agent" scheduler and a standalone job scheduling tool called Automate. I feel all of the pains you describe in your post on a daily basis. The people who put this system together clearly did not build it with the qualities I describe in my post in mind.

I'm also in the process of designing and building a major rewrite for this system, and we're centering most of it on SQL Server 2005's new SSIS (SQL Server Integration Services) facility, which is a wholesale replacement for the longstanding good-but-problematic-and-fragile SQL Server ETL tool, DTS (Data Transformation Services). So far I am pretty impressed with SSIS. Microsoft went back to the drawing board and conceived a true enterprise-class ETL framework. The design of SSIS overcomes many shortcomings of DTS. To learn more about it, I suggest checking out this ProjectREAL thing that Microsoft has set up. I found this white paper about a large-scale ETL process they built for Barnes & Noble's BI to be extremely interesting. Of particular interest to the current discussion, it discusses their logging and exception handling framework in detail. For more on SQL Server 2005, I highly recommend this SAMS book by "Salability Experts, Inc." called Changing the Paradigm. It will bring you up to speed on SQL 2005 quickly.

Even with the out-of-the-box benefits of SSIS, we still plan to build the kind of "dashboard" capabilities you describe, with the specific goal of allowing non-technical operators to be able to judge the health of the system. This will involve designing logging and tracking tables that the dashboard can query for status information. As you suggest, this gives a much better view of the system, and a much more effective management tool, than success and failure emails. However, building this kind of thing takes time and resources, and often these kinds of processes are built on a tight budget. In my current case, in which the client is willing to pay for this "dashboard" capability, they have been living with and feeling the pain of their current non-independent, non-transparent, and non-monitorable system for awhile now.

For what it's worth, in cases where we are stuck with emails for monitoring, I try to increase redundancy by using distribution lists for the emails rather than a single email address.

Best,
Dan

SQL Server Job Success, Failure, and Everything In-Between

Donna L Davis — Wed, 14 Dec 2005 09:13:08 -0800

If your scheduled SQL Server job fails at 2:00AM when no one's checking, will anyone know, or is it like a tree falling silently in a forest?