MSDN:
"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. 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."
Demo:
Create Permanent Table:
Create Table dbo.Clients (ClientID int, FName varchar(100), LName varchar(100), Sector char(1))
Insert a few Records:
Insert into dbo.Clients Select 1, 'Michael ', 'Jordan', 'N'
Insert into dbo.Clients Select 2, 'Cristiano ', 'Ronaldo', 'F'
Insert into dbo.Clients Select 3, 'Roger', 'Federer', 'A'
Select all data:
Select * from dbo.Clients
List all Pages: [DBCC IND is Undocumented & Unsupported Command; Use at own risk]
DBCC IND(SalesDB, 'dbo.Clients', 1)
PageFID = 1 (Page File ID)
PageID = 328 (Page ID)
Enable Trace 3604 to see output of DBCC Page:
DBCC TRACEON (3604);
View Page: [DBCC PAGE is Undocumented & Unsupported Command; Use at own risk]
DBCC PAGE (SalesDB, 1, 328, 2);
Delete all records:
Truncate Table dbo.Clients
View Page:
DBCC PAGE (SalesDB, 1, 328, 2);
All Records are deleted, but data still exists on the Page.
Drop Table:
Drop Table dbo.Clients
View Page:
DBCC PAGE (SalesDB, 1, 328, 2);
Table is dropped, but data still exists on the Page.
Clear the Data from the Page:
EXEC sp_clean_db_free_space 'SalesDB';
View Page:
DBCC PAGE (Sales, 1, 328, 2);
Table and Data are removed from the Page.
DBCC TRACEOFF (3604);
|
|
|
|
|
|