Add WAIT_AT_LOW_PRIORITY support to Invoke-DbaDbShrink

[ReeceGoding]

Summarize Functionality

Invoke-DbaDbShrink calls DBCC SHRINKFILE. As of SQL Server 2022, WAIT_AT_LOW_PRIORITY is supported by DBCC SHRINKFILE. We should give Invoke-DbaDbShrink this functionality. For the same reason that we provide StatementTimeout and the option to Invoke-DbaDbShrink in steps, WAIT_AT_LOW_PRIORITY is quite handy.

Is there a command that is similiar or close to what you are looking for?

No

Technical Details

This will be tricky for two reasons:

• We currently use the SMO shrink method, called as $file.Shrink($shrinkSizeKB.Megabyte, $ShrinkMethod). We would need to re-write to use T-SQL.
• Nothing else in dbatools supports WAIT_AT_LOW_PRIORITY, so we would have to figure out how calling it from PowerShell should look. It effectively has three parameters: if it should be on or off, the max duration in minutes, and what to abort.

[andreasjordan]

@claude what is your opinion?

[claude]

Claude finished @andreasjordan's task in 1m 1s —— View job

---

Opinion on Adding WAIT_AT_LOW_PRIORITY to Invoke-DbaDbShrink

TL;DR: This is a well-scoped, feasible enhancement. I support it and think the implementation path is clear.

---

Current State

The shrink operation is done via SMO on lines 315 and 324:

$file.Shrink($shrinkSizeKB.Megabyte, $ShrinkMethod)

SMO's DatabaseFile.Shrink() just calls DBCC SHRINKFILE under the hood, but with no way to pass WITH WAIT_AT_LOW_PRIORITY. Switching to raw T-SQL is the right call.

---

Proposed Implementation

New parameters:

[switch]$WaitAtLowPriority,
[int]$MaxDuration = 0,
[ValidateSet('Self', 'Blockers', 'None')]
[string]$AbortAfterWait = 'Self'

T-SQL replacement for the SMO call:

The ShrinkMethod enum maps cleanly to DBCC options:

SMO ShrinkMethod	DBCC SHRINKFILE option
Default	(none)
EmptyFile	EMPTYFILE
NoTruncate	NOTRUNCATE
TruncateOnly	TRUNCATEONLY

The generated T-SQL would look like:

DBCC SHRINKFILE (N'logical_name', target_size_mb [, EMPTYFILE|NOTRUNCATE|TRUNCATEONLY])
[WITH WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = SELF)]

This can be executed via $server.Query(...) using the already-available connection with StatementTimeout already applied.

Version guard: SQL Server 2022 (version 16) only for the WAIT_AT_LOW_PRIORITY clause. If -WaitAtLowPriority is specified on an older instance, Stop-Function with a clear message.

Backward compatibility: When -WaitAtLowPriority is not specified, we have two options:

1. Keep SMO for non-WALP path (no regression risk)
2. Switch everything to T-SQL (simpler code, same behavior)

Option 2 is cleaner since SMO just wraps the same T-SQL anyway. Worth doing both at once.

---

Concerns Worth Noting

1. EmptyFile with step-size: When ShrinkMethod = EmptyFile, DBCC SHRINKFILE ignores the target size entirely — so the step-size loop is a no-op in that case. This is already true with SMO, not a new problem, but worth documenting.

2. LogsOnly deprecated parameter: The existing deprecation notice should be preserved when refactoring.

3. $file.Refresh() after shrink: With the T-SQL approach, we'd need to call $file.Refresh() after execution (as currently done) so the size properties are updated for the output object. This is straightforward.

4. Test coverage: The existing test file should be updated to validate the new parameters and the SQL Server version guard.

---

Verdict

The issue's analysis is accurate and the proposed direction is sound. The main work is:

• Replacing ~2 lines of SMO with T-SQL string building + $server.Query()
• Adding 3 parameters with a version check
• Updating tests

I'd be happy to implement this if asked.

[andreasjordan]

There is already an open draft pr #10258 which should be handled and merged first.

[ReeceGoding]

Claude's approach looks good... for any other WAIT_AT_LOW_PRIORITY case. DBCC SHRINKFILE is unfortunately special. If the documentation is to be believed, then ABORT_AFTER_WAIT = NONE is not an option for DBCC SHRINKFILE.

Error 401 if you try it on SQL Server 2025.

Incidentally, was there a better way for me to get dbatools to give me the SQL Server error number there?

Come to think of it, the documentation doesn't mention max duration either. If you try, you get a different error!

[andreasjordan]

@claude please look at the last comment.

[claude]

Claude finished @andreasjordan's task in 1m 37s —— View job

---

Reviewing ReeceGoding's Correction

• Read the error screenshots
• Verify the correct WAIT_AT_LOW_PRIORITY syntax for DBCC SHRINKFILE
• Update proposed implementation accordingly

---

@ReeceGoding is absolutely correct — thank you for testing this. My previous analysis was wrong on two counts.

Corrected Understanding

The DBCC SHRINKFILE docs explicitly state that ABORT_AFTER_WAIT = NONE is not permitted, and the syntax shows no MAX_DURATION option at all:

-- The ONLY valid syntax for DBCC SHRINKFILE WAIT_AT_LOW_PRIORITY:
DBCC SHRINKFILE (file_name, target_size)
WITH WAIT_AT_LOW_PRIORITY (ABORT_AFTER_WAIT = { SELF | BLOCKERS })

This is meaningfully different from WAIT_AT_LOW_PRIORITY in ALTER TABLE / online index operations, which do support both MAX_DURATION and NONE. For DBCC SHRINKFILE:

Feature	DBCC SHRINKFILE	ALTER TABLE
MAX_DURATION	Not supported	Supported
ABORT_AFTER_WAIT = NONE	Not supported	Supported
ABORT_AFTER_WAIT = SELF	Supported	Supported
ABORT_AFTER_WAIT = BLOCKERS	Supported	Supported

When a lock can't be obtained, DBCC SHRINKFILE silently exits after ~1 minute and logs error 49516 to the SQL Server error log — this timeout is fixed, not configurable.

Corrected Parameter Design

My previous ValidateSet was wrong. The correct parameters are simply:

[switch]$WaitAtLowPriority,
[ValidateSet('Self', 'Blockers')]
[string]$AbortAfterWait = 'Self'

No $MaxDuration at all.

---

On Your Side Question

Incidentally, was there a better way for me to get dbatools to give me the SQL Server error number there?

Your screenshot already showed the answer via Get-Error — the number 401 was in InnerException.Errors. But a more direct approach in dbatools would be to use a try/catch with -EnableException:

try {
    Invoke-DbaQuery -SqlInstance $primary -Query "..." -Database "..." -EnableException
} catch {
    $_.Exception.InnerException.Errors | Select-Object Number, Message
}

This surfaces the SQL error number cleanly without needing to dig through Get-Error.