Archiving and Pruning growing transaction tables

Hi,

I am using SQL Server 2005.

I would like to know best approaches for archiving and pruning couple of growing transaction tables in a database.

Possible approaches which I could think of
1) Take a backup of the database and delete records from tables based on date. (Issues - Deletion from existing tables takes a long time and the Indexes are disturbed requiring reindexing.)

2) Partition the table based on Date and possibly backup only the older partitions and remove them. (not sure if this can be done seemlessly).

Please let me know your thoughts.

Thanks,
Loonysan

I would suggest you look into partitioning, as you can drop a complete partition instead of doing a lot of deletes.

I worked on a system before where we had to delete a lot of records on date, and we had performance issues when we had to delete a lot of rows. We converted the system to partitioning, and could drop a partition at a time, which solved the performance problem.

Thanks,

Marcel van der Holst
[MSFT]