Managing Jobs Using TSQL

Having he honor of working for quite a few companies that did not have the
resources to buy any of the nice SQL Server toys that exist out there or were
willing to put an email client on the servers, I have found myself spending a
good deal of time each morning checking the status of the numerous jobs running
on my servers. Not a hard thing to accomplish, but very time consuming when you
are talking about dozens of servers with hundreds of jobs. Maybe it was just me,
but no matter how much I pleaded at some of these companies, they would go
through the red-tape to get an email client put on the SQL Servers so I could
use the job notification ability to send me a nice email each morning if a
particular job failed. Being the poor companies DBA, I had to come up with
something else.

The one computer that usually had email abilities was my local desktop, funny
how they always made sure I could get the hundreds of email telling me what to
do each day. To solve my problem, I made use of my desktop and created a system
that checked the outcome of all the jobs across all my servers and sent me a
nice little report each morning.

The first thing I did was to connect to my local msdb database and
create a table to hold the repot information. You can adjust the table how you
want to since I just included the basic information.

Next create a stored procedure that will populate your new table. This
example makes use of linked servers to job information and job history from each
of my servers, you could change the linked server format over to OPENDATASOURCE
if you like.

Otherwise, simply linked all your remote servers to your desktop, adjust the
following stored procedure to account for the number of linked servers you have,
and create the following stored procedure in your msdb database.

Next, simply create a job on your desktop with whatever schedule you like to
run the stored procedure. Once the table has data, it is a simple procedure to
define reporting stored procedures to determine the outcome of jobs, average the
run time of jobs, report on the history of the jobs, etc.

If you want an automatic email sent to you, just configure SQL Mail on your
desktop and create a new job or new job step that uses the xp_sendmail
system stored procedure to run a basic query.

So, if you have the same bad luck in getting those great tools out there or
want a centralized way to keep in control of your job outcomes and history, this
simple technique can go along way in helping you quickly manage those hundreds
of jobs we all seem to accumulate over time.