How do I shrink Azure SQL database allocated space down to value closer to used space?

Question

After archiving data, my Azure SQL database has 1.5 TB allocated space and 500GB used. How do I shrink the database save save on costs?

Answer 1

The AI answer is reasonably accurate, but I'd like to add a few things:

1. If you have LOB data the shrink can take a loooooong time.
2. Shrinking introduces fragmentation, so you may want to rebuild indexes after the operation.

And I like to emphasise what the AI says about headroom. 650 GB seems like a reasonable target.

Answer 2

Shrink each data file incrementally with DBCC SHRINKFILE, leaving reasonable growth headroom above the 500-GB usage, and then—unless the database is Hyperscale—reduce its configured maximum storage to realize a billing reduction.

1. Measure each data file

Connect to the target user database, not master, and capture the file-level baseline:

SELECT file_id,
       name,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8 / 1024. AS used_mb,
       CAST(size AS bigint) * 8 / 1024. AS allocated_mb,
       CAST(max_size AS bigint) * 8 / 1024. AS max_size_mb
FROM sys.database_files
WHERE type_desc = 'ROWS';

Deleted space remains available inside the files and is not automatically returned to the service. The difference between allocated and used space is the maximum potentially reclaimable amount.^1^

2. Shrink incrementally

For a 1.5-TB allocation, do not attempt to jump directly to 500 GB. Choose a final target with operating headroom—for example, approximately 600–700 GB if workload growth and maintenance requirements permit—and shrink each file in stages.

For a single data file with file_id = 1, an example progression in MB is:

DBCC SHRINKFILE (1, 1250000);
DBCC SHRINKFILE (1, 1000000);
DBCC SHRINKFILE (1, 800000);
DBCC SHRINKFILE (1, 665600); -- 650 GiB final target

Run one command at a time and rerun the baseline query after each step. Every target must remain above that file’s current used_mb. If there are multiple data files, divide the desired total target between them according to each file’s used space.

A multi-hundred-GB or multi-TB shrink can run for hours, days, or weeks. Incremental targets preserve more progress if maintenance interrupts the operation. Run it during lower activity because shrinking consumes CPU and I/O and can temporarily block workload queries.^1^

For a quick, limited first reduction, this removes only unused space already located at the end of a file:

DBCC SHRINKFILE (1, TRUNCATEONLY);

TRUNCATEONLY does not move pages, so it is faster but usually cannot reclaim all free space. Skip it if proceeding directly with the full incremental shrink.

Do not normally shrink the transaction log: Azure SQL Database automatically shrinks it as needed. Also avoid enabling auto-shrink or scheduling regular shrink jobs; shrinking is intended for exceptional events such as this major archival operation.^1^

3. Apply the billing-related change

The required action depends on the service tier:

• General Purpose or Business Critical, vCore model: Billing is based on the configured maximum storage, not current used or allocated space. After the file allocation is below the intended limit, scale the database and reduce its maximum data size. Shrinking alone does not lower this storage charge.^2^
• Hyperscale: Maximum storage is not configurable; billing is based on allocated data storage. Reclaiming the roughly 1-TB unused allocation can therefore reduce the data-storage charge directly.^2^
• DTU model: Only storage above the amount included with the selected tier is charged separately. Whether reducing storage lowers cost depends on whether the new maximum falls below an additional-storage billing increment.^3^

For a single database, change the storage configuration through Azure portal → SQL database → Compute + storage, or use T-SQL, PowerShell, Azure CLI, or REST. Configure a limit above the post-shrink allocation and expected near-term growth.^3^

Finally, rerun the file query and verify that allocated_mb is near the chosen target. Shrinking moves pages and can increase index fragmentation, so assess affected indexes afterward and rebuild or reorganize only where the workload requires it. Do not shrink all the way to 500 GB if that leaves no room for index maintenance, temporary operations, or normal growth.

---

References

1. Manage file space for databases in Azure SQL Database
2. vCore purchasing model - Azure SQL Database
3. Scale single database resources in Azure SQL Database