How to Manage Database Size with DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY

By default, the DBCC SHRINKDATABASE operation can be resource-intensive, potentially affecting concurrent database operations. That’s why the WAIT_AT_LOW_PRIORITY option was introduced in SQL Server. When specified, this option allows the shrink operation to yield resources to higher-priority tasks when necessary.

In this article, we’ll explore how to use DBCC SHRINKDATABASE and WAIT_AT_LOW_PRIORITY for managing your database size.

How Does DBCC SHRINKDATABASE Work?

DBCC SHRINKDATABASE operates on individual data and log files within a database, shrinking data files from the end and log files as a contiguous pool. The process calculates a target size based on a specified target_percent, representing the desired free space after shrinking.

For example, with a target_percent of 25 for a database named mydb, the data file’s target size is calculated as 8 MB (6 MB data + 2 MB free space). A target_percent of 30 allows shrinking the data file to 30 percent free space. 

In log files, target_percent determines the target size for the entire log pool, translated to individual log files. DBCC SHRINKDATABASE immediately attempts to shrink each log file. Success truncates the file; otherwise, an informational message guides relocating the logical log. 

How Do I Shrink a Database in DBCC?

As mentioned earlier, shrinking the database is achievable through the DBCC SHRINKDATABASE command. The general syntax for it is as follows.

Syntax:

DBCC SHRINKDATABASE   

( database_name | database_id | 0   

     [ , target_percent ]   

     [ , { NOTRUNCATE | TRUNCATEONLY } ]   

)  

Here’s what the arguments mean.

‘database_name | database_id | 0’: You can specify the name or ID of the database you want to shrink. Alternatively, you can use 0 to indicate the current database.

‘[ , target_percent ]’: This is an optional parameter where you can specify the target percentage of free space in the database after the shrink operation. If not specified, the default is 0, meaning the Database Engine shrinks the database to its minimum size. 

NOTRUNCATE: It moves assigned pages from the end to unassigned pages at the front, compacting data within the file. When using NOTRUNCATE, free space at the file end isn’t released to the operating system, and the file’s physical size remains unchanged. This command is specific to data files and doesn’t affect log files.

TRUNCATEONLY: Frees up space at the file end, returning it to the operating system. It doesn’t rearrange pages, causing the data file to shrink only to the last assigned extent. If you specify target_percent with TRUNCATEONLY, it gets disregarded. Also, using DBCC SHRINKDATABASE with TRUNCATEONLY impacts the database transaction log file only. To truncate the data file, use DBCC SHRINKFILE instead.

Example: 

DBCC SHRINKDATABASE (UserDB, 10);

GO

In this example, the data and log files in the UserDB user database are scaled down to achieve 10 percent free space in the database. 

How to Use WAIT_AT_LOW_PRIORITY

In WAIT_AT_LOW_PRIORITY mode, a shrink command lets new queries needing Sch-S locks proceed without blocking until the shrink operation starts executing. The shrink operation begins when it secures a schema modify lock (Sch-M). 

If a new shrink operation can’t get a lock due to a long query, it times out after 1 minute by default and exits without errors. This timeout occurs if the shrink operation can’t obtain the Sch-M lock because of concurrent queries or queries holding Sch-S locks. 

The following example aims to shrink the AdventureWorks2022 database files, ensuring 20% free space. If a lock isn’t obtained within a minute, the operation aborts. 

DBCC SHRINKDATABASE ([AdventureWorks2022], 20) WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = SELF);

Limitations of DBCC SHRINKDATABASE

In cases where the data file of mydb contains 7 MB of data, specifying a target_percent of 30 enables the file to be shrunk to 30 percent free space. However, a target percentage of 40 does not trigger a shrink for the data file because the current total size of the file cannot accommodate the desired amount of free space.

This limitation can be understood as follows: 40 percent desired free space plus 70 percent occupied space in the data file (7 MB out of 10 MB) exceeds 100 percent. Consequently, any target percentage greater than 30 will not result in the shrinkage of the data file.

Besides, a log file can only shrink to a virtual log file boundary, dynamically determined by the Database Engine during creation or extension. Shrinking below this boundary may not be possible, even if unused.

Concurrency Issues With DBCC SHRINKDATABASE

Shrink commands can cause concurrency challenges in active environments, especially with long-running queries acquiring schema stability locks (Sch-S). These locks hinder shrink operations, requiring schema modify locks (Sch-M) for Index Allocation Map (IAM) pages. SQL Server 2022 introduced the shrink wait at low priority (WLP) feature, using WAIT_AT_LOW_PRIORITY mode to enhance concurrency management during shrink operations.

What Is the Best Practice to Shrink SQL Database?

Consider the following when you want to shrink your SQL database.

• Shrink a database after actions like truncating or drop tables create unused space.
• Day-to-day operations need some free space; repeated shrinking may be counterproductive.
• Repeated shrinking can hinder performance due to auto-grow events required for file expansion.
• Shrinking a database doesn’t preserve index fragmentation and often increases it.
• Avoid enabling AUTO_SHRINK unless a specific requirement exists.

What is the difference between DBCC SHRINKDATABASE and SHRINKFILE?

You have the option to use either the DBCC SHRINKDATABASE or the DBCC SHRINKFILE command. The former attempts to shrink all data and log files, while the latter allows you to shrink a specific file of your choosing. It’s not necessary to shrink all files simultaneously.

Does shrinking databases improve performance?

Moving data to shrink a file can lead to scattered placement in the file, causing index fragmentation. This fragmentation may impact the performance of queries searching a range within the index.

Conclusion

Managing database size is a balancing act, and DBCC SHRINKDATABASE with WAIT_AT_LOW_PRIORITY provides a nuanced approach. As you navigate the intricacies of storage optimization, remember that thoughtful implementation is key. If you have feedback, questions, or insights on this topic, I’d love to hear from you. Thank you for reading!