why shrink a sqlserver 2005 database?

Question

What are the advantages and disadvantages of shrinking a database? Are there any performance implications?

Answer 1

• Advantage: less disk space used
• Disadvantage: more likely to run out of space

That's basically it - a shrunk DB with no free space will need to be 'grown' in order to complete almost any operation.

When SQL runs out of space in an MDF file it has to grow it, and that is very slow and expensive.

If you know that you've enough space in the file go ahead and shrink. Be careful of fragmented files as you will need to re-index afterwards if they are.

Basic example:

500MB database file, 400MB used. You could shrink it to 400MB, but the next operation will be very slow as SQL adds 40MB (10%, by default) to the size.

500MB database file, 50MB used. You're probably wasting space. Shrink it to 100MB and the DB will still have space to do lots of things.

500MB database file, 50MB used, but about to import millions of records. Leave it unshrunk. Check again after import.

Read only DBs should always be shrunk.