Additionally, the SQL Server error log contains messages like the following:
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.
- 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.
select database_name, type, backup_start_date, backup_finish_date
order by database_name, type, backup_start_date, backup_finish_date
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.
ID članka: 2979636 - Poslednji pregled: 03.07.2014. - Verzija: 1