Can an SQL Job Deletes itself after its end date?

I have created a job which will sets up its start date and end date automatically with a dynamic value.

For example:

Job will run for eight days and at eighth day it needs to automatically deletes itself irrespective of its status.

Is there a way to code it while creating or scheduling ?

Can an SQL Job Deletes itself after its end date?


I have created a job which will sets up its start date and end date
automatically with a dynamic value.


Is there a way to code it while creating or scheduling ?

You can do this by using sp_delete_job and dynamically creating applicable syntax and controlled logic to execute this for the dynamically created job name when date wise it needs to be deleted.

Since you are building the dynamic SQL Agent jobs and creating them with a start date and end date, you'd just put additional logic in to execute sp_delete_job if the date is equal to day eight at the end of the job WHERE @job_name = ''.

You could put the additional logic in at the beginning of the job to execute sp_delete_job if the date is equal to day nine or the day after the eighth day WHERE @job_name = ''.


Syntax

sp_delete_job { [ @job_id = ] job_id | [ @job_name = ] 'job_name' } ,  
     [ , [ @originating_server = ] 'server' ]   
     [ , [ @delete_history = ] delete_history ]  
     [ , [ @delete_unused_schedule = ] delete_unused_schedule ]

source

Since you control the logic that creates the dynamic SQL Agent jobs you should be able to test with additional logic that will delete the dynamic SQL Agent job and execute sp_delete_job if the date is equal to a particular date (e.g. day eight or day nine).

It may be best to use logic that will execute sp_delete_job if the date is greater than or equal to day eight or nine in case it doesn't run on the exact date due to system issues, etc. with logic such as: WHERE Date >= CONVERT(datetime, 'YYYY-MM-DD' )


Example

USE msdb ;  
GO  

EXEC sp_delete_job  
    @job_name = N'NightlyBackups' ;  
GO

source

StackExchange Database Administrators Q#143941, answer score: 6