一臺資料庫伺服器的事務日誌備份作業偶爾會出現幾次備份失敗的情況，具體的錯誤信息為： DATE/TIME: 2018/7/30 12:10:52 DESCRIPTION: BackupDiskFile::CreateMedia: Backup device 'M:\DB_BACKUP\LOG_BACK... ...
一臺資料庫伺服器的事務日誌備份作業偶爾會出現幾次備份失敗的情況，具體的錯誤信息為：
DATE/TIME: 2018/7/30 12:10:52
DESCRIPTION: BackupDiskFile::CreateMedia: Backup device 'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-07-30_06h03m10_Mon]_logs.TRN' failed to create. Operating system error 32(failed to retrieve text for this error. Reason: 15105).
COMMENT: (None)
JOB RUN: (None)
關於Operating system error 32，這個錯誤代碼對應的錯誤信息為：
The process cannot access the file because it is being used by another process.
其實以前剛好遇到過這樣的案例，使用AplexSQL Log去分析事務日誌的備份文件，YourSQLDba的事務日誌備份的時候，就會遇到上面錯誤（YourSQLDba_LogBackups事務日誌備份會追加到同一個備份文件），但是這個怎麼定位這個錯誤呢？其實出現這個錯誤，一定有相關進程在訪問事務日誌備份文件，那麼只要排查這些應用或程式即可。剛好最近配置了AWS的DMS的（DMS簡單介紹如下，具體參考官方文檔https://docs.aws.amazon.com/zh_cn/dms/latest/userguide/Welcome.html）
在最基本級別上，AWS DMS 是 AWS 雲中運行複製軟體的伺服器。您創建源和目標連接以告知 AWS DMS 要進行提取和載入的位置。然後，計劃在此伺服器上運行以遷移數據的任務。AWS DMS 會創建表和關聯的主鍵 (如果它們在目標上不存在)。如果您願意，則可以預先手動創建目標表。或者，也可以使用 AWS SCT 創建部分或全部目標表、索引、視圖、觸發器等。
![clip_image001[4]](https://images2018.cnblogs.com/blog/73542/201808/73542-20180802154618239-205018213.png "clip_image001[4]")
因為DMS有時候會讀取事務日誌備份文件中的內容，通過DPA監控，剛好找的了這個DMS的賬號，在事務日誌備份的時間點在執行下麵SQL語句（如下所示）。那麼就是因為這個進程在讀取事務日誌備份中內容（跟普通的Replication有點不同），導致事務日誌備份出現Operating system error 32錯誤（The process cannot access the file because it is being used by another process）
SELECT /* top 50000 */
[Current LSN] , [Operation] ,[Context] ,
[Transaction ID] , [Transaction Name] ,[Begin Time] ,
[End Time] ,
[Flag Bits] ,
[PartitionId] ,
[Page ID] ,
[Slot ID] ,
[RowLog Contents 0] ,
[Log Record] ,
[RowLog Contents 1]
FROM sys.fn_dump_dblog(@P1, NULL, NULL, 11,
'M:\DB_BACKUP\LOG_BACKUP\xxxx_[2018-08-01_06h06m19_Wed]_logs.TRN',NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE [Current LSN] COLLATE SQL_Latin1_General_CP1_CI_AS >= @P2 COLLATE SQL_Latin1_General_CP1_CI_AS
AND ( ( [Operation] IN ( 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT' )
OR ( [Operation] = 'LOP_BEGIN_XACT' /* and [Transaction SID]=SUSER_SID('') */ )
)
OR ( ( ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_DELETE_ROWS' )
AND [Context] IN ( 'LCX_HEAP', 'LCX_CLUSTERED',
'LCX_MARK_AS_GHOST' ))
OR ( [Operation] IN ( 'LOP_INSERT_ROWS', 'LOP_MODIFY_ROW' )
AND [Context] = 'LCX_TEXT_MIX'
AND DATALENGTH([RowLog Contents 0]) IN ( 0, 14, 28 )
)
)
AND [PartitionId] IN ( 72057596166537216, 72057596166471680,
72057596166406144, 72057596327559168,
72057596160180224, 72057596361506816,
72057596119613440, 72057596119744512,
72057596119678976, 72057596120006656,
72057596119941120, 72057596120137728,
72057596545859584, 72057596119810048,
72057596231417856, 72057596418523136,
72057596441067520, 72057596381364224,
72057594562543616, 72057596104671232,
72057594585808896, 72057594585874432,
72057596242952192, 72057596168962048,
72057596054994944, 72057596055453696,
72057596035072000, 72057596055126016,
72057596091039744, 72057596381495296,
72057596381560832, 72057596524298240,
72057595958067200, 72057596469116928,
72057596462628864, 72057596454699008,
72057596241641472, 72057595958853632,
72057594563526656, 72057594563657728,
72057594563723264, 72057596502802432,
72057596484845568, 72057596484911104,
72057596575285248, 72057596554575872,
72057596485107712, 72057596485238784,
72057596485304320, 72057596485369856,
72057596551430144, 72057596485500928,
72057594601275392, 72057594621394944,
