May 7, 2009 at 3:01 pm
I am trying to shrink mdf file which is 800 GB in size and it's running for three days and still going on.
I also checking DISKIO and it's increasing, means that this process is still going on. I can see the self blocking in that process which I belive is not affecting anything.
Any help will be appreciated.
May 7, 2009 at 3:14 pm
Have you tried running sp_who2 to see if there's anything blocking the shrink process, or killed all processes using that dB?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
May 7, 2009 at 3:25 pm
I already ran sp_who2 and there is not blocking except self blocking of that process.
This shrinking process is blocking by itself. there is no other blocking going on.
I am thinking of shrinking datatabase first nstead of shrinking file to see if it will do any help.
May 7, 2009 at 3:44 pm
I think I solved the issue. I am tyring to shink in a smaller chunks, like 50 MB and it's working.
DBCC SHRINKFILE (DataFil1, 25000)
go
DBCC SHRINKFILE (DataFil1, 25950)
go
DBCC SHRINKFILE (DataFil1, 25900)
go
...and so on...
go
DBCC SHRINKFILE (DataFil1, 9000)
May 7, 2009 at 9:45 pm
If you are seeing self blocking for extended period of times - consider setting 'max degree of parallelism' to 1. But to take its affect on processes already running you might have to kill and restart. Your process has already run for 3 days, which means you might have to risk it all going to waste, specially if 'MDP' changes don't work as suggested. No easy answer here...
May 7, 2009 at 9:56 pm
It is often better to shrink database files in small increments so that it can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and makes it easier to interrupt without losing all progress.
Take a look at the script on the link below for an example of how to do this.
Shrink DB File by Increment to Target Free Space
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply