Export (0) Print
Expand All
Expand Minimize
1 out of 1 rated this helpful - Rate this topic

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.

Topic link icon Transact-SQL Syntax Conventions


sp_clean_db_file_free_space 
[ @dbname ] = 'database_name' 
, @fileid = 'file_number' 
 [ , [ @cleaning_delay = ] 'delay_in_seconds' ] [;]
[ @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.

0 (success) or 1 (failure)

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.

Requires membership in the db_owner database role.

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 ;
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.