ballpark time for shrinkfile

After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J