March 28, 2013 at 1:48 pm
Recently we had a 2TB database with one data file and one log file. Last week, all objects within this 2TB database was compressed with Page Compression. After compression was complete it brought the database size down to approximately 650GB. Now I need to free up space because this database is on expensive disk (SSD) and can be utilized for other databases which are highly active. With all this unallocated space what is the advice from the community what to do?
I understand shrinking the data file causes massive index fragmentation. Would rebuilding the indexes fix this?
Create another database or filegroup(s), and do a move, of all objects and compress again after completion?
Recommendations from the community on how to approach this is much appreciated.
database_sizeunallocated space
2116119.75 MB1459100.01 MB
reserved data index_sizeunused
649338616 KB484200576 KB138035032 KB27103008 KB
March 28, 2013 at 2:18 pm
If you do shrink it then yes rebuilding the indexes and updating stats is a must, but remember it will cause all your queries to recompile. I'm not sure what type of stats update would be necessary here - probably a full scan. Remember the sp_updatestats uses the last sampling rate - but if you rebuild indexes instead of reorg it will do a 100% sampling rate on the stats anyway.
If you plan on using the rest of the space for other dbs make sure you have planned the growth of the compressed db and the other additional dbs correctly to ensure you don't run out of space - that would be bad news :S
March 28, 2013 at 3:12 pm
Loundy (3/28/2013)
I'm not sure what type of stats update would be necessary here - probably a full scan.
None. Shrinking a DB doesn't affect the stats so there's no need to update stats afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2013 at 3:39 pm
Is the advice here to shrink and rebuilt the indexes? Will I need to alter tables and rebuild heaps?
March 28, 2013 at 3:46 pm
GilaMonster (3/28/2013)
Loundy (3/28/2013)
I'm not sure what type of stats update would be necessary here - probably a full scan.None. Shrinking a DB doesn't affect the stats so there's no need to update stats afterwards.
AH ok - but as the db is going to be shrunk it's best to rebuild the indexes which will update the stats automatically right? so either way your right - no need.
I guess the advice here is to shrink then rebuild the indexes - you will obviously need to have a decent maintenance window to do this as shrinking the db of this size could take a while and rebuilding the indexes equally could take some time depending on the size of the indexes.
March 29, 2013 at 8:03 am
Thank you Loundy! Gail are you in agreement to shrink and rebuild indexes?
March 29, 2013 at 8:08 am
The alternative for shinking is to move objects to a second filegroup and drop the original filegroup. However you can't do that because the original filegroup is primary and primary must always exist. So your options are:
- Create a second filegroup, move objects, shrink primary
- Shrink primary file, rebuild indexes
I don't see much recommending one over the other in this scenario.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2013 at 9:19 am
Thank you!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply