• How much "checking" I build into my custom systems depends on time and money available to do them. I haven't taken the SQL Agent route, typically I have a Windows service that periodically processes various multi-step business transactions. As part of the cycle of jobs-to-do, I usually place my "checks" code at the end of each processing cycle. My service always uses stored procedures and I usually build a few that will cull my data looking for issues that need to be brought to someone's attention. When something is found, I send off an email to the appropriate personnel (I usually maintain in the database a list of emails tied to types of activities).

    Transaction Status

    I should also note that I create transaction tables that stores the various transactions within my system. As part of these tables I determine the main steps to complete the transaction and create a status date/time column for each step as part of the same transaction row definition. These columns are defaulted to NULL. I also create a Status Notes column that can contain textual status info. This defaults to "Waiting to be processed". When the process completes a step, it runs a sProc that applies the current date/time to the appropriate status date column and updates the Status Notes column with the results of that step. If a process encounters an error, part of the error handling applies the error info in the Status Notes.

    Notifications based upon type of activity

    Sometimes the issue is support-related and I'll send it to system support folks. Typically these are business transactions that could not complete and need some human eyes to help it along. Having the Status Notes column greatly assists these folks when they analyze the transaction tables.

    Sometimes the issue is business-related, such as manufacturing jobs running late and I need to notify the appropriate business unit manager.

    Use of SQL Agent

    Many of my clients would not allow their SQL Servers to send email as part of their internal security policies. Some even go so far as to disable SQL Agent. I have to create systems within the confines given to me and if it's a 4' x 6' jail cell, so be it.