Issue:
Getting the following when trying to shrink the database.
Error:
Could not adjust the space allocation for file ‘xxxxxxxxx’.
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft SQL Server, Error: 3140)
Resolution:
This statement means that when performing a database backup, any actions that modify the database file structure (like adding or removing data files using “ALTER DATABASE ADD FILE”) or changing encryption settings must be done one at a time, in a sequential order, and not concurrently with a backup operation; essentially, you cannot modify the database files while a backup is in progress.
Possible causes
There are several rules on which operations are allowed or not allowed when a full database is currently in progress against a database. Some examples are as follows:
- Only one data Backup can occur at a time (when a full database Backup occurs, differential, or incremental Backups cannot occur at the same time).
- Only one-log Backup can happen at a time (a log Backup is allowed when a full database Backup is occurring).
- You cannot add or drop files to a database while a Backup is occurring.
- You cannot shrink files while database Backups are happening.
- There are limited recovery model changes allowed while Backups are occurring.
User action
Examine the schedules of the various database maintenance activities, and then adjust the schedules so that these operations or commands do not conflict with each other.
Comments