你的位置:首页 > 软件开发 > 数据库 > backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

发布时间:2016-07-06 00:00:14
昨天在检查YourSQLDba备份时,发现有台数据库做备份时出现了下面错误信息,如下所示: <Exec> <ctx>yMaint.ShrinkLog</ctx> <inf>Log Shrink</inf> ...

  昨天在检查YourSQLDba备份时,发现有台数据库做备份时出现了下面错误信息,如下所示:

<Exec>
  <ctx>yMaint.ShrinkLog</ctx>
  <inf>Log Shrink</inf>
  <Sql>
--  ========================================================================
-- Shrink of log file E:\SQL_LOG\xxxx_log.ldf
USE [xxxx]
DBCC SHRINKFILE (N'xxx_Log',      19043) with no_infomsgs           
--  ========================================================================
   </Sql>
  <err>Error 3023, Severity 16, level 2 : 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.
</err>
</Exec>

 

关于这个错误,是因为调整了作业的YourSQLDba_LogBackups的Schedule,导致YourSQLDba_FullBackups_And_Maintenance在运行时,事务日志备份已经开始了(这个数据库的事务日志由于索引重建、重组,会变得比较巨大),此时YourSQLDba_FullBackups_And_Maintenance作业执行收缩事务日志,就出现了这个错误。下面我们根据官方文档Server.aspx' target='_blank'>SQL Server generates a 3023 message when backup and file operations are tried at the same time来验证,测试一下这个错误出现的各类场景:

 

1: 当数据库正在做备份时,不允许修改恢复模式(Recovery Model)。

  •   There are limited recovery model changes allowed while backups are occurring.

 

会话1:执行备份数据库TEST

