SQL Server generates a 3023 message when backup and file operations are tried at the same time

Applies to: SQL Server 2014 EnterpriseSQL Server 2014 EnterpriseSQL Server 2012 Enterprise More

Symptoms


You try to run a backup, shrink, or alter database command in SQL Server, and you encounter the following messages:

Msg 3023, Level 16, State 2, Line 1

Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.


Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.



Additionally, the SQL Server error log contains messages like the following:
2014-05-12 01:00:28.05 Backup Error: 3041, Severity: 16, State: 1.

2014-05-12 01:00:28.05 Backup BACKUP failed to complete the command BACKUP DATABASE MyDatabase WITH DIFFERENTIAL. Check the backup application log for detailed messages.

You might also notice that these commands encounter a "wait_type = LCK_M_U" and a "wait_resource = DATABASE: <id> [BULKOP_BACKUP_DB] " when the status of these commands is viewed from the various dynamic management views (DMVs), such as from sys.dm_exec_requests or sys.dm_os_waiting_tasks.

Cause


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.

When any of these conflicting operations are performed, the commands will encounter the lock waits that are mentioned in the "Symptoms" section followed by you receiving the 3023 and 3041 messages.

Resolution


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.

More Information


SQL Server records the start time and the end time of the backup in the msdb database. You can examine the backup history to determine whether there was a full database backup occurring while an incremental backup was attempted and therefore caused the error. You can use the following query to help you with this process:
select database_name, type, backup_start_date, backup_finish_date

from msdb.dbo.backupset

order by database_name, type, backup_start_date, backup_finish_date

go

You can also use the "User Error Message" event in SQL Profiler Trace or the "error_reported" event in Extended Events to track the reporting of the 3023 messages back to the application that initiated the backup or other maintenance command.