| title | sp_clean_db_file_free_space (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 3eb53a67-969d-4cb8-9681-b1c8e6fd55b6 | ||
| author | stevestein | ||
| ms.author | sstein |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Removes residual information left on database pages because of data modification routines in [!INCLUDEssNoVersion]. sp_clean_db_file_free_space cleans all pages in only one file of a database.
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 [!INCLUDEssDE] 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 AdventureWorks2012 database.
USE master;
GO
EXEC sp_clean_db_file_free_space
@dbname = N'AdventureWorks2012', @fileid = 1 ;
Database Engine Stored Procedures (Transact-SQL)
Ghost Cleanup Process Guide