sp_clean_db_free_space

Question

sp_clean_db_free_space

Lynn Pettis

SSC Guru

Points: 442467
More actions
January 19, 2017 at 3:25 pm

#325764

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.

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

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

Ed Wagner SSC Guru Points: 287024 More actions · Answer 1

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

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: 442467 More actions · Answer 3

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: 287024 More actions · Answer 4

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. 😀

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 5

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