sp_clean_db_file_free_space (Transact-SQL)
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.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
sp_clean_db_file_free_space
[ @dbname ] = 'database_name'
, @fileid = 'file_number'
[ , [ @cleaning_delay = ] 'delay_in_seconds' ] [;]
Arguments
[ @dbname= ] 'database_name'
Is the name of the database to clean. dbname is sysname and cannot be NULL.[ @fileid= ] 'file_number'
Is the data file id to clean. file_number is int 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)
Remarks
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.
Permissions
Requires membership in the db_owner database role.
Examples
The following example cleans all residual information from the primary data file of the AdventureWorks2012 database.
USE Master
GO
EXEC sp_clean_db_file_free_space
@dbname = N'AdventureWorks2012', @fileid = 1 ;