April 17, 2012

sp_clean_db_file_free_space (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_clean_db_file_free_space(nvarchar @dbname
, int @fileid
, int @cleaning_delay)

MetaData:

   
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)

declare @flush nvarchar(1024)
set @flush = 'USE ' + @quoted_dbname + ' checkpoint'
--
-- 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
exec (@flush)
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
exec (@flush)
end

No comments:

Post a Comment

Total Pageviews