Skip to content

Latest commit

 

History

History
87 lines (60 loc) · 3.59 KB

sp-clean-db-file-free-space-transact-sql.md

File metadata and controls

87 lines (60 loc) · 3.59 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_clean_db_file_free_space (Transact-SQL)
Removes residual information left on database pages in a database file, because of data modification routines in SQL Server.
markingmyname
maghan
randolphwest
07/05/2024
sql
system-objects
reference
sp_clean_db_file_free_space
sp_clean_db_file_free_space_TSQL
ghost records
sp_clean_db_file_free_space
TSQL

sp_clean_db_file_free_space (Transact-SQL)

[!INCLUDE SQL Server]

Removes residual information left on database pages because of data modification routines in [!INCLUDE ssNoVersion]. sp_clean_db_file_free_space cleans all pages in only one file of a database.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_clean_db_file_free_space
    [ @dbname = ] N'dbname'
    , [ @fileid = ] fileid
    [ , [ @cleaning_delay = ] cleaning_delay ]
[ ; ]

Arguments

[ @dbname = ] N'dbname'

The name of the database to clean. @dbname is sysname, with no default.

[ @fileid = ] fileid

The data file ID to clean. @fileid is int, with no default.

[ @cleaning_delay = ] cleaning_delay

Specifies an interval to delay between the cleaning of pages. @cleaning_delay is int, with a default of 0. This delay helps reduce the effect on the I/O system.

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. A background process periodically removes ghost records. This residual data isn't returned by the [!INCLUDE ssDE] 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. To perform this operation for all database files at once, use sp_clean_db_free_space.

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 [!INCLUDE sssampledbobject-md] database.

USE master;
GO

EXEC sp_clean_db_file_free_space
    @dbname = N'AdventureWorks2022',
    @fileid = 1;

Related content