Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ronmorg
Starting Member
1 Post |
 Posted - 2014-06-26 : 11:21:11
|
| I have a large db that i need to shrink quickly and return the free space to the os FileSizeMB UsedSpaceMB UnusedSpaceMB DBFileName877002.00 572448.25 304553.75 TR60010.00 50.03 59959.97 TR_log861756.88 560943.69 300813.19 TR237648.00 31237.94 6410.06 TR3 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-26 : 13:32:56
|
| You can use DBCC SHRINKFILE. Rebuilding indexes prior to it can help, though that'll take some time and might use additional storage.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-06-27 : 03:18:30
|
| More long - term you can review the tables used ? are they all required - are there any backup tables etc?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2014-07-02 : 13:22:59
|
quote:
Originally posted by tkizer
You can use DBCC SHRINKFILE. Rebuilding indexes prior to it can help, though that'll take some time and might use additional storage.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Rebuilding indexes after the shrink will be required - the shrink will cause the indexes to become fragmented. You may end up with the same space being used after rebuilding the indexes. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-07-02 : 13:31:00
|
quote:
Originally posted by jeffw8713
quote:
Originally posted by tkizer
You can use DBCC SHRINKFILE. Rebuilding indexes prior to it can help, though that'll take some time and might use additional storage.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Rebuilding indexes after the shrink will be required - the shrink will cause the indexes to become fragmented. You may end up with the same space being used after rebuilding the indexes.
Certainly not required to rebuild indexes after the shrink, though I do agree the shrink will cause fragmentation. I was purely answering the OP's question which is to make the shrink fast. Rebuilding indexes does help with that.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Shanky
Yak Posting Veteran
84 Posts |
Posted - 2014-07-09 : 06:59:57
|
| How was the large space created, if it was created with huge delete operation space wont be released immediately because the records are marked as ghosted and it would take some time for background process to remove ghost records and mark pages with no ghost record till than you have to patiently wait.More details about ghost cleanup http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/You ofcourse have to use dbcc shrinkfile command to shrink but shrinkfile is single thread operation and so it is slow. Instead of shrinking with query you can use GUI(SSMS) and select option release unused space what this will do is cut out unused space but will not do any page movement so chances of fragmentation would be quite less.Check option'Release unused space ' in below linkhttp://msdn.microsoft.com/en-us/library/ms190757.aspxPS: Shrinking causes massive logical fragmentationRegardsShankyhttp://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-07-11 : 01:43:35
|
quote:
You ofcourse have to use dbcc shrinkfile command to shrink but shrinkfile is single thread operation and so it is slow. Instead of shrinking with query you can use GUI(SSMS) and select option release unused space what this will do is cut out unused space but will not do any page movement so chances of fragmentation would be quite less.
That's assuming there is any unused space. If you want to predict the time it will take to complete a DBCC SHRINKDATABASE - use query in this link - http://www.sqlserver-dba.com/2012/11/sql-server-how-to-predict-the-dbcc-shrinkdatabase-finish-time.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
need to shrink large fast