SQL Homework – July 2018 – Create a SQL Agent Job


July 2, 2018 by Kenneth Fisher

For this month’s homework let’s try something a bit different. SQL Agent jobs are a very powerful tool frequently used for backups and maintenance but can also be used for batch processes, exports, etc. To be fair there is better job scheduling software out there but SQL Agent is quite good and as either a DBA or a developer you are likely to run into it at some point. So let’s give it a shot.


  • The job should have 4 steps.
  • The first step should be a T-SQL script of some kind and should continue on to the next step when complete.
  • The second step should be a different kind of step. I’d recommend either a CmdExec or Powershell step but it could also just as easily be an SSIS package or something else. Again continue on to the next step if successful.
  • The 3rd step will print “Complete”, or if you feel like it set it up to send a DBMail message that the job completed successfully. Upon successful completion of this step, the job will end successfully.
  • The fourth step will print “Failed”, or again if you feel like it send a DBMail message that the job failed. Then when this step finishes (successfully or not) mark the job as failed.
  • Each step should write the results of the step to the job history.
  • The job should run on every Saturday evening at 11pm. It should also run on the third Wednesday of each month at 6am.
  • The owner of the job should not be you and should not be sa.
  • Make sure the job does not actually run, but that it is still scheduled to run as above.

There is a LOT more here to play with, but we will give some of that a shot at a later date.

One thought on “SQL Homework – July 2018 – Create a SQL Agent Job

  1. […] few years ago I gave you homework to create a SQL Agent Job and play with it a little bit. This month I want you to either take that job (or another) and work with it a bit more. We are also […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: