Removes residual information left on database pages because of data modification routines in SQL Server. sp_clean_db_file_free_space cleans all pages in only one file of a database.
Deletes operations from a table or update operations that cause a row to move can immediately free up space on a page by removing references to the row. However, under certain circumstances, the row can physically remain on the data page as a ghost record. Ghost records are periodically removed by a background process. This residual data is not returned by the Database Engine in response to queries. However, in environments in which the physical security of the data or backup files is at risk, you can use sp_clean_db_file_free_space to clean these ghost records.
The length of time required to run sp_clean_db_file_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running sp_clean_db_file_free_space can significantly affect I/O activity, we recommend that you run this procedure outside usual operation hours.
Before you run sp_clean_db_file_free_space, we recommend that you create a full database backup.
The related sp_clean_db_free_space stored procedure cleans all files in the database.
The following example cleans all residual information from the primary data file of the AdventureWorks2008R2 database.
USE Master GO EXEC sp_clean_db_file_free_space @dbname = N'AdventureWorks2008R2', @fileid = 1 ;