深入SQLServer日誌收縮
問題背景
SQLServer的日誌是DB級別的這點和MySQL不同,多個DB就需要維護多個日誌檔案並且每個DB的日誌檔案可以有多個,所以從空間資源的角度來說這給雲上的使用者帶來一些困擾,畢竟在雲上磁碟空間是要收費的,除了效能他們也非常關心這些日誌何時截斷重用、何時收縮
問題探討
在理解事務日誌基本概念的基礎上我們來做個有關收縮的測試:
--1. 構建測試用的日誌
-- tpcc是我的測試DB,ITEM表是之前生成的一個測試表,這裡不需要關心表內容
USE [tpcc]
GO
DECLARE @VALUE INT
SET @VALUE = 0
WHILE @VALUE <= 100
BEGIN
SELECT * INTO ITEM_2 FROM ITEM
DROP TABLE ITEM_2
SET @VALUE = @VALUE + 1
END
GO
--2. 觀察當前等待日誌截斷重用的原因和每個DB的日誌使用量
select log_reuse_wait_desc from sys.databases where name=`tpcc`
go
dbcc sqlperf(logspace)
go
--3. 備份資料和日誌
BACKUP DATABASE TPCC TO DISK=`D:BACKUPTPCC.BAK` WITH COMPRESSION,INIT
BACKUP LOG TPCC TO DISK=`D:BACKUPTPCC.TRN` WITH COMPRESSION,INIT
--4. 再次觀察當前等待日誌截斷重用的原因和每個DB的日誌使用量
select log_reuse_wait_desc from sys.databases where name=`tpcc`
go
dbcc sqlperf(logspace)
go
--5. 收縮日誌
USE [tpcc]
GO
dbcc shrinkfile(log)
--6. 檢視收縮後的日誌使用量
dbcc sqlperf(logspace)
go
這一步也可以通過上一步收縮的結果計算出來(90112*8K = 704 MB);
現在在理解基本概念的基礎上產生了第一個疑問,為什麼1G的日誌使用了4%卻只能收縮到704MB?
為了解答這個問題我們需要引入另一個概念 Virtual Log Files(VLF),SQLServer為了方便日誌管理,邏輯上將事務日誌劃分為多個虛擬日誌檔案,我們要討論的收縮就是以虛擬日誌檔案為單位操作的。
具體可以參考日誌的結構
我們再來重複一次剛才的測試,這次加入對VLFs的記錄觀察;
在剛才的1、3、5步驟之後記錄如下SQL返回結果,幫助後續分析;
USE [tpcc]
GO
dbcc loginfo
步驟1結束後
步驟3結束後
步驟5結束後
這裡跟收縮日誌最相關的是Stats欄位,0表示inactive,2表示active,active狀態的VLF通過checkpoint+日誌備份可以轉化為inactive,但有一個原則是header(可理解為最新的活動日誌)部分只能後推或迴繞(wraps around)不能move forward(跟offset相關);
在開始備份前有11個活動VLF,checkpoint(資料備份的第一階段)+ 備份日誌後只有最新的VLF處於活動狀態(日誌截斷並標記老的VLF為inactive-可重用),收縮後釋放掉了未使用的VLF;
截止到這裡截斷和收縮實際已經達到了充分利用空間的目的,雖然直觀看物理檔案沒有下降多少但新的日誌已經開始迴繞;但即便如此一些客戶還在追求物理檔案的進一步減少,實際想達到這個目的也很簡單,依據之前講的header部分已經迴繞,再次備份收縮就可以了;
--再次備份
backup database tpcc to disk=`d:ackup pcc.bak` with compression,init
backup log tpcc to disk=`d:ackup pcc.trn` with compression,init
--再次收縮
USE [tpcc]
GO
dbcc shrinkfile(log)
--觀察VLF
USE [tpcc]
GO
dbcc loginfo
為什麼沒有收縮最後一個inactive的VLF跟這個DB的日誌初始化大小、shrinkfile的引數有關,簡單說不回收最後一個VLF已經可以達到初始化的大小;
我們也可以通過VLF的總大小和物理檔案對比做一個驗證:
--通過FileSize計算要加8KB的頁頭 67043328+67043328+67043328+67043328+67043328+67043328+67043328+67559424+8192=536870912B=512MB
--通過最後一個偏移量算
469311488+67559424=536870912B=512MB
結論和建議
- 日誌截斷依賴於checkpoint和日誌備份(FULL模式)
- 日誌截斷的含義是把VLF標記為可重用
- 日誌收縮的多少需要看VLF的header和VLF的數量以及大小而不是通過sqlperf返回的結果判斷(這也是很多DBA和使用者誤解的地方)
- VLF的數量是事務日誌建立時初始化好的一個初始值後續會隨日誌增長和增加,大小跟日誌檔案的大小、增長速度相關沒有恆等的計算方法
- 在非雲場景下(傳統使用者),不建議使用者頻繁做日誌收縮去回收空間,因為這種收縮是有很大開銷的,但肯定也不會一直增長,我們通過頻繁的日誌備份做截斷、迴繞日誌檔案以達到節省空間的目的;在雲場景下(RDS),依然也不建議頻繁收縮,但面對一些希望用其它資源換空間資源且業務場景允許的情況下,建議這部分使用者使用OpenAPI定製化自己的備份策略
相關文章
- sqlserver 事務日誌過大 收縮方法解決方案SQLServer
- SQL Server 收縮日誌SQLServer
- SQL SERVER 2005 日誌收縮SQLServer
- SqlServer2005 收縮資料庫日誌(又一方法)SQLServer資料庫
- SQL Server 收縮事務日誌的方法SQLServer
- 有關事務日誌截斷和收縮
- 資料庫收縮,刪除日誌,自動收縮,資料庫分離附加資料庫
- 清除SqlServer日誌SQLServer
- sqlserver收縮資料庫、收縮資料檔案的操作SQLServer資料庫
- sqlserver 資料庫收縮的方法SQLServer資料庫
- [zt] SQL Server 事務日誌的收縮和截斷SQLServer
- SQLServer資料庫日誌清理 清除sqlserver2005日誌SQLServer資料庫
- SQL Server如何截斷(Truncate)和收縮(Shrink)事務日誌SQLServer
- [轉載] SQL Server事務日誌的收縮和截斷SQLServer
- 清空SqlServer日誌檔案SQLServer
- sqlserver的日誌備份SQLServer
- linux _壓縮日誌Linux
- SQL SERVER 資料庫日誌收縮整理 三種方法軼事分離資料庫而是清空日誌三是截斷日誌SQLServer資料庫
- Sqlserver:代理錯誤日誌,知多少?SQLServer
- SQL 2008 R2 收縮日誌,不用修改簡單模式SQL模式
- 縮小日誌大小,截斷日誌;然後shrink
- 壓縮SQL SERVER日誌程式碼SQLServer
- MySQL 壓縮二進位制日誌MySql
- Sql Server 2005 日誌壓縮SQLServer
- 使用logrotate來壓縮日誌(轉)logrotate
- 深入分析Oracle日誌檔案Oracle
- SQLSERVER 事務日誌的 LSN 到底是什麼?SQLServer
- SQLSERVER事務日誌已滿 the transaction log for database 'xx' is fullSQLServerDatabase
- SQLServer的頁壓縮SQLServer
- SQL SERVER 2008 R2 日誌檔案過大處理方法 (收縮及刪除)SQLServer
- Blitz4j - 可伸縮的日誌框架框架
- sqlserver關於日誌傳輸log shipping的總結SQLServer
- SQLSERVER 2005 清除DB日誌和 ERRORLOG 的方法SQLServerError
- sqlserver日誌檔案總結及充滿處理 (摘)SQLServer
- sqlserver日誌檔案總結及充滿處理(轉)SQLServer
- Sqlserver使用遊標迴圈查詢所有sqlserver error日誌帶有Exception的報錯SQLServerErrorException
- Sqlserver表和索引壓縮SQLServer索引
- oracle空間收縮Oracle