SQL Homework – July 2018 – Create a SQL Agent Job1
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.
Category: Microsoft SQL Server, SQL Homework, SQLServerPedia Syndication | Tags: Microsoft SQL Server, SQL Agent Jobs, SQL Homework
One thought on “SQL Homework – July 2018 – Create a SQL Agent Job”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] 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 […]