Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
    
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 '10 at 12:42

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.