locked
SQL agent job to run after 13 months RRS feed

  • Question

  • Hi,

    I want  to execute a 'DELETE' command for a table after 13 months. The SQL agent job should run every 13 months and delete the records from a table. I'm able to create the step say: DELETE From <TableName> for SQL agent job but I'm unable to execute the job once 13months completes.

    Example:-

    Current month is Dec'2012. So delete records from a table where month in 'Date' field ranges from  previous 12 months to current month (total=13 months) i.e from Nov'2011 to Dec'2012. I had created the schedule with properties:-

    Schedule type: Recurring

    Frequency: 

      Occurs : Monthly

     The lastThursday of every 13 month(s).

    Daily Frequency:

      Occurs once at : 3:05:00 PM

    Duration:

      Start Date: 10/26/2012

    Description:

    Occurs every last Thursday of every 13 month(s) at 3:05:00 PM. Schedule will be used starting on 10/26/2012.

    But the job doesn't automatically runs on 3:05:00 PM.

    Can anyone please share some tips or examples to perform this operation.

    Thanks in advance.

    Ashish

    Wednesday, December 26, 2012 9:55 AM

Answers

  • Thanks Satheesh and Russell for your responses.

    Ok looking back at my original settings as shared in my first post:-

    Schedule type: Recurring

    Frequency: 

      Occurs : Monthly

     The lastFriday of every 13 month(s).

    Daily Frequency:

      Occurs once at : 8:00:00 AM

    Duration:

      Start Date: 12/28/2012

    OR in terms of script the schedule will have following parameters:-

           @enabled=1,
            @freq_type=32,
            @freq_interval=6,
            @freq_subday_type=1,
            @freq_subday_interval=2,
            @freq_relative_interval=16,
            @freq_recurrence_factor=13,
            @active_start_date=20121228,
            @active_end_date=99991231,
            @active_start_time=80000,
            @active_end_time=235959

    Now If i create the schedule according to these original settings and then run the command:-

    USE msdb ;
    GO
    exec sp_help_jobactivity @job_name = 'Testing_Delete'
    GO

    I recieve the 'next_scheduled_run_date' value as "2014-01-31 08:00:00.000". That means it will execute the job again on 31JAN2014 as required after 13 months.

    So isn't original setting should work in such case?? If the original settings doesn't work then why i'm recieving a correct next_scheduled_run_date??



    Wednesday, January 2, 2013 4:00 AM
  • Yes, I believe so.

    Also I was under the impression that there is no direct way to schedule the job for 13 months, and that was the problem you were facing, just now I confirmed and it is possible.

    Regards
    satheesh



    Wednesday, January 2, 2013 5:21 AM

