Shrink of data file - DBCC SHRINKFILE - taking a long time and not completing - stuck at 99% completion

Question

Post reply

Shrink of data file - DBCC SHRINKFILE - taking a long time and not completing - stuck at 99% completion

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
October 13, 2012 at 7:06 am

#265629

We had a large data purge recently where a large part of the data in a file was deleted.
File is at 140 GB size but only 40 GB is used space after the purge.
I am trying to shrink the file in small - 1GB - increments like this, so I can better monitor the progress of the shrinks:
dbcc shrinkfile (dbfile1, 148000);
go
dbcc shrinkfile (dbfile1, 147000);
go
dbcc shrinkfile (dbfile1, 146000);
go
...
Each of the steps above has taken about 30 seconds.
However, when I get to this step, it does not complete even after 20 minutes and I end up aborting it:
...
dbcc shrinkfile (dbfile1, 137000);
go
...
Checking the percent_complete gives me 99% but it is also stuck at that number and does not progress any further:
select percent_complete
from sys.dm_exec_requests
where session_id = 128
I have checked and defrag'ed the database following the successful shrinks.
Has anyone else faced this issue?
What could be the cause?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

I found out that this data file contains data from a text column.

Could this be the reason I am having trouble shrinking the file?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

Marios Philippopoulos (10/13/2012)
I found out that this data file contains data from a text column.
Could this be the reason I am having trouble shrinking the file?

why don't you re run it for a longer period, if theres a lot of data it could well take longer than 20 minutes

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Marios Philippopoulos (10/13/2012)
I found out that this data file contains data from a text column.
Could this be the reason I am having trouble shrinking the file?

Yes it is. The structure of LOB trees means that it takes much longer than 'normal' to move the pages around when you shrink.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

As i said, you'll need to be patient and run it for longer than 20 mins 😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

Here is an excellent blog explaining the issue:

http://www.sqlskills.com/blogs/paul/post/Why-LOB-data-make-shrink-run-slooooowly-(T-SQL-Tuesday-006).aspx

And Paul Randal's Shrink Alternative:

So what if you *do* need to run a shrink? For instance, if you've deleted a large proportion of a very large database and the database isn't likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
• Create a new filegroup
• Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON <filegroup> syntax, to move the tables and remove fragmentation from them at the same time
• Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Artoo22 (10/15/2012)
Here is an excellent blog explaining the issue:
http://www.sqlskills.com/blogs/paul/post/Why-LOB-data-make-shrink-run-slooooowly-(T-SQL-Tuesday-006).aspx
And Paul Randal's Shrink Alternative:
So what if you *do* need to run a shrink? For instance, if you've deleted a large proportion of a very large database and the database isn't likely to grow, or you need to empty a file before removing it?
The method I like to recommend is as follows:
• Create a new filegroup
• Move all affected tables and indexes into the new filegroup using the CREATE INDEX ... WITH (DROP_EXISTING) ON <filegroup> syntax, to move the tables and remove fragmentation from them at the same time
• Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

Thank you.

I ended up shrinking the file, but had to wait a few hrs.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Marios Philippopoulos (10/16/2012)
I ended up shrinking the file, but had to wait a few hrs.

😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