March 5, 2012 at 4:02 pm
Hi,
I am trying to shrink a datafile using a dbcc shrinkfile command it just keeps on running.
It has not hung up though. I can see that the physical io and cpu figures used by the spid running the command being incremented. Also no applicatons are running and threrefore there is blocking either. It is a 240 GB file with about 110 GB FREE space available inside the data file. Trying to shrink only 1 GB at a time does not help either.
If anyone has faced this problem and knows how to get around it Pl. let me know.
March 5, 2012 at 4:24 pm
Is there a need to shrink the file? Do you know all the ugly things that shrinkfile does to your indexing etc? Unless you absolutely must do it, I would recommend leaving the file alone.
March 5, 2012 at 4:51 pm
as a part of reclaiming the space we are doing this activity and we have 6gb free space left on this drive. So need to do this activity.
actually user asking to purge the old records for one of the db so we have created new table and we have moved 2011 records to new table and we have truncated the old table but we have not reclaimed the space but when we are checking the db properties the unused space is like 110gb for this space we are shrunk the data file but no luck, its taking too long to process the query.
can pls advice on this.
March 5, 2012 at 4:56 pm
as a part of reclaiming the space we are doing this activity and we have 6gb free space left on this drive. So need to do this activity.
actually user asking to purge the old records for one of the db so we have created new table and we have moved 2011 records to new table and we have truncated the old table but we have not reclaimed the space but when we are checking the db properties the unused space is like 110gb for this space we are shrunk the data file but no luck, its taking too long to process the query.
can pls advice on this.
March 6, 2012 at 12:36 am
If IO and CPU are increasing then it is working. There are some database structures (heaps, LOB columns) that cause shrink to run slow, you just have to be patient.
Don't forget to rebuild indexes afterwards.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 10:52 am
I second GilaMonster. Very large DB files seem to take forever to shrink. I have had them take 2 days, myself. This is a great argument for a clustered index on every table.
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply