none
Archiving large table RRS feed

  • Question

  • I have a database size around 2.5 TB which has a table with 3 billion records size about 650 GB, Database is in simple recovery model.

    I want to archive the table since it is not required. i am planning to create a new blank database where I can select into the complete 650 GB table from old DB to new DB and then take the compress backup of new DB which may capture 150 GB backup space then remove newDB from server and table from old DB.

    Here I just wanted to understand if I Select Into such a large table of 650 GB then what challenges can I face on production like log growth in simple recovery and any other performance issues? whether it will be completed or may fail in between due to something?


    SQL Server DBA


    • Edited by Zeal DBA Thursday, June 27, 2019 9:57 AM
    Thursday, June 27, 2019 9:55 AM

All replies

  • Selecting should not cause log growth on source but it will cause log to grow on destination where you are inserting. Do you have column in your database which is ever increasing like identity or some ever increasing value. In that case you can write queries to move data in batches which I would suggest. Identity can be bit misleading in ever increasing so be careful

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, June 27, 2019 10:17 AM
  • how much log size while SELECT INTO can be expect in new DB while data size of a table is 650 GB

    SQL Server DBA

    Thursday, June 27, 2019 2:04 PM
  • Hi Zeal DBA,

    >> how much log size while SELECT INTO can be expect in new DB while data size of a table is 650 GB

    I can’t offer a fixed value size of log file.

    But the sys.dm_db_log_space_usage DMV can be used to monitor the log space in a specific database. It provides information about the amount of log space currently used.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, June 28, 2019 9:23 AM