![]() When a timeout occurs, an error 49516 message will be sent to the SQL Server error log, for example: Msg 49516, Level 16, State 1, Line 134 Shrink timeout waiting to acquire schema modify lock in WLP mode to process IAM pageID 1:2865 on database ID 5. This will occur if the shrink operation cannot obtain the Sch-M lock due to concurrent query or queries holding Sch-S locks. 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 shrink operation will execute when it is able to obtain a schema modify lock (Sch-M) lock. 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. You cannot specify ABORT_AFTER_WAIT option NONE.This feature is similar to the WAIT_AT_LOW_PRIORITY with online index operations, with some differences. For more information, see Understanding concurrency issues with DBCC SHRINKDATABASE. The wait at low priority feature reduces lock contention. WAIT_AT_LOW_PRIORITY with shrink operationsĪpplies to: SQL Server 2022 (16.x) and later versions, Azure SQL Database, Azure SQL Managed Instance Suppresses all informational messages that have severity levels from 0 through 10. ![]() For more information, see DBCC SHRINKFILE. To truncate the data file, use DBCC SHRINKFILE instead. Azure Synapse Analytics doesn't support this option.ÄBCC SHRINKDATABASE with the TRUNCATEONLY option affects the database transaction log file only. Ignores target_percent if specified with TRUNCATEONLY. The data file shrinks only to the last assigned extent. Releases all free space at the end of the file to the operating system. NOTRUNCATE is applicable only to data files. As such, the database appears not to shrink when you specify NOTRUNCATE. The free space at the end of the file isn't returned to the operating system, and the physical size of the file doesn't change. Azure Synapse Analytics doesn't support this option. This action compacts the data within the file. Moves assigned pages from the file's end to unassigned pages in the front of the file. The percentage of free space that you want left in the database file after the database has been shrunk. 0 specifies that the current database is used. Arguments database_name | database_id | 0 To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
0 Comments
Leave a Reply. |