Jeff Moden - Monday, January 16, 2017 11:12 PM

VastSQL - Monday, January 16, 2017 10:23 PM

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.

Once you recover the original space, then nightly archive processes won't take up much space and the new data will have room to go in probably without any growth.  If you actually need to recover the space because of the items I mentioned in the post above this one, then you will need to shrink your files and properly regrow the LDF.  Then the nightly process will live comfortably in the relatively small area that it will occupy.  If you do the archive only once per month, then I wouldn't do any shrinking (other than the very first shrink after you massive initial archive process) because it will grow to be the same size at a monthly level as ash304 was suggesting... just at a different level.