August 4, 2008 at 4:16 am
Hi, I need to create a scheduled job that will truncate tables in a live environment between two dates i.e. on the first day of each month we truncate all the data in the database from the preceding month. So on the 1st of August we truncate everything out of the tables from the month of July. As this is a very sensitive and live environment this job will need to be carried out outside of normal working hours, we are looking at truncating about 1 million rows each month with as little impact to the live database as possible.
Any advice or tips would be much appreciated.
August 4, 2008 at 4:33 am
Truncate table command will delete all rows in the table , so unless you can guarantee that there will be no inserts happening to the table until the previous records have been deleted you shouldn't use the truncate command.
Deleting on the other hand will give you the flexibility of deleting rows from the previous month while rows for the current month are still being inserted.
truncate table works very fast and if you can afford to lock the table while the truncation is going then this would be your best bet in terms of performance. but since your database is live and you might require the transactions in case something goes wrong i recommend that you go with the delete.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply