Jaa

Page Cleaning with sp_clean_db_file_free_space and sp_clean_db_free_space

  • Artikkeli

When you download the latest SQL Server 2008 Books Online (January 2009 update) you'll see new entries for the system stored procedures sp_clean_db_file_free_space and sp_clean_db_free_space.  The sp_clean_db_free_space system stored procedure is used to clean up ghost records across all database files in a specified database.  This effects ghost records from INSERT/UPDATEs  that haven't already been cleaned up by background processes. The sp_clean_db_file_free_space performs this same task as sp_clean_db_free_space, except you specify it as the name suggest for a specific file. 

As you could imagine, these procedures are I/O intensive procedures.  I ran a small test on my AdventureWorks2008 database and found that it took about 35 seconds to run for a 262MB database. Looking at the stored procedure definition for sp_clean_db_free_space using EXEC sp_helptext, I saw that this procedure actually calls sp_clean_db_file_free_space for each file in the specified database.  Looking at the definition for sp_clean_db_file_free_space, under the covers it executes the undocumented command DBCC CLEANPAGE for each @dbid , @fileid, and @page.

BOL mentions that these procedures should be used for environments where physical security for the data or backups are at risk.  So this looks like another security tool in your toolbox that you can use in very specific security scenarios.  In the big security picture, I do that that if you are aware that your data or backups are at risk, you should try to address that problem in a more direct fashion via people, processes, and technology.

Comments

  • Anonymous
    February 19, 2009
    I have two blog subject follow-ups tonight: Follow-up #1... Regarding my " Page Cleaning with sp_clean_db_file_free_space

  • Anonymous
    September 23, 2010
    Hi Joseph, if we use the above system stored procedures, is there a chance that a database can get corrupted since these procedures are touching the pages and use cleanpage? Thank you AK

  • Anonymous
    September 23, 2010
    Hi AK, Corruption issues are most commonly caused by disk subsystem issues (I/O path, disk, etc).  Touching each data page with these procedures will not be an issue unless you have a pre-existing hardware, disk issue (but even then - any other I/O producing activity could also cause an issue to manifest - so these procedures aren't specifically associated with issues). I also did a quick bug/issue check today for these procedures, and found no correlation. Best Regards, Joe

  • Anonymous
    September 23, 2010
    Thanks for the confirmation.