SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized

SQL SERVER - FIX: ERROR : Msg 3023, Level 16, State 2 - Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized help button Errors are the best way to learn how SQL Server works and as DBA’s we are bound to see many of them from time to time. One of the primary functions of a DBA would include creating backups and most importantly trying to automate the same using jobs and maintenance plans.

Here is a typical scenario which a DBAs can encounter. One fine day they notice that some backup jobs are failing for no reason. Normal troubleshooting always starts with an error message. Recently, one of my blog readers sent an email to me which was worth a look.

I am getting below error. What is the cause and solution?

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.

I pinged him on twitter and asked more details. He informed that they have a job which runs and fails with the error described above. I asked him to get more details about the job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means his issue might be resolved.

But that left me curious to find the possible causes of the error Msg 3023, Level 16, State 2. Reading the message again, it looks like two parallel backups would cause error. So I ran two parallel backup command for a database which was little big in size (100GB). As soon as two full backups started, I could see that only one backup was making progress (session id 57) and another (session id 58) was waiting for first one to finish.

Solarwinds

SQL SERVER - FIX: ERROR : Msg 3023, Level 16, State 2 - Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized backup_stuck-01

Which means the error is not raised and backup is waiting. But as soon as I cancelled the query (session 58), I got below message.

SQL SERVER - FIX: ERROR : Msg 3023, Level 16, State 2 - Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized backup_stuck-02

Another possible reason of the error is that if we perform shrink operation in parallel to backup operation. (Shrink is NOT something which I recommend, but people would never listen)

SQL SERVER - FIX: ERROR : Msg 3023, Level 16, State 2 - Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized backup_stuck-03

Here is the text

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 who came first, here is the behavior. If a backup is started when either add or remove file operation is in progress, the backup will wait for a timeout period, then fail. If a backup is running and one of these operations is attempted, the operation fails immediately.

Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.

Learning using error messages is a great way to understand what happens inside SQL Server. Do let me know in the recent past, what have you learnt from error messages in your environments.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Fix – Error 5058, Level 16, State 1 – Option cannot be set in database
Next Post
SQL SERVER – FIX: ERROR : Msg 3136, Level 16, State 1 – This differential backup cannot be restored because the database has not been restored to the correct earlier state

Related Posts

13 Comments. Leave new

  • Hi Pinal,
    How should I use expire date option on sql backups because in my case I am trying to take backups on a pendrive and I can’t witness any expiring backups. Also I am unable to overwrite the expired backups with new ones.

    Reply
  • Very Good one..I really like this site

    Reply
  • Hello, today I faced the same issue. I checked for simultaneous operation running in server like another backup or shrink on the database. But nothing was running. Finally I rebooted the server and restarted the backup command. It ran successfully

    Reply
  • While researching an issue with a failing backup (Symantec Backup), so I thought I’d manually back up the databases. When that resulted in the error which led me here, I used your friend’s script and found some processes suspended and a couple of them blocked. All of them have to do with either my Symantec backup or a SQL maintenance plan. I assume that the timing of the backup and the scheduled maintenance plans are overlapping resulting in the issue.

    My question is, is it safe to kill these processes, the databases affected are our main ERP database and I’d rather not cause the company to be halted because of a bad timed killed process.

    I have stopped any active maintenance activity and the Symantec backup jobs have failed or been cancelled. Is it safe to kill the processes or should I just wait until the end of the day and reboot the server (assuming that will flush out the processes in question and then change the schedule of the maintenance plan so there isn’t a conflict.

    Any help would be greatly appreciated.

    Reply
  • Help Please!!,
    I’ve got the same error but I don’t know how to apply the solution: “Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.”
    Can you tell me how to do to stop the conflicting operation?. Thanks a lot. Daniel

    Reply
  • Daniel,

    You can find out the blocking processes by running the query ‘sp_who2’ or ‘select * from sys.sysprocesses’. You can kill the blocking session ‘kill idnumber’.

    Reply
  • Vaibhav Shukla
    July 11, 2016 2:40 pm

    Thank you Pinal. Today I was in same situation . I found in my problem that the conflicts occurred due to parallel backup process. Same as you mentioned above. Using sys.syprocess and sp_who2 ,I reached to reason.

    Reply
  • Johney Deepak
    August 4, 2017 3:50 am

    Pinal – You are awesome again!
    Transaction log backup was running when I tried adding an additional log in my development server. This is when I encountered the same error message. I will wait until the backup operation is completed now.

    Reply
  • You’re the man! Thanks to the Balmukund’s query I could find a session of HP Data Protector which was suspended over 9 days – it was blocking our SQL Full Backup job. So one thing which might help is to reboot server or find the hanging session and kill it…
    Thank you very much for your analysis – it led me to find the root case of the problem!

    Reply
  • Thank you for posting this. Even though the person didn’t give you more details..
    Thank you!!

    Reply

Leave a Reply

Menu