Free space after rows removed

Question

Free space after rows removed

david.alcock

SSCertifiable

Points: 6021
More actions
February 24, 2014 at 8:47 am

#288495

I'm fully aware that after deleting records within a table the space is not returned back, I'm fine with that. However, I have just removed 900,000 records from a table that was 212Gb in size (CRM), I've rebuilt the clustered index and it has freed up roughly 5Gb!
So the removed records have not really dented the reserved or free space. Am I right in thinking that after the deletion any new records will be allocated into the newly freed up (but not visible) space so essentially, the database wont grow for some time?
Regards
David
'Only he who wanders finds new paths'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

david.alcock (2/24/2014)
I'm fully aware that after deleting records within a table the space is not returned back, I'm fine with that. However, I have just removed 900,000 records from a table that was 212Gb in size (CRM), I've rebuilt the clustered index and it has freed up roughly 5Gb!
So the removed records have not really dented the reserved or free space. Am I right in thinking that after the deletion any new records will be allocated into the newly freed up (but not visible) space so essentially, the database wont grow for some time?
Regards
David

Correct, the space will be available to you to use within the DB but unless you run a SHRINK DB command you won't actually see the space reclaimed. Unless you truly need the space due to limitations in your storage, you probably shouldn't bother shrinking your DB/files as it will most likely highly fragment your indexes and just cause more "maintenance" for you in the long run

See: http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 2

Basically what is happening is that your deleted records become ghost records. This is a performance feature to speed up the delete and rollbacks if needed. Once the record is marked as a ghost record it stays that way until the ghost cleanup task does its thing and removes the record (slot). The space is now available to the database to use. Here is a post to help explain what is happening:

http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-ghost-cleanup-in-depth/

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

david.alcock SSCertifiable Points: 6021 More actions · Answer 3

Thanks all, always had the concept but good to read in a little more detail!

Thanks again

D

'Only he who wanders finds new paths'

david.alcock SSCertifiable Points: 6021 More actions · Answer 4

Hi all and apologies for bringing this post up again.

I've done some more digging and looking into the dm_db_index_physical_stats have found the following:

CLUSTERED INDEX: LOB_DATA

Page Count = 26915075

Record_Count = 1702573

Ghost_Record_Count = 34936562

!!!!!

Now I have seen the ghost cleanup running, but yet it doesnt seem to be clearing this lot out. By the way its an old CRM 4.0 table (asyncoperationbase).

Any help or ideas on how to approach these massively appreciated. Also, its SQL2005, 9.00.4035.00

Regards

D

'Only he who wanders finds new paths'

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Execute sp_clean_db_free_space to clean gost record and again check.

If your server is production be careful running this. Also advisable to take full backup before executing this command. Prefer to execute it during outside business hours as this is I/O intensive.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

david.alcock SSCertifiable Points: 6021 More actions · Answer 6

Also makes no sense why I put this in the 2008 forum!

Yes I tried previously and no luck - same with rebuilding the clustered index, we're not presently in a position to restart either though I suspect we may have to.

I think what I might try to do is to recreate the table on a different server and assess the space, we might have a window to recreate everything.

D

'Only he who wanders finds new paths'