delete records doesn't make the table size shrink

Question

Post reply

delete records doesn't make the table size shrink

sqlfriend

SSC Guru

Points: 52781
More actions
March 25, 2012 at 6:52 pm

#266182

We have a table in a database which has the most data in the database.
It basically is a snapshot table to take the student status at the time each night a process run.
The table storage increases as time goses on, but actually we don't want to store so much student info in the table.
So we have maintenance job run to delete some data in history.
But I see the table size still is the same size showing when I right click a table - property- data space.
What else should I done in order for them table size decrease and hence database size decrease because we deleted a lot of history data in the biggest table?
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Here you can find an explanation and a possible solution:

Claim DB Space After Deleting Records in Table - Reduce DB Space

And here you find some resources why you shouldn't use that solution:

Stop Shrinking Your Database Files. Seriously. Now.[/url]

Here's a good reason not to run SHRINKDATABASE...

Shrinking Database is Bad – Increases Fragmentation – Reduces Performance[/url]

So, think twice before you act 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

To reduce the table size, rebuild the clustered index. The problem is likely that the deletes have resulted in 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

Jorge Segarra SSCrazy Points: 2571 More actions · Answer 3

GilaMonster (3/26/2012)
To reduce the table size, rebuild the clustered index. The problem is likely that the deletes have resulted in lots of free space on the pages.

^^ This

=============================================================
/* Backups are worthless, Restores are priceless */

Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management