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復原模式更換為簡單模式。
圖形化介面派:
選擇 「選項」(Options) 將資料庫復原模式(Recovery model)換為簡單模式
指令派:
ALTER DATABASE [DB名] SET RECOVERY SIMPLE
GOStep2.執行記錄檔壓縮(也就是所謂的 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
留言
張貼留言