Where's My Additional Free Space ?

Question

Where's My Additional Free Space ?

homebrew01

SSC Guru

Points: 55548
More actions
December 14, 2009 at 6:11 am

#385935

I just archived 6,000,000 rows to another server and deleted them from the original server. sp_spaceused on the archive server shows the table takes up about 80 gig, but the free space in the original database only increased by 40 Gig. What could account for the difference ?

Viewing 15 posts - 1 through 15 (of 24 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Have you rebuilt all the indexes on the tables that rows were deleted from?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I would lean towards Gail's note. Likely something just hasn't been updated, but once you've deleted the rows, the space is available for reuse. If you've deleted things, the space is there. It could still be in extents/pages, marked as free.

homebrew01 SSC Guru Points: 55548 More actions · Answer 3

I haven't had any "quiet time" to rebuild the indexes yet. (I'm running 2005 Standard, so I can't do online rebuilds :crying:)

Would rebuilding actually increase space in the data files ?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

It doesn't increase the space, but it does fix any reporting issues from the deletes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

homebrew01 (12/14/2009)
Would rebuilding actually increase space in the data files ?

It may well do. Say those deletes have been spread out over the table. You may have large portions of the table that have pages that are mostly free space. They're still allocated to the table (and still have at least one row on them) but they're mostly free space. Rebuild with your usual fill factor and suddenly a table that took say 200 000 pages that were on average 40% full can now take under 100 000 pages.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Gail... just checking because I'm low on coffee... does that work only if the table isn't a heap?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Partially. Pre-SQL 2008 a heap can't be rebuilt except by creating a cluster on it. A heap can still have the 10% avg usage of pages if the rows deleted were scattered all over. The space should be later reused by new inserts but after the delete the table could be taking much the same space it did before the delete just with lots of free space on the pages.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

GilaMonster (12/15/2009)
Partially. Pre-SQL 2008 a heap can't be rebuilt except by creating a cluster on it. A heap can still have the 10% avg usage of pages if the rows deleted were scattered all over. The space should be later reused by new inserts but after the delete the table could be taking much the same space it did before the delete just with lots of free space on the pages.

Thanks Gail... that's kind of the way I remembered it.

@Homebrew... do you have a clustered index on the table you deleted from?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

Is it possible that Ghost Cleanup just hasn't run yet?

Inside the Storage Engine: Ghost cleanup in depth

homebrew01 SSC Guru Points: 55548 More actions · Answer 10

Jeff Moden (12/15/2009)
@Homebrew... do you have a clustered index on the table you deleted from?

I do not have a clustered index, but I have 6 non-clustered indexes.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially used until more data is added.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

GilaMonster (12/15/2009)
That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially used until more data is added.

+1 Gail hit the nail on the head.

homebrew01 SSC Guru Points: 55548 More actions · Answer 13

GilaMonster (12/15/2009)
That's a problem. The heap can't be rebuilt, except by putting a cluster on it, and if there are pages partially used in the heap they'll stay partially used until more data is added.

So I rebuild the non-clustered indexes and hope for a small improvement ?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

I'b build a clustered index. You may want to analyze the usage on the table. There may be a useful index that may benefit from being a clustered index instead of a nonclustered index.