DBCC SHRINKDATABASE QUESTION

I am archiving about 25 gigs of images from our OLTP database to an archive database. After archiving these images(table which contains a column datatype image), there is about 25 gigs of unallocated space reported by EM. I run the normal Shrink database routine
DBCC SHRINKDATABASE
( database_name [ , target_percent ] )
--I set the target percent low(about 5%), current db size after archive is 75 gigs so it should shrink to about 79 gigs...
and instead of releasing the free space to the operating system the routine actually increases the used space of the db to fill almost all the newly freed space. Basically I remove 25 gigs of data, run the routine and now magically the database grows 25 gigs instead of freeing up 25 gigs.(now the db is back to the orginal size before archive process!!) I've only experienced this when dealing with image datatypes. I am wondering if the way images are stored has something to do with this unexpected behavior. If anybody has any insight please let me know. ThanksYes this is a know issue that it can happen sometimes but I don't know if
there is a KB regarding this or not. Try using DBCC CLEANTABLE and see if
that helps. If not then you might have to BCP out all the data from that
table, truncate it and bcp it back in.
--
Andrew J. Kelly
SQL Server MVP
"mike petanovitch" <mpetanovitch@.hotmail.com> wrote in message
news:5DB04B5D-64C6-4422-AEB4-A1E99BEE8574@.microsoft.com...
> I am archiving about 25 gigs of images from our OLTP database to an
archive database. After archiving these images(table which contains a
column datatype image), there is about 25 gigs of unallocated space reported
by EM. I run the normal Shrink database routine:
> DBCC SHRINKDATABASE
> ( database_name [ , target_percent ] )
> --I set the target percent low(about 5%), current db size after archive is
75 gigs so it should shrink to about 79 gigs...
> and instead of releasing the free space to the operating system the
routine actually increases the used space of the db to fill almost all the
newly freed space. Basically I remove 25 gigs of data, run the routine
and now magically the database grows 25 gigs instead of freeing up 25
gigs.(now the db is back to the orginal size before archive process!!)
I've only experienced this when dealing with image datatypes. I am
wondering if the way images are stored has something to do with this
unexpected behavior. If anybody has any insight please let me know. Thanks

dbcc shrink taking forever

Im trying to shrink a data file with 5 gigs free. Nobody is in the db but Im
40 minutes down the road with not light at the end of the tunnel. Is there a
faster way?
That's not an unreasonable amount of time for it to take depending on your
hardware and the fragmentation level of the free space in the database. I
would give it a couple of hours at least. In SQL Server 2005 shrink has
progress reporting which will help with this type of problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"chris" <chris@.noemail.com> wrote in message
news:#y5fNrtLEHA.2716@.tk2msftngp13.phx.gbl...
> Im trying to shrink a data file with 5 gigs free. Nobody is in the db but
Im
> 40 minutes down the road with not light at the end of the tunnel. Is there
a
> faster way?
>

dbcc shrink taking forever

Im trying to shrink a data file with 5 gigs free. Nobody is in the db but Im
40 minutes down the road with not light at the end of the tunnel. Is there a
faster way?That's not an unreasonable amount of time for it to take depending on your
hardware and the fragmentation level of the free space in the database. I
would give it a couple of hours at least. In SQL Server 2005 shrink has
progress reporting which will help with this type of problem.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"chris" <chris@.noemail.com> wrote in message
news:#y5fNrtLEHA.2716@.tk2msftngp13.phx.gbl...
> Im trying to shrink a data file with 5 gigs free. Nobody is in the db but
Im
> 40 minutes down the road with not light at the end of the tunnel. Is there
a
> faster way?
>

dbcc shrink taking forever

Im trying to shrink a data file with 5 gigs free. Nobody is in the db but Im
40 minutes down the road with not light at the end of the tunnel. Is there a
faster way?That's not an unreasonable amount of time for it to take depending on your
hardware and the fragmentation level of the free space in the database. I
would give it a couple of hours at least. In SQL Server 2005 shrink has
progress reporting which will help with this type of problem.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"chris" <chris@.noemail.com> wrote in message
news:#y5fNrtLEHA.2716@.tk2msftngp13.phx.gbl...
> Im trying to shrink a data file with 5 gigs free. Nobody is in the db but
Im
> 40 minutes down the road with not light at the end of the tunnel. Is there
a
> faster way?
>