All replies

  • I don't think there is a direct way to schedule for every 13 months.

    What you can do is add the schedule for next run from an SP once you delete records. That can go as the final step in the SQL agent Job

    Regards
    Satheesh



    Wednesday, December 26, 2012 10:57 AM
  • So, the next job would run in January 2014 and would delete from December 2012 through January 2014?  This is interesting since the description appears to mean that you are deleting data that is 13 months old up to and including data that was just inserted at 3:04:59 PM.   (This seems like a strange purge pattern to me, for what it is worth.)

    Satheesh suggested a stored procedure to reset the next run date.  That should work and the stored procedure could be run a the end of the SQL Agent job step, creating the next schedule entry.

    Another way to do this is to schedule the job to run on the last Thursday of every month.  Then have some controlling information to decide whether to delete everything, or to skip the delete for this month.  E.g. sample snippet of code:

    Create Table #myTable (EntryDate Datetime)
     
    -- Try different dates
    INSERT INTO #MyTable Values('2011/11/01')
     
    if exists (SELECT * FROM #MyTable WHERE EntryDate <=  DATEADD (Month,-13,GETDATE()))
       Print 'Run the Delete'
    else
       Print 'Wait another month.'
       
    Drop Table #myTable

    This code assumes that all of the data from within the earlier period is deleted.  Therefore testing for data at the extreme range will cause the delete to happen.  (Of course, the Print 'Run the Delete' would be replaced by the actual code that does the deletion.)

    RLF

    Wednesday, December 26, 2012 1:54 PM
  • Your scheduling issue is answered by satheesh and Russell ! There are other tasks to look into like ...

    When you are scheduling job even take consideration of keeping first step as to take the database backup & I would suggest to run the delete statement in batches say delete 10000 records . In this way you can avoid log file issues .

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

    Thanks,Suhas Vallala

    Wednesday, December 26, 2012 2:03 PM
  • Ok. thanks for replying guys.

    Russell: Actually, i have another tool that generates the report from database with a date range of 13 months (previous 12 month+current month) , that's why data should be retained for maximum 13 months.

    I liked the idea from Satish of setting schedule for next 13 months as a job step.

    Can anyone please share some example of how to set schedule for next 13 months as a job step??

    Thursday, December 27, 2012 6:18 AM
  • The Agent Job steps will be like this

    Step 1. Call your SP for deletion

    Step 2. Call a second SP which will set the schedule.

    Now inside the SP calculate the schedule for next run (datediff(month,13, Current_rundate ...something like this will work)  and use sp_add_jobschedule  to add the Schedule 

    USE [msdb]
    GO
    DECLARE @schedule_id int
    EXEC msdb.dbo.sp_add_jobschedule 
    @job_id=N'<<Job ID(get this from sysjobs)>>', @name=N'Next13', 
    		@enabled=1, 
    		@freq_type=8, 
    		@freq_interval=16, 
    		@freq_subday_type=1, 
    		@freq_subday_interval=0, 
    		@freq_relative_interval=0, 
    		@freq_recurrence_factor=1, 
    		@active_start_date=20121227, --Next Run Date
    		@active_end_date=20121227,   --- Schedule End date
    		@active_start_time=0, 
    		@active_end_time=235959, @schedule_id = @schedule_id OUTPUT
    select @schedule_id
    GO
     

    Clickk the link for more details on sp_addjobschedule

    http://msdn.microsoft.com/en-us/library/ms366342.aspx

    Regards
    Satheesh


    • Edited by Satheesh Variath Thursday, December 27, 2012 6:31 AM
    • Proposed as answer by SQL_Learn Thursday, December 27, 2012 10:16 AM
    Thursday, December 27, 2012 6:30 AM
  • Thanks Satheesh.

    This is what i've implemented:-

    --------------------------job step code----------------

    declare @myDate DateTime,@newDate DateTime
    declare @intDate int

    BEGIN TRANSACTION
    Set @myDate=GETDATE()

    Set @newDate = DateAdd(Month,14, @myDate - Day(@myDate) + 1) -1
    Set @intDate =CAST(Convert(varchar(8),@newDate,112)as INT)


    EXEC msdb.dbo.sp_update_jobschedule @job_name=N'Testing_Delete', @name=N'Delete_Records',  
           @active_start_date=@intDate

    COMMIT TRANSACTION

    ----------------------------X-----------------------------

    I've used "sp_update_jobschedule" instead of "sp_add_jobschedule" as it will just update the existing schedule for next execution rather then creating a new one. This seems to work fine as per the requirement, however, i still have 2 queries regarding it:-

    (1) "@active_start_date" value ideally signifies the date when the job is first executed. Here, by changing the @active_start_date value will result in the loss of actual start date value but it can be traced by using job history or logs. If a new schedule is added, an unnecessary entries of previously executed schedules will come up.

    Can there be any workaround for maintaining both the actual start date value and the new @active_start_date value?? ( May be not possible in my case!!)

    (2) I require to make the job re-run period of 13 months as configurable, so that if in future requirement changes, one can easily configure it say for example as 6 months or 14 months.

    Is it possible to make this value as configurable and then use this value to identify the next date as i've shown in the job step code above??

    Thanks in advance!!

    Ashish

           




    Thursday, December 27, 2012 3:30 PM
  • I am not sure of the first question.

    What come to my mind is another solution. If you really want to keep the Job Run details , you can create a table in your Db and make log entries. that way if the SQL agent Job is archived, you will have the history records with you.

    For the second question, you can make a table again. 

    CREATE TABLE JobConfig(
    ID INT, 
    RunDate Date,
    MonthToRunAfter  int)
    --1 27 Dec 2012 13
    --

    Make your SP which sets the next schedule to read the months from this table . It will pick the latest date and  MonthToRunAfter from there. If no entry is there then default it to 13 months. 

    I hope this works for you

    Regards
    satheesh

    Thursday, December 27, 2012 4:01 PM
  • Sure, base the new date on the current date or on the latest run.  For example:

    DECLARE @NextDate datetime;

    SET @NextDate = DATEADD(Month, 13, GETDATE());

    Convert it to the IntDate and use it in your update schedule call.

    RLF

    Thursday, December 27, 2012 5:11 PM
  • Thanks Satheesh and Russell for your responses.

    Ok looking back at my original settings as shared in my first post:-

    Schedule type: Recurring

    Frequency: 

      Occurs : Monthly

     The lastFriday of every 13 month(s).

    Daily Frequency:

      Occurs once at : 8:00:00 AM

    Duration:

      Start Date: 12/28/2012

    OR in terms of script the schedule will have following parameters:-

           @enabled=1,
            @freq_type=32,
            @freq_interval=6,
            @freq_subday_type=1,
            @freq_subday_interval=2,
            @freq_relative_interval=16,
            @freq_recurrence_factor=13,
            @active_start_date=20121228,
            @active_end_date=99991231,
            @active_start_time=80000,
            @active_end_time=235959

    Now If i create the schedule according to these original settings and then run the command:-

    USE msdb ;
    GO
    exec sp_help_jobactivity @job_name = 'Testing_Delete'
    GO

    I recieve the 'next_scheduled_run_date' value as "2014-01-31 08:00:00.000". That means it will execute the job again on 31JAN2014 as required after 13 months.

    So isn't original setting should work in such case?? If the original settings doesn't work then why i'm recieving a correct next_scheduled_run_date??



    Wednesday, January 2, 2013 4:00 AM
  • Yes, I believe so.

    Also I was under the impression that there is no direct way to schedule the job for 13 months, and that was the problem you were facing, just now I confirmed and it is possible.

    Regards
    satheesh



    Wednesday, January 2, 2013 5:21 AM
  • Ok. Thanks Satheesh.

    Thanks to all for responses. This was a good learning experience for me.

    Regards,

    Ashish

    Thursday, January 3, 2013 6:21 AM