I have worked on a issues where there was a requirement to release free space from the data file. There was a database whose data file grew very large. Development team worked and deleted/archived lot of data and now there was lot of free space available inside the database file. The next step was to run Shrink command to release the free space inside the file.
Although, we recommended against Shrinking, but the Development team mentioned that the database will not grow much as we will be archiving the data regularly, so we need to get the free space from the data file which can be used by other databases on the server.
So, we started working on releasing the free space available in the data file, but we ran into issues where we tried standard options, but they were not working. Below are some steps which we tried to release the free space.
– Ran DBCC SHRINKFILE against the data file. This command completed successfully, but did not release any free space.
– Tried running ShrinkFile command in batches to release small chunk of space, but still was not working.
– Rebuilded all the indexes in the database and then issued SHRINKFILE command, but still the free space was not released.
– Ran DBCC UPDATEUSAGE to correct any page and row count inaccuracies in the catalog views. Then ran the ShrnikFile command, but still we could not release the free space.
DBCC UPDATEUSAGE (DatabaseName) WITH NO_INFOMSGS;
– We are SQL Server 2008 R2 SP2, so no known issues related to Shrink issues.
– Checked for any GhostRecords/Cleanup, but no such issues found.
– Finally, we were able to fix the issue by below steps.
1. Ran DBCC CLEANTABLE which reclaims space from dropped variable-length columns in tables or indexed views. Below code is used to reclaim space from all tables in current database.
EXEC sp_msforeachtable ‘DBCC CLEANTABLE(0, ”?”) ‘;
2. Ran SHRINKFILE command which now released the free space.
Caution using DBCC CLEANTABLE command, 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 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.