Interview Question of the Week #006 – Is Shrinking Database Good or Bad?

Is shrinking a SQL Server database good or bad? My short interview answer: routine shrinking is usually bad for performance, and a one-time shrink can make sense when a large amount of space has become permanently unnecessary.

Why I Would Not Schedule a Shrink

Deleting rows frees space inside a data file. SQL Server can reuse that space for later rows without asking Windows to grow the file again. Shrinking tries to return file space to the operating system. If the same workload soon needs the space, the file grows again. The shrink and regrowth perform work without solving a lasting capacity problem.

Moving pages during a data-file shrink can also increase index fragmentation. That’s why a weekly shrink job followed by a weekly index rebuild is a poor maintenance cycle. The rebuild may grow the file again, and next week’s shrink repeats the disruption.

Look at the Free Space First

Before anyone talks about shrinking, check how much of each file is used. This query only reads; it changes nothing:

SELECT name AS LogicalName,
       type_desc AS FileType,
       size / 128 AS SizeMB,
       FILEPROPERTY(name, 'SpaceUsed') / 128 AS UsedMB,
       (size - FILEPROPERTY(name, 'SpaceUsed')) / 128 AS FreeMB
FROM sys.database_files;

Run it in the database you’re asking about. Sizes are stored in 8 KB pages, so dividing by 128 gives megabytes. A data file with some free space is healthy; that space is room for next month’s rows.

When Would I Consider It?

Suppose an archive permanently removes a large part of a database and the disk space has a better use. First confirm that the space really will stay unused, identify which file holds it, and plan the operation for a suitable maintenance window. Check the file’s expected future size and free space after the operation. A log file has different reasons for retaining space, so diagnose log reuse separately rather than assuming a data-file shrink rule applies to it.

The interview answer is a decision, not a command to run blindly. Keep reusable free space for ordinary growth. Return it only when the reduction is durable and the cost is justified.

Free space in a data file is not waste, it is room to grow, and shrinking it on a schedule only buys the same growth again.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.