Removes residual information left on database pages because of data modification routines in SQL Server. sp_clean_db_free_space cleans all pages in all files of the database.
sp_clean_db_free_space [ @dbname = ] 'database_name' [ , [ @cleaning_delay = ] 'delay_in_seconds' ] [;]
@dbname = 'database_name'
Is the name of the database to clean. dbname is sysname and cannot be NULL.
@cleaning_delay = 'delay_in_seconds'
Specifies an interval to delay between the cleaning of pages. This helps reduce the effect on the I/O system. delay_in_seconds is int with a default of 0.
Return Code Values
0 (success) or 1 (failure)
Delete 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_free_space to clean these ghost records. To perform this operation per database file, use sp_clean_db_file_free_space (Transact-SQL).
The length of time required to run sp_clean_db_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running
sp_clean_db_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_free_space, we recommend that you create a full database backup.
The related sp_clean_db_file_free_space stored procedure can clean a single file.
Requires membership in the
db_owner database role.
The following example cleans all residual information from the
USE master; GO EXEC sp_clean_db_free_space @dbname = N'AdventureWorks2012';