ShrinkFile on Varbinary column
-
Hi,
I need a help. am doing shirnk on 160gb( but data is just 30gb) to claim space. This db has Varbinary columns.
Shrinkfile for 1GB (Dbcc shrinkfile ('Filename', 159) takes 35 minutes and i had to kill it since it blocked other session.
have you anyone faced this problem when shrinking varbinary data? Do you have any better idea to reclaim space.?
thanks
-
Shriking a 160GB file directly to 30GB takes very long time and causes blocking.
One work around is to shink the file in smaller chunks.
E.g. shink the file by 2GB at a time.
Shinking by 2-5GB will take only couple of minutes and don't cause blocking issue.
-
LOB data of any form makes for a very, very slow shrink, just because of the way the LOB data is stored.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Suresh,
I tried for 1gb space. It was about 35 minutes and blocked others. See my posting again.
Gail,
Will You advice any workaround to reclaim space?
-
SQL Show (6/12/2013)
Gail,Will You advice any workaround to reclaim space?
Patience. 🙂
Why are you shrinking anyway? Has the database been permanently reduced in size? Will the space you're trying to reclaim never be needed by the database again?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/12/2013)
SQL Show (6/12/2013)
Gail,Will You advice any workaround to reclaim space?
Patience. 🙂
Why are you shrinking anyway? Has the database been permanently reduced in size? Will the space you're trying to reclaim never be needed by the database again?
I prefer Paul Randal's method:
1. Create new filegroup.
2. Create new file on new filegroup. Set this to your required size.
3. Rebuild all indexes with the new filegroup as a target.
4. Shrink the old empty file.
-
Sean Pearce (6/12/2013)
I prefer Paul Randal's method:1. Create new filegroup.
2. Create new file on new filegroup. Set this to your required size.
3. Rebuild all indexes with the new filegroup as a target.
4. Shrink the old empty file.
And wait and wait and wait while the shrink moves around the LOB data that's still in the old filegroup, in the file that's been shrunk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (6/12/2013)
Sean Pearce (6/12/2013)
I prefer Paul Randal's method:1. Create new filegroup.
2. Create new file on new filegroup. Set this to your required size.
3. Rebuild all indexes with the new filegroup as a target.
4. Shrink the old empty file.
And wait and wait and wait while the shrink moves around the LOB data that's still in the old filegroup, in the file that's been shrunk.
Index rebuild has moved the lob data so the shrink on an empty file is near instantaneous.
-
Sean Pearce (6/12/2013)
GilaMonster (6/12/2013)
Sean Pearce (6/12/2013)
I prefer Paul Randal's method:1. Create new filegroup.
2. Create new file on new filegroup. Set this to your required size.
3. Rebuild all indexes with the new filegroup as a target.
4. Shrink the old empty file.
And wait and wait and wait while the shrink moves around the LOB data that's still in the old filegroup, in the file that's been shrunk.
Index rebuild has moved the lob data so the shrink on an empty file is near instantaneous.
Index rebuilds don't move the LOB data. Just the index b-tree.
The only two ways to move the LOB data are to recreate the table (insert into ... select from ...; drop table ...) and a partitioning trick that I don't recall offhand but I think is described on Kimberly Tripp's blog
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
This server is newly acquired from another team. So I am not sure what their trend was and this client very money sensitive for a disk addition. This application is 24/7 basis and they are adamant aginst a downtime in order to run shrink file.
Currently we are running out of space, 180gb drive is holding 160gb datafile. 🙂 .
Thanks for tip to move into a new filegroup. Again the problem downtime. am going to escalate this to my mangement.
-
GilaMonster (6/12/2013)
SQL Show (6/12/2013)
Gail,Will You advice any workaround to reclaim space?
Why are you shrinking anyway?
This drive is shared by another instance of sql(agree not a good practice) which is also oltp.
Has the database been permanently reduced in size? Will the space you're trying to reclaim never be needed by the database again?
No. Reclaimed space may not be used again in near future. Here we have one more trouble. In just 48 hours, database size went upto 160gb and archival also was done immediatly. we are still trying to identify what caused this much sudden growth.
Thanks every one.
-
GilaMonster (6/12/2013)
Index rebuilds don't move the LOB data. Just the index b-tree.The only two ways to move the LOB data are to recreate the table (insert into ... select from ...; drop table ...) and a partitioning trick that I don't recall offhand but I think is described on Kimberly Tripp's blog
Surprisingly, Gail is correct and my chance of "stump the master" has gone up in smoke.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply