Though rarely, we do need to run shrink commands in our lab database environments. The easiest way is to run the SHRINKDATABASE command is :
-- -- Sample database shrink command -- DBCC SHRINKDATABASE(SampleDB) GO
Above command shrinks the database to the point where there is no free space left. It may be useful in some situations, but a more common version is where we reduce the space to a pre-defined amount.
In the below example, we reduce the free space down to 25%.
-- -- Reduces the data &amp; log file such than only 25% free space remains -- DBCC SHRINKDATABASE(SampleDB, 25) GO
Nuance with OS
Free space created by moving used pages at the end of the physical file (MDF) to the free locations in the beginning or middle, to create contiguous free space at the end. The resultant free space is not necessarily returned to Operating System (that’s good).
For this behavior, use the below command:
Please note that the free space is not returned to the OS i.e. physical file size is not reduced; And this ‘NOTRUNCATE’ only applies to data files (not log file).
-- -- Shrink, but do not reduce the MDF file size -- DBCC SHRINKDATABASE(SampleDB, NOTRUNCATE) GO
Release to OS
When you want to release that free space to the OS, you want to use TRUNCATEONLY option. This option does not rearrange any pages; Just releases the existing free space to Operating System.
-- -- Shrink, but do not reduce the MDF file size -- DBCC SHRINKDATABASE(SampleDB, TRUNCATEONLY) GO
Before you shrink database, the follow command must be run to understand the amount of free space available; Is there any to be freed? We covered in a previous post here.
-- -- How much free space is available -- DBCC SHRINKDATABASE (SampleDB, TABULAR) GO
- Do not run this command in production databases. Careful measures need to be considered before we jump to this option, as this is the “last option” measure.
- Also, in production environment, do not set the AUTO_SHRINK database option to ON.
- Shrinking does not reduce fragmentation in tables & indexes.
Hope this helps,