0

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-ver15#remarks

Shrink operations in progress can block other queries on the database, and can be blocked by queries already in progress.

Is this (blocking) applicable to both data and log files?

The question is so that the shrink activity can be planned during normal working hours or during maintenance window to prevent user/system issues caused due to the blocking.

For example - the following answer (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ed0db5ea-06a3-4a93-8ccc-362cfb94703d/would-manually-executing-dbcc-shrinkdatabase-block-the-database-?forum=sqltools) says the opposite of what is given in the above quote:

DBCC SHRINKFILE does not block operations in the database.

2
  • 1
    I would normally lean towards discounting the posts from potentially random people on the social side.... but that's Erland Sommarskog! and if you follow the link to learn.microsoft.com/en-us/sql/t-sql/database-console-commands/… directly, it does confirm that SHRINKFILE allows others to work in the database at the same time. Sep 26, 2022 at 12:37
  • It does allow others to work but blocks the,m as per the same link - When attempting to reclaim space during regular usage, shrink database and shrink file operations currently require a schema modify lock (Sch-M) when moving or deleting Index Allocation Map (IAM) pages, blocking the Sch-S locks needed by user queries. As a result, long-running queries will block a shrink operation until the queries complete. This means that any new queries requiring Sch-S locks are also queued behind the waiting shrink operation and will also be blocked, further exacerbating this concurrency issue.
    – variable
    Jul 31, 2023 at 7:54

1 Answer 1

1

Does shrink (of data/log file) causes blocking?

It can, yes, Docs/Learn is correct. It may or may not depending on the options chosen and the data/type in the database. For example, truncating just the free space at the end of a file can be done without much/any blocking (certain internal items do need to be obtained). Moving pages around to create free space at the end of a file so that the space can be reclaimed by the OS will cause significant latching and may cause more items, for example in a LOB tree.

The question is so that the shrink activity can be planned during normal working hours or during maintenance window to prevent user/system issues caused due to the blocking.

It's an online operation, however I would personally do it during a downtime, unless of course in your testing you've found that the production workload isn't so sensitive that it causes a problem.

2
  • Can you add information about log files to your answer? Are you referring to log file in the following statement: For example, truncating just the free space at the end of a file can be done without much/any blocking (certain internal items do need to be obtained).
    – variable
    Sep 26, 2022 at 14:04
  • 4
    The Docs answer what/how it is done, you asked if it could cause blocking, I'm not rewriting the Docs article. Sep 26, 2022 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.