By Tanayankar Chakraborty & Dimitri Furman
Issue
We recently worked on a customer case involving challenges with shrinking an Azure SQL Database (Hyperscale). The primary concern was that the shrink operation was progressing slowly and was not reclaiming storage space as expected. In some scenarios, customers also encountered error messages during the shrink operation.
Observed Error
The cx noticed this error after executing the shrink command:
Sql error number: 1222. Error Message: Lock request time out period exceeded.
Lock request time out period exceeded.
Lock request time out period exceeded.
Lock request time out period exceeded.
In some cases, the operation was aborted with the following message:
The shrink operation was aborted because a page to be moved by shrink is in use by an active transaction on the primary replica or on one or more secondary replicas. Retry shrink later.
Recommendations and Mitigation Steps
Here are some of the Recommendations to be followed while handling the DB shrink of a Large Azure SQL DB:
The following best practices are recommended when performing shrink operations on large Azure SQL Database Hyperscale databases:
- Assess Used vs. Allocated Space - Before initiating the shrink operation, review the used and allocated space for each data file and document the results:
SELECT file_id,
CAST(SUM(FILEPROPERTY(name, 'SpaceUsed')) AS bigint) * 8 / 1024. AS space_used_mb,
CAST(SUM(size) AS bigint) * 8 / 1024. AS space_allocated_mb
FROM sys.database_files
WHERE type_desc = 'ROWS'
GROUP BY file_id;
- Shrink Using TRUNCATEONLY -
Run DBCC SHRINKFILE with the TRUNCATEONLY option for each data file. This can quickly reclaim unused space at the end of the file when applicable:
DBCC SHRINKFILE (<file_id>, TRUNCATEONLY);
To retrieve all applicable file IDs:
SELECT file_id
FROM sys.database_files
WHERE type_desc = 'ROWS';
- Validate Space Reclamation - Re-run the space usage query to confirm whether the allocated space has been reduced.
- Use Incremental Target Sizes -
If unused allocated space remains, specify a target size in gradual increments. For example, if a file is 1 GB with only 100 MB used, shrink the file incrementally:
DBCC SHRINKFILE (1, 900);
DBCC SHRINKFILE (1, 800);
Continue until the desired size is reached (The value above i.e. 800 or 900 is in MB).
Additional Best Practices:
1. Multiple DBCC SHRINKFILE operations can be run concurrently on the database, which may help reduce overall execution time. The problem is if we increase the number of parallel shrinks too much, they start blocking & deadlocking each other which is counterproductive. In such cases, using the WAIT_AT_LOW_PRIORITY option in DBCC SHRINKFILE can mitigate locking issues.
2. Shrink progress can be monitored using the following query:
SELECT command,
percent_complete,
status,
wait_resource,
session_id,
wait_type,
blocking_session_id,
cpu_time,
reads,
CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');
3. For future shrink operations, we recommend specifying a target size equal to space used plus approximately 3 GB. Very small target size windows often result in longer execution times and reduced effectiveness due to repeated preprocessing.
4. Perform shrink operations during off-business hours or a planned maintenance window, as active workloads during peak hours can cause blocking and timeouts.
5. Databases with large LOB or columnstore data may require additional time to shrink. Rebuilding columnstore indexes can improve shrink effectiveness, as they internally use LOB storage. Also, as stated in this document, If the DB has objects with LOBs, compacting them before shrink can help.
6. Index rebuilds should not be executed concurrently with shrink operations. Index rebuilds require free space and may trigger file growth, which counteracts shrink efforts. Rebuild indexes first, then perform shrink operations. If page density is low Rebuild, If page density is high, no need to rebuild.
7. New data will always be distributed to the file with the max empty space according to the proportional fill algorithm. This is explained in detail here. There is no one-to-one mapping between tables and files, and deadlocks may sometimes occur during parallel shrink operations.
8. It must be noted that index rebuilds can improve shrink effectiveness, especially if page density is low. The key point here is that you need to rebuild before shrink. Hence, it is best to focus on rebuilding indexes with less than 80% page density, particularly for large tables, and to use sampled mode ( Please check the DMV sys.dm_db_index_physical_stats here for more info on sampled mode) for index statistics to avoid long-running queries. Please use the script here to find out the page density.
9. Columnstore indexes internally use LOB data types and can hinder shrinking if not rebuilt. Prioritizing columnstore index rebuilds can help as data at the end of the files can prevent truncation.
10. If needed, attempt shrink operations on two to three files at a time and adjust parallelism cautiously while monitoring for blocking or deadlocks.
References
Shrink for Azure SQL Database Hyperscale is now generally available | Microsoft Community Hub
DBCC SHRINKFILE (Transact-SQL) - SQL Server | Microsoft Learn
Database file space management - Azure SQL Database | Microsoft Learn
Page and Extent Architecture Guide - SQL Server | Microsoft Learn
sys.dm_db_index_physical_stats (Transact-SQL) - SQL Server | Microsoft Learn
Microsoft