December 5, 2016 at 10:13 am
Hi,
Long story but I have a production DB that has a ton of old archive records (staging records). I wrote an SSIS package that copies all the old archive records to a different server for archiving purposes. The package then batch deletes the old records based on date. Works fine.
My problem is that after I run my deletes I am left with allot of 'available free space' that I want to reclaim. In researching it looks like the only way to reclaim this deleted space is to run:
DBCC SHRINKDATABASE (N'DB' )
My understanding is that running this will cause indexes to fragment. I don't want that.
So how do I reclaim all that newly freed up space without fragmenting indexes?
December 5, 2016 at 10:27 am
krypto69 (12/5/2016)
Hi,Long story but I have a production DB that has a ton of old archive records (staging records). I wrote an SSIS package that copies all the old archive records to a different server for archiving purposes. The package then batch deletes the old records based on date. Works fine.
My problem is that after I run my deletes I am left with allot of 'available free space' that I want to reclaim. In researching it looks like the only way to reclaim this deleted space is to run:
DBCC SHRINKDATABASE (N'DB' )
My understanding is that running this will cause indexes to fragment. I don't want that.
So how do I reclaim all that newly freed up space without fragmenting indexes?
You don't, at least not the original database. If you want to shrink leave plenty of space for the next 12-18 months data growth and then do a REBUILD on every index on every table.
Alternatively you could create an entirely new database (again of the appropriate size FROM THE START) and then copy everything into it and then drop the old one after you had verified you were good.
I will add that if there is ANY CHANCE that the database will grow back to anywhere near where it is now, I would leave it as is. Well, first I would check for having left the DEVASTATINGLY BAD SQL SERVER DEFAULT of 1MB data file growth in place on the way up. If that is the case I would plan out a proper OS file defrag project. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 5, 2016 at 10:44 am
I'd really prefer not to have to do a nightly rebuild on every index - don't think I have enough time to complete that nightly anyway.
Can I just do a rebuild on the indexes for the tables that I deleted records from? Or does it have to be all tables in the DB?
Is my problem the deletion of the records? If I were to change it to truncate the tables instead of delete would the space allocation be correct (free up the unused/deleted space)?
December 5, 2016 at 10:56 am
Consider containing your staging tables in a separate database or file group, so they can the shrunk down without impacting your other application tables. It also prevents you from including all this staging data in backups; if that's your goal.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 5, 2016 at 11:12 am
I'd really prefer not to have to do a nightly rebuild on every index - don't think I have enough time to complete that nightly anyway.
Can I just do a rebuild on the indexes for the tables that I deleted records from? Or does it have to be all tables in the DB?
Is my problem the deletion of the records? If I were to change it to truncate the tables instead of delete would the space allocation be correct (free up the unused/deleted space)?
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).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 5, 2016 at 12:35 pm
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.
December 5, 2016 at 11:55 pm
This was removed by the editor as SPAM
December 6, 2016 at 7:43 am
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.
A) Assuming you are only going to be left with data that is significantly smaller than 800GB I would do the "move what I need to keep to new tables then truncate the old ones" routine for the initial cleanup. Be sure to be smart about indexing and how you do the movement to be most efficient. You won't need to rebuild anything this way - just create them after populating your new tables. BTW, do this into HEAP tables to start and then shrink the database and then create all indexes.
B) You can keep identity values. Look up books online and SET IDENTITY_INSERT ON.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply