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.
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.
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)
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)
17 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.
Very Good one..I really like this site
Thanks Kiran.
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
There has to be some operation. did you get a chance to look at sysprocesses?
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.
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
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’.
Correct. Thanks Sreevathsa for helping Daniel
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.
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.
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!
Thank you for posting this. Even though the person didn’t give you more details..
Thank you!!
Thank you Dave, for saving my buttocks 6 years later with this article.
Now I have to find out why my clients production server is making backups in the middle of the day.
Hi there,
I’ve found another scenario for this error and that is if you try to take a backup during a DBCC CHECKDB(”)
If there is a integrity check going on and you try to take a backup it will yield this error too.
Hope that helps :-)
Got this one lately: On big and old database which was running in full recovery, a dba did a reindexjob every sunday. The log backup ran every hour with an (aweful) shrink at the end of the job. The reason was that the log backup + shrink during the re index job took so much time – on a slow storage and with a growing database- that it eventually took more than the one hour during backup logs. From then on, the log could not be saved and grew full up to the point where it ran out of space and the database stopped…. that was a fuuuun monday morning :D .
Solution was simply to remove the shrink, make a manual log backup and that’s it. Eventually we now switch the DB to simple before re indexing, then back to full recovery model when it’s done, immediately followed by a full backup.
This article did lead me to the right answer, I thought I would complete the “causes” with my own 0.02$, thanks!
Hi, does fragmentation only occurs on Index tables?