archiving tables

We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.

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]