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!

Fuzzy Purple Monkey
  • 255
  • 1
  • 3
  • 7
  • 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 08 '10 at 12:42

3 Answers3

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
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.

joeqwerty
  • 108,377
  • 6
  • 80
  • 171
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.

Nick Kavadias
  • 10,758
  • 7
  • 36
  • 47