backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a d...

最新推荐文章于 2020-12-23 14:46:32 发布

weixin_34056162

最新推荐文章于 2020-12-23 14:46:32 发布

阅读量378

点赞数

文章标签：数据库

昨天在检查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作业执行收缩事务日志，就出现了这个错误。下面我们根据官方文档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 
  

如上所示，在备份数据库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. 
  

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; 
  

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

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

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); 
  

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

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  
  

检查发现会话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基本上是一样的。

参考资料：

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