Archiving Solution RRS feed

  • Question

  • Hi There,

    I am experiencing problem with how to Archive data from my production database as the production database is now in some terabytes size and it is effecting performance in bigway because of unnecessary in active data. due to this we have decided to archive un-used data from those databases. the production system is now using SQL Server 2000 so i cant make use of partitioned tables. i am in confusion to develop best Archiving solution in my situation. i am thinking of different options like creating SP's  or cursors(because to avoid heavy locking during the process) or SSIS package to do the same.

    could anyone suggest best possible way  to develop a perfect solution for Archiving data  in my case??



    Tuesday, November 15, 2011 10:35 PM

All replies

  • Hi Peter_saddon,

    Since your database has a large amount of data needs to be transfered, I would suggest you use SSIS, which is powerful and easy to use. You can use it to create packages to move data efficiently and schedule jobs to run the packages at your desired intervals.

    For more help about SSIS, you can move to the SSIS forum.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Get or Request Code Sample from Microsoft
    If you have any feedback, please tell us.
    • Marked as answer by Stephanie Lv Tuesday, November 22, 2011 8:06 AM
    • Unmarked as answer by Peter_staddon Saturday, November 26, 2011 2:47 AM
    Thursday, November 17, 2011 8:31 AM
  • hi you can please refer the below given link, it may help you.


    Tuesday, May 1, 2012 11:05 AM
  • 1) delete data batch wise  use cte

    2) see log file size , will increase when deletion perform.

    3) try shrink log file if is increasing

    4) update statics after deletion data

       you can use ssis packages or query only thing monitor db size .

    check and confirm.

    Thursday, April 9, 2015 3:36 PM