Though rarely, we do need to run shrink commands in our lab database environments. The easiest way is to run the SHRINKDATABASE command is :

Simple Option

Targetted Shrink

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%.

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).

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.

Important Insight

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.

Note:

• 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,