As a DBA, encountering errors is a common part of our job. It is through these errors that we learn how SQL Server works and gain a deeper understanding of its inner workings. One of the key responsibilities of a DBA is to create backups and automate them using jobs and maintenance plans. However, there are times when these backup jobs fail without any apparent reason.

Let’s take a look at a typical scenario that a DBA might encounter. Imagine you come across an error message like this:

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.

This error message indicates that there is a conflict between backup operations and other file manipulation operations or encryption changes on the database. In simpler terms, these operations need to be performed one after the other, rather than simultaneously.

So, what could be the cause and solution to this error? Let’s explore further.

In a recent email from one of my blog readers, they shared a similar error message they encountered. Upon further investigation, it was discovered that a job was running and failing with the same error. To troubleshoot the issue, I asked the reader to provide more details about the job and also suggested checking a blog post by my friend Balmukund, which provides a query to find what is running at the same time as the job.

Unfortunately, the reader did not respond, which indicates that their issue might have been resolved. However, this left me curious to find the possible causes of the error message (Msg 3023, Level 16, State 2).

Upon analyzing the error message again, it became apparent that running two parallel backups could potentially cause this error. To test this theory, I ran two parallel backup commands for a database with a size of 100GB. As expected, only one backup made progress while the other was waiting for the first one to finish. This confirmed that the error is not raised when backups are waiting, but rather when two backups are running simultaneously.

Another possible reason for this error is if a shrink operation is performed in parallel with a backup operation. It’s important to note that performing a shrink operation is not recommended, but some individuals still choose to do so.

Here is the error message that would be encountered in such a scenario:

Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file 'SQLAuthority'. 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.

Depending on the order of operations, the behavior would be as follows:

• If a backup is started while an add or remove file operation is in progress, the backup will wait for a timeout period and then fail.
• If a backup is running and one of these operations is attempted, the operation fails immediately.

The solution to this error is to identify the conflicting operation and retry your operation after stopping or finishing the conflicting operation.

Learning from error messages is a great way to gain insights into what happens inside SQL Server. It helps us understand the underlying mechanisms and improve our troubleshooting skills. I encourage you to share your experiences of learning from error messages in your own environments.

Stay tuned for more SQL Server tips and insights!