SQL Database Backup Error – File manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Backups are one of the critical and primary functions as a DBA. Recently I noticed a failed backup job that runs apart from our enterprise backups. We use these individual backups to refresh non-prod environments for developers to run their queries. I have had these jobs fail with the following error:

Msg 3023, Level 16, State 2, Line 1
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.

The job fails as soon as I initiated a re-run. So naturally I went to check what processes are running currently. To do this, I run sp_WhoIsActive. I noticed that a scheduled job from enterprise backup system has been running but ran a little longer than usual. This seems to be causing the error when I tried to run another backup through the SQL Agent job on the same database.

The resolution is that i just waited for the scheduled backup job to complete and re-ran the SQL Agent job and it completed successfully.

Another possible reason for this error is that if we perform a shrink operation in parallel to backup operation. In general, shrink is NOT recommended unless you know that you have deleted a large set of data releasing space that you would like to reclaim.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: