Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in SQL Server 2012 with 460.000 rows.

This Table has the following Schema:

Schema:

We had an issue with our application so all 500.000 rows were created with Comments filled up with spaces.

This led to a table-size of approximately 3.7GB:

enter image description here

After this I did the following to fix the ending spaces, but the database stays at 3.7GB.

update voucher set comments = ltrim(rtrim(comments))

A quick calculation showed me, that one completely filled row has approximately 280 bytes of raw data and one empty row (all varchars empty) has 32 Bytes.

This means my table should be between 32MB-122MB for 460.000 rows.

Why is the table 3.7GB? How can I reclaim the space?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Your rows fit in page, even with 100 spaces in Comments. Updating the Comments to ltrim(rtrim)) will make absolutely no difference in space used, since the rows will be left in place in their respective pages (ie. absolutely no page gets freed, you just get more space available on each page). To reclaim the lost space you should rebuild the table:

ALTER TABLE ... REBUILD;

Not that 3.7Gb is big by any stretch...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.