3

Is there a way to approximate how long a shrink will take on a SQL Server database? Are there tools available that could provide some guesstimate?

We have very large databases, so it would be good to know how long the database would be unavailable for (even if only an approximate estimate in hours).

Thanks in advance!

Improve this question
1
  • Don't shrink MS SQL DBs unless you've got a good reason to do so. It's not 'normal', and should not be part of your maintenance routine.
    – Chris S
    Jul 8, 2010 at 12:42

3 Answers 3

Reset to default
4

Shrinking a database is rarely recommended as it causes index and disk fragmentation. If you do need to shrink a file the operation is an online operation and won't take the database offline at all.

Improve this answer
2

I don't beleive the database will be unavailable while a shrink is occurring. I believe this holds true for both running DBCC SHRINKDATABASE, DBCC SHRINKFILE, and shrinking the database and\or files from SSMS. The database is not put into single user mode while being shrunk. The only caveat I'm aware of is that the database cannot be shrunk while it's being backed up.

Improve this answer
2

The only valid reason i can think for performing a shrink is if you've turned table compression or have over allocated free space and are running out of disk space.

restore the database onto some other similar kit & perform the same operation.
Even if it's older hardware at least you have a baseline for comparison.

Have a look at running the shrink with the TRUNCATEONLY option, this is a much quicker operation & may release enough space without the need to perform page moves, which is what causes table locking.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .