Shrink Database With Low Priority

Reclaiming data space can be an important part of database maintenance over time and is a common need in hosted environments.  Shrinking a database is not recommended automatically and outside of controlled windows for the simple reason that it can cause concurrency issues.  Shrink database WLP (with low priority) introduced in SQL Server 2022 addresses this by waiting with less restrictive locking.  The functionality is similar to how the Alter Index Wait_AT_LOW_PRIORITY works.

               DBCC SHRINKDATABASE (2, 20, NOTRUNCATE)

               WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF)

The shrink will complete as soon as the command is sent as long as there are no blocking operations on this table. The WAIT_AT_LOW_PRIORITY indicates that if the shrink can not complete immediately it waits and holds low priority locks, allowing other operations that hold conflicting locks with the DDL statement to proceed with priority.

To date I have only seen this command demonstrated with the SELF option which means that the shrink operation will be exited without taking any action should it encounter any blocking.  I expect that None, and blockers will be added as we move to public preview but this is just speculation on my part. 

I will update this information as we get closer to GA.

Leave a Reply

Your email address will not be published.

X