Failed to claim unused space with SP_SPACEUSED?
Recently I have been stumped by not cliaming unused space correctly even though after delete handful of rows on a table.
You may be aware using SP_SPACEUSED will get you details such as :
| Column name |
Data type |
Description |
|
database_name |
nvarchar(128) |
Name of the current database. |
|
database_size |
varchar(18) |
Size of the current database in megabytes. database_size includes both data and log files. |
|
unallocated space |
varchar(18) |
Space in the database that has not been reserved for database objects. |
| Column name |
Data type |
Description |
|
reserved |
varchar(18) |
Total amount of space allocated by objects in the database. |
|
data |
varchar(18) |
Total amount of space used by data. |
|
index_size |
varchar(18) |
Total amount of space used by indexes. |
|
unused |
varchar(18) |
Total amount of space reserved for objects in the database, but not yet used. |
So for a moment I was unable to see what could be the problem even after running DBCC UPDATEUSAGE and running SP_SPACEUSED @UpdateUsage='TRUE' against this table. Flash, I remembered that if you ever end up removing a variable-length column (varchar) or a text column from one of your tables, a certain amount of wasted space will linger in the table's physical alignment. SO in order to claim the unused space from the deleted rows you can execute DBCC CLEANTABLE statement, this requires two parameters and an optional batch size as a third. The first parameter is the database name (character data), or database ID (integer data). The second parameter is the table, view name or ID (again, character and integer data, respectively).
To wrap up: You wouldn't get such problem for fixed length of data-types though.