BACKUP DATABASE [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705.bak' WITH NOFORMAT, NOINIT,  
       NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO

 

会话2:修改数据库的恢复模式。

USE [master]
GO
ALTER DATABASE [TEST] SET RECOVERY SIMPLE WITH NO_WAIT
GO

 

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

如上所示,在备份数据库TEST时,如果我们去修改数据库TEST的恢复模式,就会报这个错误,这是不允许的。

 

 

2:当数据库正在备份时,添加或删除文件是不允许的

  • You cannot add or drop files to a database while a backup is occurring. 

测试验证如下

 

1:会话窗口1执行备份数据库TEST

BACKUP DATABASE [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705.bak' WITH NOFORMAT, NOINIT,  
       NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
GO

 

2:会话窗口2执行增加日志文件时就会报错

USE [master]
GO
ALTER DATABASE [TEST] ADD FILE ( NAME = N'QCMDB_Data1', 
        FILENAME = N'D:\tmp\QCMDB_Data1.ndf' , SIZE = 5120KB , 
        MAXSIZE = 10485760KB , FILEGROWTH = 20480KB ) TO FILEGROUP [PRIMARY]
GO
-----------------------------------------------------------------------
Msg 3023, Level 16, State 2, Line 3
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.

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

3:同一时刻只能允许一个事务日志备份(log backup),(当数据库正在做全备时,事务日志备份是允许的)

  • Only one log backup can happen at a time (a log backup is allowed when a full database backup is occurring).

关于这一点,同一时刻肯定只能允许一个事务日志备份,理论上不能出现两个并行的事务日志备份,这也是我纠结的地方,关于这段英文“Only one log backup can happen at a time (a log backup is allowed when a full database backup is occurring”, 是否两个事务日志备份是就会出现上述错误呢?

 

1:会话ID为64的窗口执行下面事务日志备份脚本

 

SELECT @@SPID;
BACKUP LOG [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705_1430_1.trn' 
    WITH NOFORMAT, NOINIT, 
NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

2:会话ID为66的窗口执行下面事务日志备份脚本

SELECT @@SPID;
BACKUP LOG [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705_1430_2.trn' 
    WITH NOFORMAT, NOINIT, 
NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

3:  在同时执行上面两个脚本前,最好先生成大量事务日志(我用重建TEST库里面几个大表的索引,生成了大量事务日志),然后同时执行上面两个窗口脚本(注意,由于需要人手工点击执行脚本,所以还是有点时间差),在第三个窗口查看会话信息

SELECT   [Spid] = er.session_id 
        ,[ecid] 
        ,[Database] = DB_NAME(sp.dbid) 
        ,[Start_Time]
        ,[SessionRunTime]    = datediff(SECOND, start_time,getdate())    
        ,[SqlRunTime]=     RIGHT(convert(varchar, 
                                 dateadd(ms, datediff(ms, sp.last_batch, getdate()), '1900-01-01'), 
                            121), 12)  
        ,[HostName]  
        ,[Users]=COALESCE(sp.LOGINAME, sp.nt_username)
        ,[Status] = er.status 
        ,[WaitType] = er.wait_type 
        ,[Waitime] = er.wait_time/1000   
        ,[Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                       ( CASE WHEN er.statement_end_offset = -1
                                              THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                   * 2
                                              ELSE er.statement_end_offset
                                         END - er.statement_start_offset ) / 2) 
        ,[Parent Query] = qt.text 
        ,[PROGRAM_NAME] = program_name 
FROM    sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE   session_Id >= 51;

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

如上所示,会话64在执行事务日志备份备份时,会话66被阻塞,他在等待更新锁(LCK_M_U),至于为什么有更新更新锁,使用SQL Profile跟踪看到下面信息,你也许就明白了。

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

   所以我基本上很难构造两个并发的事务日志备份,我尝试使用两个作业在同一时刻运行事务日志备份,也无法实现并发的事务日志备份,真怀疑能否实现这样的场景,所以在这种场景下,我并不能重现这个错误信息。当然如果你取消第二个事务日志备份也会出现这个错误信息(此处实验没有截图,请见第五种情形),所以,个人理解应该是对英文文档理解的一些偏差。

 

4:当数据库正在备份时,不能收缩数据库文件。当然这里的备份包括完整备份和事务日志备份。

  • You cannot shrink files while database backups are happening.

 

会话1: 在数据库TEST上执行事务日志备份(备份前,为了实验效果,最好生成大量事务日志)

BACKUP LOG [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705_1430_1.trn' 
    WITH NOFORMAT, NOINIT, 
NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

 

会话2:执行下面收缩TEST事务日志的SQL

USE TEST;
GO
DBCC SHRINKFILE(2,10);

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

备份数据库的时候,收缩数据库文件倒是能测试通过,不知道是否与我的测试案例有关系,也没有太多精力去研究、改造测试案例。有兴趣的可以研究一下。

 

5:同一时刻,只能做一个数据库备份操作(当数据库完整备份时,差异、增量备份不能同时出现)

  • 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).

 

 

1:会话66执行下面完整备份脚本

BACKUP DATABASE [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_20160705.bak' WITH NOFORMAT, NOINIT,  
 
       NAME = N'TEST-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
 
GO

 

2:会话65执行下面差异备份脚本

BACKUP DATABASE [TEST] TO  DISK = N'D:\DB_BACKUP\TEST_DIFF_20160705_01.bak' 
    WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'TEST-Full Database Backup',
     SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

3:会话窗口3执行下面脚本,检查SQL之间的阻塞

 
 
  SELECT wt.blocking_session_id                    AS BlockingSessesionId
        ,sp.program_name                           AS Blocking_ProgramName
        ,COALESCE(sp.LOGINAME, sp.nt_username)     AS Blocking_HostName    
        ,ec1.client_net_address                    AS ClientIpAddress
        ,db.name                                   AS DatabaseName        
        ,wt.wait_type                              AS WaitType                    
        ,ec1.connect_time                          AS BlockingStartTime
        ,wt.WAIT_DURATION_MS/1000                  AS WaitDuration
        ,ec1.session_id                            AS BlockedSessionId
        ,h1.TEXT                                   AS BlockedSQLText
        ,h2.TEXT                                   AS BlockingSQLText
  FROM sys.dm_tran_locks  AS tl WITH(NOLOCK)
  INNER JOIN sys.databases AS db  WITH(NOLOCK)
    ON db.database_id = tl.resource_database_id
  INNER JOIN sys.dm_os_waiting_tasks AS wt  WITH(NOLOCK)
    ON tl.lock_owner_address = wt.resource_address
  INNER JOIN sys.dm_exec_connections  ec1 WITH(NOLOCK)
    ON ec1.session_id = tl.request_session_id
  INNER JOIN sys.dm_exec_connections  ec2 WITH(NOLOCK)
    ON ec2.session_id = wt.blocking_session_id
  LEFT OUTER JOIN master.dbo.sysprocesses AS sp WITH(NOLOCK)
    ON SP.spid = wt.blocking_session_id
  CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1 
  CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

检查发现会话66(执行完整备份的会话)阻塞了会话65(差异备份的会话),但是如果我取消会话65,如下所示,就会发现出“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.”错误,当然,如果你不取消,不会出现这个错误,会话会一直阻塞,直到完整备份完成,然后继续执行差异备份。这个跟场景3基本上是一样的。

backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

 

参考资料:

https://support.microsoft.com/en-us/kb/2979636

 


原标题:backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

关键词:ip

ip
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。