May 18, 2011 at 11:15 am
TheSQLGuru (5/18/2011)
sqlapprentice (5/17/2011)
Steve Jones - SSC Editor (5/16/2011)
This (http://www.sqlservercentral.com/Forums/Topic993883-146-6.aspx#bm1008735) seems to indicate this is by design. I would shrink the file, looking to deallocate the LOB extents.I finally solved this issue by exporting to another table with the same structure on another database and importing the data back again.
After doing this, the database has 8GB free inside and a further shrink release much free space back to OS.
I must say it doesn't bode well for your server if you are struggling with space free and need that 8GB back.
Also, PLEASE know that shrinking a database introduces MASSIVE fragmentation to the data and index structures. That can be a horrible thing for performance!
We have a window to clean up all the fragmentations. This is my second concern.
Bazinga!
May 18, 2011 at 11:26 am
Hope you are aware that when you defrag your indexes the size of your database will expand back out some.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2011 at 12:43 pm
TheSQLGuru (5/18/2011)
Hope you are aware that when you defrag your indexes the size of your database will expand back out some.
Could you please explain briefly why filling up all the (previously pretty empty) intermediate pages in the indexes can increase the size of the database?
Albeit i am OK with a slight swelling back of the database size, I am just curious.
Bazinga!
May 18, 2011 at 1:31 pm
It's not intermediate pages, it's that a rebuild of the index creates a duplicate index. Because it's transactional, it builds a new one, uses space (hence db growth) and then drops the old one. So the space is needed for temporary use.
May 18, 2011 at 1:48 pm
Extra space is also required for the intermediate sorting during index build unless you specify the SORT_IN_TEMPDB option. IIRC rebuiding an index requires something like 1.3X size for the rebuild.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 18, 2011 at 3:08 pm
TheSQLGuru (5/18/2011)
Extra space is also required for the intermediate sorting during index build unless you specify the SORT_IN_TEMPDB option. IIRC rebuiding an index requires something like 1.3X size for the rebuild.
Wow, thanks (to Steven as well) for all the down to nuts and bolts details. I wish someday i could call myself a gugu on SQL too.
Bazinga!
May 18, 2011 at 4:29 pm
sqlapprentice (5/18/2011)
TheSQLGuru (5/18/2011)
Extra space is also required for the intermediate sorting during index build unless you specify the SORT_IN_TEMPDB option. IIRC rebuiding an index requires something like 1.3X size for the rebuild.Wow, thanks (to Steven as well) for all the down to nuts and bolts details. I wish someday i could call myself a gugu on SQL too.
When you have 35000 manhours invested in the product you probably WILL be able to call yourself an SQLGuru! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 19, 2011 at 2:08 am
Just one more thing to add, I've read the Microsoft bug fix article (http://support.microsoft.com/kb/272220/EN-US/) regarding incorrectly reclaiming space from a table with text or image columns but it seems to me they all address SQL7.0. However, my box is a one with SQL2008 enterprise and SP2.
Is it becuase text and image data types are essentially outdated types and have different treatment by SQL Server data engine? If our vendor uses for example varbinary(max) instead of image, will this space reclaim issue solved?
Bazinga!
May 19, 2011 at 3:25 am
the ghost cleanup is designed to only delete small amounts of records at a time to avoid swamping the DE performance. With lots of rows you may not see an immediate result in space release. Paul Randall details this on his blog
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 19, 2011 at 3:34 pm
Steve Jones - SSC Editor (5/18/2011)
It's not intermediate pages, it's that a rebuild of the index creates a duplicate index. Because it's transactional, it builds a new one, uses space (hence db growth) and then drops the old one. So the space is needed for temporary use.
Steve,
If sets "SORT_IN_TEMPDB = ON", can't he avoid utilizing space in his database and defer that space utilization to the TempDB?
LC
May 19, 2011 at 3:39 pm
I think you avoid most of it, but AFAIK, there still needs to be some extra space in the db because the index structure has to exist in the database. It can't "switch" from tempdb back to the database instantly. So I'd think you'd need the same index size again as free space.
I'll have to play with it.
May 19, 2011 at 3:47 pm
sqlapprentice (5/16/2011)
Unfortunately, I desperately need space released back to the OS. Any other thoughts?
Yes. What I am going to suggest that you evaluate will not release space back to the operating system. It may, however, free up some space utilized by your large table.
When you rebuilt your table, did you set the the fill factor to something other than the default (100%)?
If your fill factor is something other than 0% or 100% (they are both 100%), you may wish to reevaluate your fill factor setting. If you were to have a low fill factor of, for example 50%, your table would be larger than if the fill factor was set to 100%.
Determining optimum fill factors is a subject in its own right, so I don't want to go into too much detail on this post. I'm just offering it as a consideration you should look at since you are very low on space.
Just a suggestion: If all of your I/O activity on the table consists of insertions of new records and deletions of old records, I would consider setting the fill factor to 100%. If you've never expressly set the fill factor, then it is already defaulted to 100% and there is nothing further to do on this subject.
LC
May 20, 2011 at 6:51 am
Just a suggestion: If all of your I/O activity on the table consists of insertions of new records and deletions of old records, I would consider setting the fill factor to 100%.
That is only true for a heap table or where the clustered index is increasing/decreasing only. If it is on something that is spread throughout the table, 100% gets you massive fragmentation across the entire data value range right from the get go.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2011 at 7:14 am
TheSQLGuru (5/20/2011)
Just a suggestion: If all of your I/O activity on the table consists of insertions of new records and deletions of old records, I would consider setting the fill factor to 100%.
That is only true for a heap table or where the clustered index is increasing/decreasing only. If it is on something that is spread throughout the table, 100% gets you massive fragmentation across the entire data value range right from the get go.
When I referred to insertions, I specifically meant adding new records with the keyword "INSERT" (my use of the word "insertions" was unambiguous). Adding new records to the end of a clustered index will not cause significant fragmentation of a clustered index (he has no non-clustered indexes).
When I referred to deletions, I assumed random deletions, which will cause fragmentation of a clustered index, which is corrected by index rebuilding. If the deletions are all at the "front end" of the table using the lowest numbered IDENTITY column values, there will be virtually no clustered index fragmentation.
He has a table large enough for my statements about the degree of fragmentation to be accurate.
LC
May 20, 2011 at 7:59 am
For THIS poster's table (which has PK on identity) I agreed that your statement was correct. But it is NOT generally applicable, and many people read these forums and a statement like that taken generally can be very problematic. I was addressing the cases where 100% fill factor on a clustered index can lead to horrible fragmentation on pure insert load.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply