Lynn Pettis - Thursday, January 19, 2017 3:25 PM
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