How to delete Job before specified date

• avdhut.k

  SSCrazy

  Points: 2834

  More actions

  January 27, 2014 at 3:38 am

  #396168

  Hi,

  I am using Sql Server 2008 R2,

  We used to create Jobs at daily basis,

  My requirement is to delete Jobs,on or before specified date.

  I can use sp_delete_job to delete job,but how to delete on specified date.

  Please help me how to do it.

  Thanks in Advance!!!!

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  January 27, 2014 at 3:43 am

  #1683473

  I would create a table that stores the information of to be deleted jobs (job name and date of deletion).

  Then I would create a daily job that uses that information to delete all of the jobs that are supposed to be deleted that day.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  January 27, 2014 at 6:21 am

  #1683509

  avdhut.k (1/27/2014)

  ---

  Hi,

  I am using Sql Server 2008 R2,

  We used to create Jobs at daily basis,

  My requirement is to delete Jobs,on or before specified date.

  I can use sp_delete_job to delete job,but how to delete on specified date.

  Please help me how to do it.

  Thanks in Advance!!!!

  Query sysjobs, you have the option of date_created or modified_date. My query below gets any job names older than 31st dec 2013 @ midday

  selectname

  , date_created

  , date_modified

  from msdb..sysjobs

  where date_modified < '2013-12-30 12:00:00'

  order by date_modified

  Build a list and pass the items from that list into sp_delete_job

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