Home Forums SQL Server 2014 Administration - SQL Server 2014 Data Archive RE: Data Archive

  • January 16, 2017 at 10:23 pm

    #1923101

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.