Sql-server – SQL Server Table keeps big after schema change

database-sizesize;sql server

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?

Best Answer

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...

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

When you delete from heaps, allocated space may not be unallocated unless you use a table lock. See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew] 
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...

Sql-server – Big size of database logs SQL Server 2008

You need to do log backups at least daily (we do them every 15 mintues). Read in books online about how to backup the log (not the daatbase, the log, these are two separate types of backups) and you should find directions for how to truncate the log without backing it up which you will likely need since you have let it get this size. The make sure you have a current backup of the database before you start. Then truncate the log and immediately set of regularly schedueld backups so this doesn't happen again.

Best Answer

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

Sql-server – Big size of database logs SQL Server 2008

Related Question