[SQL Server]:backup, file manipulation operations changes on a database must be

bestu

　　From: http://www.cnblogs.com/kerrycode/p/5645290.html
　　昨天在检查YourSQLDba备份时，发现有台数据库做备份时出现了下面错误信息，如下所示：
  yMaint.ShrinkLog  Log Shrink  --  ========================================================================-- Shrink of log file E:\SQL_LOG\xxxx_log.ldfUSE [xxxx]DBCC SHRINKFILE (N'xxx_Log',      19043) with no_infomsgs--  ========================================================================     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.　　关于这个错误，是因为调整了作业的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]GOALTER DATABASE [TEST] SET RECOVERY SIMPLE WITH NO_WAITGO

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224220264-83303967.png

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

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224221983-1890981801.png

　　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 = 10GO　　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 = 10GO　　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_nameFROM    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 qtWHERE   session_Id >= 51;

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224224467-752103550.png

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

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224227327-2059342472.png

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224229452-2066724797.png

　　所以我基本上很难构造两个并发的事务日志备份，我尝试使用两个作业在同一时刻运行事务日志备份，也无法实现并发的事务日志备份，真怀疑能否实现这样的场景，所以在这种场景下，我并不能重现这个错误信息。当然如果你取消第二个事务日志备份也会出现这个错误信息（此处实验没有截图，请见第五种情形），所以，个人理解应该是对英文文档理解的一些偏差。
　　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 = 10GO　　会话2：执行下面收缩TEST事务日志的SQL
USE TEST;GODBCC SHRINKFILE(2,10);

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224231811-1006949206.png

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

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224233983-274237180.png

　　检查发现会话66（执行完整备份的会话）阻塞了会话65（差异备份的会话），但是如果我取消会话65，如下所示，就会发现出“backup, file manipulation operations (such as>

http://images2015.cnblogs.com/blog/73542/201607/73542-20160705224235936-1973948949.png

　　参考资料：
　　https://support.microsoft.com/en-us/kb/2979636