Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shrink of data file - DBCC SHRINKFILE - taking a long time and not completing - stuck at 99% completion Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 7:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1372418
Posted Saturday, October 13, 2012 11:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1372441
Posted Saturday, October 13, 2012 12:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 6,745, Visits: 14,384
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"
Post #1372447
Posted Saturday, October 13, 2012 2:26 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:37 AM
Points: 40,610, Visits: 37,073
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 2008, MVP
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

Post #1372464
Posted Sunday, October 14, 2012 9:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 6,745, Visits: 14,384
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"
Post #1372510
Posted Monday, October 15, 2012 6:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:37 AM
Points: 997, Visits: 3,089
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

@SeanPearceSQL

About Me
Post #1372702
Posted Tuesday, October 16, 2012 4:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:24 AM
Points: 1,865, Visits: 3,620
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1373115
Posted Tuesday, October 16, 2012 5:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 6,745, Visits: 14,384
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"
Post #1373142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse