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

  昨天在检查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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值