Post reply

Populate TEST environment from PROD backup, truncate some tables, then shrink then rebuild or rebuild then shrink?

  • October 20, 2014 at 7:39 am

    #311951

    The use case is to refresh lesser environments like DEV and TEST with production data, but I don't have the disk resources to keep a 100% copy in lesser environments, so need to put my database on a quick diet. We do not have a sophisticated build process in place (yet), and am really looking for a band-aid for the time being. So please, pragmatic suggestions only.

    The procedure so far is restoring a backup of the desired production database to a secondary server, truncate tables where possible, and batch deleting from some other tables (Order, Shipments, etc.) to slim down the database.

    The next step would be to shrink either a few files or the whole database, but this will likely fragment the indexes. Being a TEST system I am not concerned with blistering performance. This is also a virtualized environment backed by SAN so disk I/O is going to be pretty much random, so most of the benefit is reclaiming wasted space on pages and not necessarily on sequentially re-ordering pages.

    So my question becomes more of a sequencing issue. Is it better to shrink the database then rebuild the indexes and accept the file growth or rebuild then shrink. I'm leaning towards the former, but wanted to solicit opinions.

    Thanks!

  • October 20, 2014 at 7:44 am

    #1753499

    Shrink, then rebuild. As long as you don't shrink to 0% empty space, you shouldn't see too much of a file growth.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 20, 2014 at 8:29 am

    #1753513

    Thanks for the quick response!

    I was thinking if I wanted to try a bit more sophistication on a per filegroup basis: allocate a new filegroup, sized correctly based on sp_spaceused or FILEPROPERTY(name, 'SpaceUsed'), rebuild all indexes to this new filegroup, then drop the old filegroup.

    Any thoughts on that approach?

  • October 20, 2014 at 8:40 am

    #1753517

    It's a lot of work. It'll work, except for PRIMARY. It will leave test different to prod though.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply