Read-only Archive databases

  • I administer an environment that consists of databases that are rolled forward quarterly (going back 5 years). Only the current quarter is actively being modified. Each "archive" database consists only 2 large tables with indexing. This is a large reporting environment. I am looking to reclaim the most space possible. I would like to compact the archive databases in such a way that I minimize space allocation for each, and do not negatively affect query performance.

    Can anyone give some recommendations on this?

    I was thinking of shrinking the database, then rebuilding the indexes on the tables with 100% fill factor. If I remove the Read-only option and then use the shrink database command, are all files including index files shrunk?

    Thanks for any advise/suggestion.

    Jason

    If I

  • Shrinking could help, but you need to then be sure that you rebuild the indexes. You've mentioned it, but it's important to clear fragmentation for your heavily used queries. So be sure your clustered index matches where you most often search in groups. The 100% fillfactor should help as well.

    You can also use some hardware optimizations. If your disks are more than 50% full, they data on the second 50% is accessed slower. You might consider moving this data to a new server as well if possible. It definitely helps with caching if you aren't sharing with the OLTP database.

  • Thanks for the suggestion. I am also looking at adding 2 servers to the environment to help with disk access, as you mentioned. I just want to be sure I maximize space I already have.

    Regards,

    Jason

  • Jason,

    I think you have a good plan for now. Look at them on disk as well (defrag there) and then go with your plan.

  • Steve,

    We defrag all volumes quarterly as well.

    I appreciate your taking the time for advice.

    Jason

  • Sure thing and good luck.

    If you remember, let us know what you did and what worked (or didn't).

    Steve

  • jason (9/28/2007)


    I administer an environment that consists of databases that are rolled forward quarterly (going back 5 years). Only the current quarter is actively being modified. Each "archive" database consists only 2 large tables with indexing. This is a large reporting environment. I am looking to reclaim the most space possible. I would like to compact the archive databases in such a way that I minimize space allocation for each, and do not negatively affect query performance.

    Can anyone give some recommendations on this?

    I was thinking of shrinking the database, then rebuilding the indexes on the tables with 100% fill factor. If I remove the Read-only option and then use the shrink database command, are all files including index files shrunk?

    Thanks for any advise/suggestion.

    Jason

    If I

    Jason, DO NOT shrink AFTER the index rebuild.

    In addition you should try also move the the tables from PRIMARY filegroup into another filegroup to make sure the space usage is optimized as much as possible.

    Good luck.


    * Noel

  • He mentioned a rebuild after shrinking, which is what you need to do.

    If you shrink, you'll mess all kinds of stuff up with fragmentation.

  • Steve, I know he did. I was just making sure it was not by chance 😉


    * Noel

  • One last comment : After the data is in read-only mode (can't ever be modified ever again). You can move that data onto its on filegroup, do you thing with indexes, set to read-only. Take a final backup and stop ever doing maintenance on those files. This can be quite usefull if you have a small, or non-existant maintenance window.

Viewing 10 posts - 1 through 9 (of 9 total)

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