How to delete records at a specified time

Question

I need to truncate my table every month exactly at 23:59:59.59 hour so tat the new records

starts getting inserted from 00:00:00.00 automatically. How will I do it without scheduling

the job..??

Example:

The table must get truncated or records must get automatically deleted exactly at July 31st

23:59:59.59

score 0 · Answer 1 · edited May 23 '17 at 12:11

0

You can do this by CRON or by the SSMS New Job

Follow this link for SQL Server Creating Ne Job

SQL Server New Job Every Month

edited May 23 '17 at 12:11

Community

1
1

answered Jul 03 '14 at 09:45

Arijit Mukherjee

3,817
2
31
51

SQL Server jobs can be scheduled only to the nearest second. So when do you schedule it for? 23:59:59 or 00:00:00? Say the former and it takes 700 milliseconds to run. What do you do with a record which arrives as 23:59:59.900? Schedule at the stroke of midnight an you may delete rows recorded at, say, 00:00:00.010. Depending on the business this may be OK or disasterous. – Michael Green Jul 03 '14 at 13:31

score 0 · Answer 2 · answered Jul 03 '14 at 10:36

Not so good but 1 more way of achieving this is by using WAITFOR DAELAY feature.

Eg:

BEGIN
    WAITFOR DELAY '24:00';
    EXECUTE sp_helpdb;
END;
GO

Now the problem with WAITFOR DAELAY is that, you can't give a time more than 24 hours. So in this case you can do a workaround as follows:

In your SP add few lines:

IF CAST(GETDATE() AS DATE) = '07/31/2014'--OR dynamically calculate last day of the month
BEGIN
     DELETE FROM TableName;-- your delete statement
END

As you will schedule WAITFOR for everyday at night, so we can assume on '07/31/2014' also it will be invoked at night only, hence only matching the date part will be enough. If you specify the time then there is possibility that the SP is invoked on exact specified time as there are many factors involved (such CPU cycle is available, thread, etc.) while invoking a script.

For your reference: http://msdn.microsoft.com/en-IN/library/ms187331.aspx

Its something new to me. For sure will try the above method . Thank you — user3737182, Jul 03 '14 at 11:15
While testing SQL Server on virtual machines last year we observed `WAITFOR DELAY '00:00:10'` take 30 seconds to complete. Be careful using it to schedule activities to millisecond precision. — Michael Green, Jul 03 '14 at 13:26
@user3737182 Please do let me know if this solution worked in your case. — Nithin Gangadharan, Jul 07 '14 at 05:44

score 0 · Answer 3 · answered Jul 03 '14 at 13:21

What's the granularity on your time? Your example shows hundreths of a second. What's your tolerance for losing records, say one that's created one ten thousandth of a second after midnight, or of retaining one that's created one ten thousandth of a second before midnight?

Any solution based on job scheduling will have to deal with granularity discrepancies between the job scheduler and the data, plus the execution time of the job itself, plus any pre-emptive multitasking the OS and/or SQL Server throw into the mix plus any shenanigans the underlying VM (if any) may wreck.

I'd suggest you use a partitioned view to spread your data over base tables according to the date & time the data was captured. Since you talk of truncating once a month two tables distinguished by "month number modulo 2" would be adequate. Similar schemes could be devised for any truncation rate you fancy. Then you can perform any tidy-up, reconciliation, archiving or whatever on last month's base table while actively inserting into this month's. Once you're happy truncate last month's base table and you're ready for the next month-end cycle.

How to delete records at a specified time

3 Answers3