sp_clean_db_free_space

  • Lynn Pettis

    SSC Guru

    Points: 442141

    Does anyone know at what level sp_clean_db_free_space does a clean of space in the database.

    We have an issue that requires us to remove and clean some data from a table in our production database.

    I have read BOL and it really doesn't go into sufficient detail on this subject.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Lynn Pettis - Thursday, January 19, 2017 3:25 PM

    Does anyone know at what level sp_clean_db_free_space does a clean of space in the database.

    We have an issue that requires us to remove and clean some data from a table in our production database.

    I have read BOL and it really doesn't go into sufficient detail on this subject.

    I have some info from the procedure on SQL 2012.

    It executes sys.sp_clean_db_file_free_space for each file in sys.database_files where type = 0.
    It turn, sys.sp_clean_db_file_free_space calls DBCC CLEANPAGE for every page in the file being processed.  It fires a checkpoint every 10000 pages.
    I can't tell you what DBCC CLEANPAGE does under the hood.

    HTH

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Lynn Pettis - Thursday, January 19, 2017 3:25 PM

    Does anyone know at what level sp_clean_db_free_space does a clean of space in the database.

    We have an issue that requires us to remove and clean some data from a table in our production database.

    I have read BOL and it really doesn't go into sufficient detail on this subject.

    this is what goes on in the background, hope it helps...


    create procedure sys.sp_clean_db_file_free_space (
    @dbname sysname,
    @fileid int,
    @cleaning_delay int = 0)
    as
    begin
    SET NOCOUNT ON
    declare @quoted_dbname nvarchar(258)
    set @quoted_dbname=QUOTENAME(@dbname)

    --
    -- Parameter check
    -- @dbname
    --
    if (db_id(@dbname) is null)
    begin
    RAISERROR(15010, 16, -1, @quoted_dbname)
    return (1)
    end

    --
    -- security check
    -- only db_owner can execute this
    --
    declare @check nvarchar(1024)
    set @check = 'USE ' + @quoted_dbname +
      'if (is_member (''db_owner'') != 1)
    begin
    raiserror(14260, 16, -1)
      end'
    exec (@check)
    if @@error =14260
    begin
    return (1)
    end

    declare @page int
    set @page = 0
    declare @dbid int

    create table #continueclean (
    fileid int,
    page int)
    declare @sql as nvarchar(1024)
    set @sql = 'insert #continueclean (fileid, page) select file_id, size from ' + @quoted_dbname + '.sys.database_files where file_id=' + cast(@fileid as nvarchar) + ' and type=0'
    exec (@sql)

    select @dbid=db_id(@dbname)
    while exists(select * from #continueclean where fileid = @fileid and page > @page)
    begin
    dbcc cleanpage(@dbid , @fileid, @page)
    set @page = @page+1

    -- delay execution to throttle impact on system
    if (@cleaning_delay > 0)
    waitfor delay @cleaning_delay

    -- checkpoint periodically
    if @page % 10000 = 0
    begin
    dbcc flush('data', @dbid)
    end

    set @sql = 'update #continueclean set fileid=file_id, page=size from ' + @quoted_dbname + '.sys.database_files where file_id=' + cast(@fileid as nvarchar) + ' and type=0'
    exec (@sql)
    end
    drop table #continueclean
    dbcc flush('data', @dbid)
    end

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Lynn Pettis

    SSC Guru

    Points: 442141

    Not really helpful.

    If you delete data from a table would sp_clean_db_free_space satisfy a requirement that the data could not be recovered by forensic tools.  I am starting to think no and our best bet would be to migrate good data to a new database leaving questionable data behind in the source database.

  • Ed Wagner

    SSC Guru

    Points: 286958

    Lynn Pettis - Friday, January 20, 2017 9:28 AM

    Not really helpful.

    If you delete data from a table would sp_clean_db_free_space satisfy a requirement that the data could not be recovered by forensic tools.  I am starting to think no and our best bet would be to migrate good data to a new database leaving questionable data behind in the source database.

    I found myself thinking about this and my brain started going down the road of DBCC PAGE and DBCC WRITEPAGE.  It was a scary place. 😉  I'm not sure where to go from here, but it's an intriguing line of thought, albeit a dangerous one.  My initial thought is that this is not a good line of thought.  I guess I have something to think about over the weekend. 😀

  • Henrico Bekker

    One Orange Chip

    Points: 27652

    Lynn Pettis - Friday, January 20, 2017 9:28 AM

    Not really helpful.

    If you delete data from a table would sp_clean_db_free_space satisfy a requirement that the data could not be recovered by forensic tools.  I am starting to think no and our best bet would be to migrate good data to a new database leaving questionable data behind in the source database.

    OK, so I had to dig deeper. 
    I can't explain it better than - http://michaeljswart.com/2015/05/its-hard-to-destroy-data/

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply