January 4, 2017 at 9:16 am
Hi SSC,
Just a quick question about that proc sp_clean_db_free_space.
When I run it across a database it does take a long time.
If I stop the proc before its finished, when I start it again, will it start from where it left off? Or will it have to do the whole process from scratch again?
We have some issues with data on disk that will require us to run this proc over a DB as a once off. Its looking like it will take over 24 hours to complete.
We would like to run the proc for 4 hours a day for example, rather than leaving it to run for how ever many hours it will take to complete. This way we can ensure that the proc is run at a time with the least impact on production.
Anyone familiar with the proc and its workings?
Many thanks,
Rin
EDIT:
I now have another question regarding this SP.
Not sure if I should create a new thread or continue it in this one. A Mod can sort this out for me for what ever is appropriate.
Heres the scenario:
I have a database with a table with 2 columns: Data1 and Data2.
I input a row with data for both columns.
I then drop the first column data1 from the table.
I stop SQL Server and open the mdf file for the database in a text editor. I can search for the data inputted in the removed column and it finds it in the file.
Next, I start up sql server and run the sp_clean_db_free_space command over the db. Take a backup and stop the sql server service again.
Open up the mdf in a text editor and i can still see the old data from the removed column.
It seems the data remains in the file even after running the sp_clean proc.
I have tested it with deleting the table- after the sp_clean the data is gone from the mdf file.
I have tested it with deleting jsut the row- again, the sp_clean proc did its job and got rid of the data from the mdf file.
So how come it dosnt work in my scenario where I drop a column?
Any ideas would be greatly appreciated.
January 4, 2017 at 3:00 pm
Rin Sitah (1/4/2017)
Hi SSC,Just a quick question about that proc sp_clean_db_free_space.
When I run it across a database it does take a long time.
If I stop the proc before its finished, when I start it again, will it start from where it left off? Or will it have to do the whole process from scratch again?
We have some issues with data on disk that will require us to run this proc over a DB as a once off. Its looking like it will take over 24 hours to complete.
We would like to run the proc for 4 hours a day for example, rather than leaving it to run for how ever many hours it will take to complete. This way we can ensure that the proc is run at a time with the least impact on production.
Anyone familiar with the proc and its workings?
Many thanks,
Rin
You can check the stored procedures yourself and see what all is being done and how it's done.
You would start with
sp_helptext 'sp_clean_db_free_space'
and then you can see that this is calling sp_clean_db_file_free_space for each file in the database.
And then when you do
sp_helptext 'sp_clean_db_file_free_space'
and then you can see that this one is calling DBCC CLEANPAGE using the parameters @dbid, @fileid, @pageid to go through each page. DBCC CLEANPAGE is undocumented
When you go through the code, you'll notice that in each is using temp tables to keep track of the files and pages that it's cleaning up. And that data doesn't look like it is persisted anywhere. I would guess it's just going to do the same thing on each invocation and it doesn't look like it has a way to restart where it left off. Which makes sense as there could be new ghost records to clean up and you could restart weeks later.
You may want to see if you can do this by file instead with sp_clean_db_file_free_space since that would break it up some.
Sue
January 5, 2017 at 9:47 pm
This was removed by the editor as SPAM
January 6, 2017 at 7:01 am
I have updated the post with a scenario where sp_clean_db_free_space dosnt seem to work. Please see original post for details.
January 6, 2017 at 7:41 am
Rin Sitah (1/6/2017)
I have updated the post with a scenario where sp_clean_db_free_space dosnt seem to work. Please see original post for details.
You'd pretty much want to create a new thread when you have a different question, no one is going to move it for you.
But...your edit, second question:
I stop SQL Server and open the mdf file for the database in a text editor. I can search for the data inputted in the removed column and it finds it in the file.
Next, I start up sql server and run the sp_clean_db_free_space command over the db. Take a backup and stop the sql server service again.
Open up the mdf in a text editor and i can still see the old data from the removed column.
It seems the data remains in the file even after running the sp_clean proc.
I have tested it with deleting the table- after the sp_clean the data is gone from the mdf file.
I have tested it with deleting jsut the row- again, the sp_clean proc did its job and got rid of the data from the mdf file.
So how come it dosnt work in my scenario where I drop a column?
It's because it works different for columns. One of the caveats of using this. Dropped columns aren't touched in any of the page cleaning processes unless you perform some specific actions before trying to clean the pages.
You can find information on this and different actions to perform for different scenarios in this article:
http://michaeljswart.com/2015/05/its-hard-to-destroy-data/
Sue
January 6, 2017 at 8:41 am
The following link
https://msdn.microsoft.com/en-us/library/dd408732(v=sql.110).aspx
states the following information
Microsoft Technet
The length of time required to run sp_clean_db_free_space depends on the size of the file, the available free space, and the capacity of the disk. Because running sp_clean_db_free_space can significantly affect I/O activity, we recommend that you run this procedure outside usual operation hours.Before you run sp_clean_db_free_space, we recommend that you create a full database backup.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 8, 2017 at 10:27 pm
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply