May 15, 2014 at 8:41 am
First off we have SS 2008R2 on VM. I have been told we have a database(~400gb) that has a large table (~87 gb) that has been "emptied" and we need to regain the space. My boss heard that shrink database was bad so his suggestion was that we create a matching table, BCP the data in, and that way we have our smaller table. Or that we do an Insert Into kind of thing.
I think doing a one off Shrinkfile should be fine then doing some reindexing.
What would your suggestions be? By the way I am new to this position.
Nancy
May 15, 2014 at 8:54 am
Nancy
Shrinking is fine as a one-off, so long as that table won't grow to 87GB again. If you think it will, leave the database as it is and ask for some extra disk.
John
May 15, 2014 at 10:14 am
Thanks for the reply. The table will not be growing again. They will hold the files outside the db now. They are just afraid that the shrink will cause the indexes to be useless and that rebuilding them will take too much time. But my question is if they just BCP the table, they will still have all the extra space in the db, we would have to BCP the entire database and that just seems like a really drastic step. Maybe I'm wrong. I don't have any experience with BCP.
Any more voices yeah or nah for Shrink vs BCP?
May 15, 2014 at 11:26 am
If it's a one time thing, shrink it. One, two or three shrinks over the life of a database is no big deal. It's the people scheduling monthly, weekly, daily shrinks that are creating huge problems for their databases.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 15, 2014 at 12:00 pm
What everyone else said, shrinking once or twice is okay.
Shrinking a data file causes extra disk fragmentation. This is why you want to size the disk appropriately upon creation to ensure the data file(s) are as contiguous as possible. Concern over indexes should be a non-issue since they should be maintained regularly anyway.
Shrink it then rebuild/reorg your indexe(s) for the table as needed.
May 15, 2014 at 4:56 pm
You should rebuild your indexes after the shrink.
When you shrink, be sure to leave enough space to accommodate the rebuild of your largest clustered index/table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply