Cari Blog Ini

22 Maret 2012

Best Practices of SHRINK DATABASE

Consider the following information when you plan to shrink a database:

1. A shrink operation is most effective after an operation
that creates lots of unused space,
such as a truncate table or a drop table operation.

2. Most databases require some free space to be available for regular day-to-day operations.
If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space
that was shrunk is required for regular operations.
In these cases, repeatedly shrinking the database is a wasted operation.

3. A shrink operation does not preserve the fragmentation state of indexes in the database,
and generally increases fragmentation to a degree.
This is another reason not to repeatedly shrink the database.

4. Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.


Source:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/fc976afd-1edb-4341-bf41-c4a42a69772b.htm