krypto69 (12/5/2016)

---

1) Who said anything about nightly index rebuild? It sounded to me like you did some massive cleanup process that removed hundreds of GB from your database and you want that space back.

2) If this is an ongoing process refer to my point about not shrinking at all. You are just going to put data back into the database apparently if you then need to delete some/all of it the next day.

3) Unless you have HEAP tables, deleting records releases the empty pages for reuse. And if you delete some of the records on the page in a range index and then put more in the next day/week you don't want to reindex the empty space out of the pages anyway.

4) If you are deleting a large fraction of a table then it is MUCH more efficient to TRUNCATE the table (perhaps copying out records you need to keep to a new object).

1) Yes massive cleanup first - then a nightly delete

2) I need to reclaim after the first massive delete (that is the whole point of doing this is to reclaim 800 Gigs of space). Should I rebuild all indexes after this first massive delete? Or am I better off to truncate and create the tables via a script?

3) none of these are HEAP's

4) Yeah but I was hoping to retain the Id's (pkey values) into the new tables. If I truncate I will not be passing the old I'ds.