First thing first – If you have a choice to shrink the database and not shrink it, find reasons to not shrink it. Database administrators are often tasked with managing the size of a SQL Server database. The DBCC SHRINKDATABASE command is a powerful tool in their arsenal, allowing them to reduce the physical size of the data and log files in the specified database. However, as with all powerful tools, it should be used judiciously to avoid potential performance problems. In this blog post, we will explore the DBCC SHRINKDATABASE command and its options, focusing on the WAIT_AT_LOW_PRIORITY feature introduced in SQL Server 2022 and later versions.
Here are a few additional blogs where I have written about why Shriking should be used sparsely or not.
- SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance
- SQL SERVER – SHRINKDATABASE For Every Database in the SQL Server
- SQL SERVER – What the Business Says Is Not What the Business Wants
Understanding DBCC SHRINKDATABASE
The DBCC SHRINKDATABASE command helps reduce the data size and log files in a specified database. It takes the database name or ID as an argument, with an optional target percent that denotes the free space you’d like to maintain in the database file after it has been shrunk.
The command also provides two special options: NOTRUNCATE and TRUNCATEONLY. The NOTRUNCATE option moves assigned pages from the file’s end to unassigned pages in the front of the file, effectively compacting the data within the file but not releasing the free space at the end back to the operating system. On the other hand, the TRUNCATEONLY option releases all free space at the end of the file to the operating system without moving any pages within the file.
Introducing WAIT_AT_LOW_PRIORITY
The WAIT_AT_LOW_PRIORITY option is a feature that can be used along with the DBCC SHRINKDATABASE command to manage lock contention more effectively. This feature is especially useful when a shrink operation needs to acquire a schema modify lock (Sch-M), which can often cause significant blocking in the database.
When a shrink command is executed in WAIT_AT_LOW_PRIORITY mode, new queries requiring schema stability (Sch-S) locks are not blocked by the waiting shrink operation until the shrink operation stops waiting and starts executing. If a new shrink operation in WAIT_AT_LOW_PRIORITY mode cannot obtain a lock due to a long-running query, the shrink operation will eventually timeout after 1 minute by default and will exit with no error.
The WAIT_AT_LOW_PRIORITY option takes two possible values for the ABORT_AFTER_WAIT argument: SELF and BLOCKERS. The SELF option is the default, and it allows the shrink operation to exit without taking any action if it’s being blocked. The BLOCKERS option, on the other hand, kills all user transactions that are blocking the shrink operation, allowing it to continue.
DBCC SHRINKDATABASE (AdventureWorks2019, 100) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = BLOCKERS);
Concluding Thoughts
Managing the size of a SQL Server database is an essential part of a database administrator’s job. DBCC SHRINKDATABASE, when used judiciously, can help manage the physical size of the database effectively. The WAIT_AT_LOW_PRIORITY option further enhances this by managing lock contention effectively and ensuring that database shrink operations do not unnecessarily block other queries.
However, it’s also worth noting that the constant database shrinking and growth can lead to fragmentation and adversely impact performance. Therefore, it’s essential to have a well-considered strategy for managing database size, and tools like DBCC SHRINKDATABASE should be a part of that strategy, not the entire strategy.
Always remember, “With great power comes great responsibility!“
You can always reach out to me on Twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
