The time it takes to shrink a database is dependant on how large the
database files are, how much data is in the database, and how fragmented the
data is. There is no magical formula to determine time that I'm aware of.
By what you say below I would assume the first run, that was spinning
forever, lost its connection and wasn't doing anything any longer. If it
was I believe it would have blocked the second run.
I prefer using SHRINKFILE and do my database files independently. Also,
you should expect performance to degrade while this is going on because it
is shifting all the data in the database file. Only do it during off
hours. The device size won't change until the run is completely finished
with the database file.
Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************
-----Original Message-----
From: Juan Vera
[mailto:mssqldba-ezmlmshield-x20070926.[Email address protected]
Sent: Wednesday, June 29, 2005 4:38 PM
To: LazyDBA Discussion
Subject: Shrinking a Database, Part II
I wonder if someone can clarify a couple of points for me:
When I test DBCC SHRINKDB against a snapshot of a production database, to
get an accurate estimate of how long an outage I need to carve out to
complete this operation, the following happens:
1. the DBCC SHRINKDATABASE seems to run forever (60h and not finished??)
2. having made the mistake of losing the session where this was running, I
ran it again (yes, over top of the already running DBCC - oops) and it ran
in 30 seconds, doing nothing to the size, and generating no errors. The
earlier instance of DBCC continued to run without a hiccup.
3. While this first never-ending shrink is running, the database performance
deteriorates by factor of 3, but seems eminently usable.
4. so following the advice of some listers here, I set the database to
single user mode and re-ran. So far (5 hours), I see no change in device
size and it's not done
Can anyone give me an expectation for how long it will take to shrink this
database (or any database, for that matter, of a given size), and whether it
can be accomplished while the database is in service?
Juan Cristián Vera
Database Architect
Ecast, Inc.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and delete
the material from any computer.
MS Sql Server LazyDBA home page