[SQL Server] Database mirroring 卡住時如何處理

Database 的 log_reuse_wait_desc 卡在 "Database Mirroring" 時怎麼處理?

SELECT log_reuse_wait_desc
FROM sys.databases;  


 

以往我們遇到這種case時,因為"Database Mirroring"狀態卡太久會造成log size成長到好幾十GB,VLF(Virtual Log File)數量也會暴增,導致backup files size越來越大,造成我們後續維護困難...

其中一個的做法是把mirroring斷掉後,將principal DB 從full mode設為simple mode後,直接shrink,mirroring重接。

但我們為了確保backup file完整性,不會遺失任何一段時間,所以我們會選擇執行下面步驟:

Step 1. 斷掉mirroing

alter database [DBname] set partner off 

Step 2. 執行dbcc loginfo檢查是否能shrink log size以減少VLF數量

DBCC LogInfo('dbname')


Results的數量為VLF的數量,

Status:'0' 表示等待被用,'2' 代表正在被用

通常卡在此狀態,Status會幾乎都顯示’2’,則表示此DB shrink不下來,故我們會接著做Step3,若下面行數很多status為0可以直接做Step4.

PS: 平常若需要shrink Log時若shrink不下來,是因為最下面幾行Status=2,表示空間無法壓縮,因壓縮會從最後面的VLF開始壓,若後面的VLF正在被使用則此shrink無效

Step 3. 做Log Backup

BACKUP LOG [DBname] TO DISK = N'D:\Dbname.bak' 

Step 4. 執行Step 2在檢查一次後, 做shrink Log

USE [Dbname]
GO
DBCC SHRINKFILE (N'Logicname' , 0, TRUNCATEONLY)
GO

Step 5. 將Step 3做的Log backup去mirroring DB restore後將mirroring接回來

--在mirroring Server執行
RESTORE LOG  Dbname FROM 
 DISK = N'D:\Dbname_Log.bak' WITH  FILE = 1, 
  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Alter database Dbname set partner = 'TCP://principal Server name:7777'
GO

--在principal Server執行
Alter database Dbname set partner = 'TCP://mirroring Server name:7777'

第三個方法在網路上比較少人這樣做,在此給大家參考,但因為此方法我們第一次執行時就導致SQL service整個hang住進不去,所以目前我們不實施此方法去解決mirroring卡住的問題...

前提: 我們的mirroring作業模式為非同步資料庫鏡像 (高效能模式, High-Performance Mode)

Step 1. 查看mirroring狀態

use [msdb]
go
EXEC sp_dbmmonitorresults 'DBname',2,1

多執行幾次若是正常的mirroring狀態unsent_log會是'0',但若mirroring狀態是卡住傳不過去mirroring DB的話,每執行一次上面的語法會發現unsent_log越來越大量

unsent_log: principal DB發送mirroring DB時,所有未發送到mirroring DB的Log數量(send queue)。
unrestored_log: 在mirroring DB中,已經傳送過來但還沒被寫入mirroring DB的Log數量會存放於此(redo queue)。

Step 2. 查看VLF數量及Status是否正常

DBCC LogInfo('dbname')

Step 3. 將mirroring切成高安全性模式(High-Safety Mode)

ALTER DATABASE DBname SET SAFETY ON

Step 4. 回頭檢查Step 2 & Step 3的狀態,如果有重新開始動了就能將mirroring模式切回原本狀態

ALTER DATABASE DBname SET SAFETY OFF

 

參考文獻:

https://docs.microsoft.com/zh-tw/sql/database-engine/database-mirroring/database-mirroring-operating-modes

https://docs.microsoft.com/zh-tw/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-dbmmonitorresults-transact-sql