Recently I had an opportunity to inherit a system from a client for doing some enhancements. One of the tasks involved in the project was also to clean up the system which had lots of left overs like unused tables, stored procedures etc. Along with them one thing I noticed was the presence of lots of jobs which are not executed at all but still existing on the production environment. On inquiring on this I was told that these were created by developers for performing activities which would require onetime execution of scripts like for example flushing out old data, applying quarter level financial calculations etc. As a policy the db admin team were creating jobs with a one time schedule for performing them unattended as most of activities had to be performed on weekends or out of office hours.
Issue
Issue with the above approach was that jobs were left in the SQLServer agent even after required task was performed and it was left for someone to go and tidy up them from the server. When I inherited the system it was a big mess with lots of unwanted jobs retained in the instance and it was a time consuming process to identify and remove the unwanted jobs from the system.
Solution
There is an easier solution for scenarios like above to create auto cleanup jobs which takes care of the tidy up part by itself once the required task is performed. This blog explains how we can create the jobs with the auto cleanup capability.
Illustration
Consider the case of deleting oldest quarter data from the table at the beginning of each quarter. This would usually be done over the weekend as deleting bulk data would take considerable amount of time and also there would be some following task like index reorganizing etc.
We can utilize a one time scheduled job for this purpose. There’s an option in the job properties which is seldom used which enables the job to do auto tidy up once the task is performed. The property is set as below in jobs notification properties
Once you set this property the job will get automatically destroyed once it runs successfully as per the set schedule.
Caveat
One caveat to note here is that since the job gets deleted upon successful execution you will not be able to check the history to see if job executed successfully.
So if you need a mechanism to ensure the job executed successfully before getting deleted what you could do is to add a dummy step in the end with just a print statement as below
And then configure to log the output to a file
Once the job is executed and removed you can go and check the output file to see the logged message indicating successful job execution.
Summary
This method can be used to create auto tidying up jobs with notification which will help you to automate adhoc one time tasks based on a predefined schedule. This will help us to make sure the jobs get auto cleaned up after performing the requested tasks and would certainly save some amount of developer effort in getting this cleaned up.
