February 28, 2013 at 5:24 am
Hi All,
Actually I am running below command, but i am not able to reduce "UnusedSpacedKB" 184 from XYZ Table.
I am running below command :
DBCC CLEANTABLE (DatabaseName,'XYZ', 0)
--------------------------------------------------------------
I don't know why UnuseredSpaced is not reducing. Would request please guide me if any concept problem.
TableName : XYZ
RowCounts : 5387
TotalSpaceKB: 1296
UsedSpaceKB: 1112
UnusedSpaceKB : 184
Note : I have big table which has taken 59 GB space which i want to remove but first i am testing on small table.
Regards,
Sachin.
February 28, 2013 at 6:39 am
This is from MSDN regarding DBCC CLEANTABLE:
DBCC CLEANTABLE reclaims space after a variable-length column is dropped. A variable-length column can be one of the following data types: varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. The command does not reclaim space after a fixed-length column is dropped.
If the dropped columns were stored in-row, DBCC CLEANTABLE reclaims space from the IN_ROW_DATA allocation unit of the table. If the columns were stored off-row, space is reclaimed from either the ROW_OVERFLOW_DATA or the LOB_DATA allocation unit depending on the data type of the dropped column. If reclaiming space from a ROW_OVERFLOW_DATA or LOB_DATA page results in an empty page, DBCC CLEANTABLE removes the page.
DBCC CLEANTABLE runs as one or more transactions. If a batch size is not specified, the command processes the whole table in one transaction and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows. DBCC CLEANTABLE cannot be run as a transaction inside another transaction.
This operation is fully logged.
DBCC CLEANTABLE is not supported for use on system tables, temporary tables, or the xVelocity memory optimized columnstore index portion of a table.
Best Practices
DBCC CLEANTABLE should not be executed as a routine maintenance task. Instead, use DBCC CLEANTABLE after you make significant changes to variable-length columns in a table or indexed view and you need to immediately reclaim the unused space. Alternatively, you can rebuild the indexes on the table or view; however, doing so is a more resource-intensive operation.
Have you dropped any variable length columns from these tables?
March 2, 2013 at 10:42 am
Haven't heard back so was wondering if the information provided was helpful or not,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply