How to Shrink an MS SQL Database

To reduce the disk usage of an MS SQL database, a shrink operation can be performed. It's best not to shrink your database however, as this causes index fragmentation which affects performance.

If you need to reduce your database size, an ideal time to shrink your database is after you have deleted a lot of old or unneeded data from your database.  For example, many ecommerce applications have maintenance tasks that you can run periodically to clear out old data. The shrink operation will then free up the unused space.  You do not want to shrink your database often however, and many database experts advise against shrinking all together, so this may be something you want to discuss with your developer. 

 

Microsoft states the following best practices relating to shrinking a database:

 

Best Practices:
 
A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate

Add Feedback