SQL maintenance – shrinking the database

SQL Database Fragmentation

As you add and remove records from your SQL database, it will become fragmented, and have gaps of free space in it.

SQL is pretty good at reusing available free space, so your database does not necessarily keep growing in size.  However, it will benefit from occasionally being reorganized to reduce the fragmentation.

This is done with via the shrink database procedure.  When this is requested, the database is reorganized to reduce fragmentation, and the free space is released to the system.

I usually recommend shrinking the database once a week.  Typically this would be on a weekend day, when the database is not being used.  The reason I usually do this on a weekend, is that the process can take some time to run if the database is large.  Typically your office, or store, closes earlier on say Sunday, and there is a larger window for the task to be run.

If your database does not have a large number of inserts and deletes occurring, then shrinking it less frequently may be an option.  Once a month may be appropriate in this case.



Leave a Reply