• 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