SQL Server 2014 Enterprise SQL Server 2014 Enterprise SQL Server 2012 Enterprise SQL Server 2008 R2 Enterprise SQL Server 2008 Enterprise Microsoft SQL Server 2005 Enterprise Edition Еще…Меньше
Симптомы
При попытке запуска резервного копирования, сжатия или изменять команды базы данных в SQL Server и возникают следующие сообщения:
Сообщение 3023, уровень 16, состояние 2, строка 1
Операции резервного копирования и файл манипуляции (например, изменить ФАЙЛ базы данных добавить) в базе данных должны быть сериализованы. Повторите инструкцию после завершения текущего резервного копирования или операции.
Msg 3013, уровень 16, состояние 1, строка 1
Резервное копирование базы данных завершается аварийно.
Кроме того в журнал ошибок SQL Server содержит сообщения следующим образом:
Ошибка резервного копирования 2014-05-12-01:00:28.05: 3041, уровень серьезности: 16, состояние: 1.
2014-05-12 01:00:28.05 резервного КОПИРОВАНИЯ резервной копии не удалось завершить команду резервная копия базы данных MyDatabase WITH DIFFERENTIAL. Подробные сообщения в журнале приложения резервного копирования.
Можно также заметить, что эти команды возникает «wait_type = LCK_M_U»и «wait_resource = базы данных: < id > [BULKOP_BACKUP_DB] » при просмотре состояния этих команд из различных динамических административных представлений (DMV), например, от sys.dm_exec_requests или sys.dm_os_waiting_tasks.
Причина
Существует несколько правил, на которых операции будут разрешены или запрещены при полной базы данных в данный момент выполняется в базе данных. Ниже приведены некоторые примеры:
-
Только один данных резервное копирование может выполняться одновременно (при полной резервной копии происходит, добавочных или разностных резервных копий, не может быть одновременно).
-
Только одна резервная копия журналов может произойти одновременно (резервной копии журнала разрешено, когда выполняется полное резервное копирование).
-
Невозможно добавить или удалить файлы в базе данных во время резервного копирования происходит.
-
Невозможно сжать файлы, пока происходит резервное копирование базы данных.
-
Существуют во время резервного копирования происходят изменения модели ограниченного восстановления.
При выполнении любой из этих операций конфликтующие команды встретит ожидания блокировки, описанные в разделе «Проблема», а затем при получении сообщений 3023 и 3041.
Решение
Изучите расписания различных действий по обслуживанию базы данных и затем скорректировать расписания таким образом, чтобы эти операции или команды не конфликтуют друг с другом.
Дополнительные сведения
SQL Server записывает время начала и время окончания резервного копирования в базе данных msdb. Можно проверить журнал резервного копирования, чтобы определить, существует ли полное резервное копирование происходит, когда попытка добавочное резервное копирование и таким образом вызвал ошибку. Для этого процесса можно использовать следующий запрос:
select database_name, type, backup_start_date, backup_finish_date
from msdb.dbo.backupsetorder by database_name, type, backup_start_date, backup_finish_date
go
«Сообщение об ошибке пользователя» события трассировки SQL Profiler или событие «error_reported» в расширенных событий также можно использовать для отслеживания отчетов 3023 сообщений обратно в приложение, инициировавшее резервного копирования или другие команды обслуживания.
Нужна дополнительная помощь?
SQL server error 3023 triggers while trying to run a backup, shrink, or alter database command in SQL Server when a backup process is running in the server.
As a part of our Server Management Services, we help our Customers to fix SQL related errors regularly.
Let us today discuss the possible causes and fixes for this error.
What is SQL server error 3023?
As we discussed earlier the error 3023 triggers while trying to run a backup, shrink, or alter database command in the Server. This generally happens when we perform another database backup if the backup is already running for the database.
Let us now look at the common reasons for this error.
What causes SQL server error 3023?
There are some restrictions in the operations that are allowed and not allowed when a database backup process is running. These includes:
1. We can run only one database backup at a time. When a full database backup occurs, differential or incremental backups cannot occur at the same time.
2. Only one log backup can happen at a time. A log backup is allowed when a full database backup is occurring.
3. You cannot add or drop files to a database while a backup is occurring.
4. You cannot shrink files while database backups are happening.
5. There are limited recovery model changes allowed while backups are occurring.
Thus, a breach in any of the above restrictions can trigger the SQL Server error 3023, SQL Server error 3013 and SQL Server error 3041 messages. Let us now look into the steps to fix this error message.
How to fix the SQL server error 3023?
The solution to fix the 3023 error is to 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.
The first thing to do if we see this error message is to check the SQL Server Agent jobs and wait till that job completes.
We need to check the status of the database in SQL to see if it is in a state such as “Restoring” and if so, allow time for the current operation to complete before attempting another backup.
Alternately we can perform any of the following steps:
* Cancel the pending operation against the database
* Bring the database offline and back online
* Remove the database from the backup selections
Further, SQL Server records the start time and the end time of the backup in the msdb database. We can examine the backup history to determine whether there was a full database backup occurring while an incremental backup was attempted. 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
~~
[Need any further assistance in fixing Database errors? – We’re available 24*7]
Conclusion
In short, the SQL server error 3023 occurs while trying to run a backup, shrink, or alter database command in SQL Server when a backup process is running in the server . Today, we saw how our Support Engineers fix this error.
February 12, 2014 by Muhammad Imran
Last weekend, my support engineer and I were at a client site to upgrade SQL Server 2005 to SQL Server 2012. Before proceeding with any activity, we tried to take a backup and got the given below error that I received it later by my support engineers as shown below.
Let me explain the error and its solution in details.
Message Number: 3023
Severity : 16
Error Message: 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.
Resolution:
If you look at the error description, it seems that there is a transaction going on in the background, and due to this reason SQL Server is not allowing us to take a backup or may be the database is corrupt. So we started doing our basic testing and it seems everything is fine. Then why SQL Server is not allowing us to take a backup ?
So, I thought of executing the given below query to check whether any backup or restore operation is in progress and I found that a scheduled backup was already in progress. Ooopss……….. So we waited for a while and took the backup again.
Conclusion:
Whenever you come across this error, you should check what the processes are in progress in SQL Server. It will give you a hint, why you are unable to take backup. In my case, a scheduled backup was in progress at the same time when I tried to take the backup.
Posted in Errors, SQL SERVER | Tagged Backup and file manipulation operations, Msg 3023 Level 16 State 1, raresql, SQL, SQL Server, SQL Server 2012 | Leave a Comment
- Remove From My Forums
-
Question
-
Hi All,
Over the weekend, I noticed one occurrence of the following error while Idera SQLsafe was running a full backup:
Error: 3023, Severity: 16, State: 3.
Source: spid18s.
Message: 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.It only happened once 2 nights ago, but it is the first time we have ever seen such a message in the 5 years the server has been operational.
There are no database shrink or alter operations going on at the time, and in fact the only other things running are native t-log backups, which should be able to run concurrently.
The only thing I can think of that might have something to do with this was that last week we implemented a change to reset the affinity mask to 0 (i.e. use all processors) instead of 65535 (i.e. hard-bind all processors), and increase max/min server memory
by an additional 5 GB, which was physically added a month ago. We did not reboot SQL Services afterwards, and I wonder whether permanent system tasks which are not migrated as a result can interfere, especially as I noticed the source was coming from
SPID 18, which is currently showing as a TASK MANAGER command, although interestingly, it’s last batch was a month ago.Any thoughts would be appreciated!
Regards,
Diane
Diane
Question: I received an alert for a regular backup failure . A closer inspection of the sql server agent error logs displayed this error message:
Executed as user: myUser. 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. [SQLSTATE 42000] (Error 3023) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).
Answer: There are different reasons this error message is generated. The error message says that «ALTER DATABASE ADD FILE and encryption changes must be serialized». This means there are some restrictions (or rules) defining what activity can take place when a full back is in progress.
For example:
Only one backup operation can occur on a database at any time
Restriction on adding or dropping files on a database
Restriction on database shrink if a database is being backed up
The first step in troubleshooting is to identify the other activity which is causing the error. Check SQL Server Agent Jobs, and any other schedulers which may be triggering jobs conflicting with the backup.
Also , check any other current activity which a user may be attempting.
Sometimes the problem may be generated indirectly. Let’s say there is a failed backup being rerun — this backup may run unexpectedly into a window for another activity. SQL Server maintains a Backup history for a single SQL Server database use the information out of the backup duration to assess whether the backup took longer than normal or was rerun .
Author: Tom Collins (http://www.sqlserver-dba.com)
Share:
As part of our Server Support Services, we have solved numerous SQL related issues for our clients.
In this context , we shall look at the main causes of this error and how to fix SQL error 3023.
What causes SQL server error 3023?
The main cause of this error is when we are duplicating a backup process or when you are trying to run an alter action at the same time with an ongoing database backup process.
What to observer during if SQL server error 3023 occurs
When this error takes place, you should observe the following factors;
i. Whether there is another instance of a backup process at the same time of making another database backup action. It is important to note that only one backup process per database is allowed.
ii. Activities such as dropping tables or altering is not permitted during a backup process.
iii. Files cannot undergo shrinking when an backup process is in place.
iv. There can only be one log backup per database backup session. So duplicates at the same time is not allowed.
How to solve SQL server error 3023
In order to fix SQL 3023 error, we need to check whether there is an ongoing database upgrade process running. If there is, it is best to do a proper schedule arrangement so that two or more processed do not clash.
A very good approach is to check the error logs for more information to enable you have a clearer idea of what is going on.
You can use the following query command to debug the database backup history or status;
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
Need support in solving Database errors? Reach us Here.
Error: Msg 3023, Level 16, State 2, Line 4
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 means that you were trying to run dbcc shrinfile command when some backup operation was running on the database. It can be any backup full,differential and transaction log. It means: please try running shrikfile command when backup operation has completed. Now you have to find what backup was running at that time. See errrlog or msdb job history you could find more details.
If you refer to BOL
Operations that cannot run during a database or transaction log backup include:
• File management operations such as the ALTER DATABASE statement with either the ADD FILE or REMOVE FILE options.
• Shrink database or shrink file operations. This includes auto-shrink operations.
Conclusion:
Whenever you come across this error, you should check what the processes are in progress in SQL Server. It will give you a hint, why you are unable to take backup. In my case, a scheduled backup was in progress at the same time when I tried to take the backup.
Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.