This stored procedure will Archive and then Purge rows from one table to another.
It uses the 'DELETE OUTPUT' statement to delete and move the rows to the destination table, so its pretty fast.
You can set the number of rows that will be purged in each transaction loop to find the optimal size.
It can be set to only run for a certain amount of minutes. For example, you can setup an agent job that will run at 7:00 pm, and only run it for 30 minutes.
If the destination table does not exist, it will be created.
The parameters passed are:
This is the source table name in the format : [database].[schema].[tablename]
This is the destination table in the format : [database].[schema].[tablename]
This is the date column name to check in the source table.
This is the Purge Date. If the value in the @column_to_check column is less than this, it will be purged.
This is the numbe of rows to purge in each transaction. You can adjust this for optimal performance.
This is the minutes that the process will run. The process will exit after this amount of minutes have elasped.
exec usp_archive_rows @sourceTABLENAME = '[ftest].[dbo].[Table_1]',
@desttablename = '[ftest_log].[dbo].[arch_table_1]',
@column_to_check = 'logdate',
@PURGE_DATE = '2014-04-01 00:00:00.000',
@BATCH_SIZE = 500,
@MINUTES_TO_PROCESS = 15
This will arhcive the rows in [ftest].[dbo].[Table_1] into [ftest_log].[dbo].[arch_table_1].
It will check the column 'logdate' in the sourcetable for rows with the date is less than 2014-01-01 00:00:00.000.
It will purge 500 rows in each transaction loop.
It will run for 15 minutes and then exit.