T-SQL筆記20_肥大的ldf檔案_查詢便超慢_壓縮SQL Server ldf

 



當SQL Server在查詢時遇到很慢的情況
但在異端server又執行的飛快正常時候
可能就要去檢查是不是交易紀錄檔過大導致
情境是backup到本機在做測試時發覺慢到不像話


query 一個查詢竟然要耗費6分鐘
這驚人的耗時讓我懷疑人生

在這篇論壇貼文討論中也有滿多高手提到的
https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139592

而通常product線上的主機都會定時做備份跟purge
才不會愈來愈大


在SQL Server中每一個資料庫都有兩種形式的資料檔
資料檔MDF 跟 記錄檔LDF
所有資料庫的交易紀錄都會優先寫入至LDF
然後才從LDF傳至MDF
這就是底層資料庫交易紀錄的觀念

在SQL Server中管理LDF方式
主要跟資料庫復原模式設定有關

簡單復原模式(Simple Recovery model)
每次的checkpoint動作發生時,SQL Server會截斷已完成或回複的交易紀錄,
換言之,SQL Server可以重複使用交易紀錄的儲存空間。
此模式下DBA只能執行完整備份與差異備份。

1.交易記錄的管理負擔降至最低,因為交易記錄不會備份。
2.如果資料庫已損毀,簡單復原模式就會面臨工作損失風險。
3.資料只能復原至遺失資料的最近一次備份。因此,在簡單復原模式下,備份間隔不應該太長,以免損失大量資料。因此若無法接受損失最新變更的實際執行系統而言,簡單復原模式
不是適當的選擇。建議使用完整復原模式。
4.對於使用者資料庫而言,簡單復原模式,通常適合用於測試及開發資料庫,或是唯讀資料佔大部分的資料庫,例如資料倉儲。




完整復原模式(Full Recovery model)
所有交易紀錄會被SQL Server保存在該資料庫的紀錄檔內。DBA可以針對該資料庫,進行完整備份、差異備份和交易紀錄備份。


大量紀錄復原模式(Bulk-logged Recovery model)
除了BULK行為(載入大量資料或建立索引)所產生的交易紀錄之外,
絕大多數的交易紀錄都會被保留,所以此模式無法保證能將資料庫還原到過去的任何時間,因為沒有BULK成為的交易紀錄。


基本處裡SOP:
Step1.把DB復原模式更換為簡單模式。
Step2.執行記錄檔壓縮(也就是所謂的 shrink)。
Step3.將DB復原模式更換回完整模式。


這是目前的ldf

可以看到檔案大到100多GB
真是災難

Step1.把DB復原模式更換為簡單模式。

圖形化介面派:

對「資料庫」(Database) ->右鍵->屬性(Properties)


選擇 「選項」(Options) 將資料庫復原模式(Recovery model)換為簡單模式


指令派:
ALTER DATABASE [DB名] SET RECOVERY SIMPLE 
GO



Step2.執行記錄檔壓縮(也就是所謂的 shrink)。

圖形化介面派:
對「資料庫」(Database) ->右鍵 ->工作(Tasks) ->壓縮(Shrink) ->檔案(Files)




把「檔案類型」(File type)更換為->「記錄檔」(Log)






至壓縮動作(Shrink action)下改選擇
->釋放未使用的空間之前,先重新組織頁面(Reorganize page before releasing unused space)
->輸入壓縮後檔案大小,這裡輸選的大小為 0MB。


指令派:
DBCC SHRINKFILE (ldf檔名, 0);

可以看到log占用從144G變成剩下3MB而已


Step3.將DB復原模式更換回完整模式。


參考Step1.作法








Ref:
Function of Simple Recovery Model in SQL Server Database
https://www.datarecovery.institute/simple-recovery-model-in-sql-server-database/

Choosing the Right Backup Strategy for Dynamics CRM
https://www.sherweb.com/blog/dynamics-365/backup-strategy-dynamics-crm/

SQL Server Transaction Log and Recovery Models
https://www.sqlshack.com/sql-server-transaction-log-and-recovery-models/

DBCC SHRINKFILE (Transact-SQL)
https://docs.microsoft.com/zh-tw/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

SQL Server 無敵手冊第七篇- 淺談SQL Server備份基本概念
https://ithelp.ithome.com.tw/articles/10028383






留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題