MDF and LDF in MSSQL Server

hemingwaylee ,

Master Database File (mdf)主要存放data、table、stored procedure、index和trigger等等都存放於此。Log Database File (ldf)主要存放logs，比方說transaction log、error log以及user的一些操作都會記錄在ldf裡面。

我們可以直接用mdf和ldf來備份和還原database，也可以用bak file來備份和還原database。通常我們建議用backup產生bak file的方式來備份和還原database；因為如果用mdf和ldf來備份和還原database的話，需要先把SQL Server相關的Service都停下來。

再來我們來看一下ldf裡面都放什麼東西，我們可以透過fn_dblog()來看：

Select * from ::fn_dblog(null,null)

或是

DBCC LOG('my_table', 3)

但實際上並不容易理解這是內容是什麼(可以參考這篇文章來看如何解讀)，所以通常用3rd party的tool來看ldf file還是比較方便。

值得一提的是，如果SQL script沒有寫好就有可能造成tempdb.mdf和templog.ldf size長大的問題。我們先來做個實驗，原本的tempdb.mdf和templog.ldf的size如下：
tempdb_grow_ori

然後我們執行以下SQL script：

CREATE TABLE #tbl
(
    id INT IDENTITY(1,1) NOT NULL,
    fname VARCHAR(255),
    mname VARCHAR(255),
    lname VARCHAR(255)
)

DECLARE @i INT
SET @i = 0
WHILE @i < 100000
BEGIN
    INSERT INTO #tbl
    VALUES(CONVERT(VARCHAR(255), NEWID()), CONVERT(VARCHAR(255), NEWID()), CONVERT(VARCHAR(255), NEWID()))

    SET @i = @i + 1
END

我們把資料塞到#tbl這個temp table裡面去，再觀察tempdb.mdf和templog.ldf的size變化如下，正常來說一般對database的操作都會寫log到ldf裡面。
tempdb_grow_normal

但是如果我們的SQL script中有uncommitted的transaction：

CREATE TABLE #tbl
(
    id INT IDENTITY(1,1) NOT NULL,
    fname VARCHAR(255),
    mname VARCHAR(255),
    lname VARCHAR(255)
)

BEGIN TRAN
    DECLARE @i INT
    SET @i = 0
    WHILE @i < 100000
    BEGIN
        INSERT INTO #tbl
        VALUES(CONVERT(VARCHAR(255), NEWID()), CONVERT(VARCHAR(255), NEWID()), CONVERT(VARCHAR(255), NEWID()))

        SET @i = @i + 1
    END
--COMMIT TRAN

執行之後，再觀察tempdb.mdf和templog.ldf的size變化如下：
tempdb_grow_trans

這段uncommitted的transaction造成ldf長大的問題。所以在寫SQL statement時必須要特別留意是否忘了寫commit或是沒有做error handling而在exception發生時，沒辦法run到commit的statement。

通常在重新啟動SQL Server Service後，tempdb.mdf和templog.ldf的size就會恢復正常。如下圖：
tempdb_grow_ori

當然，如果是因為SQL script寫不好而造成ldf size長大的問題，就算重啟SQL Server Service或是透過SQL script去shrink database，ldf的size在一段時間之後還是會再長回來。如果沒有寫錯的SQL script，在正常使用的狀況ldf的size還是會正常的增長，這時候可以參考這篇文章，看看是要改用simple recovery mode還是要將ldf做backup。

最後值得一提的是，在ldf裡面的transaction log主要是用來確保database ACID的特性；另一方面，有了transaction log，我們就可以用來還原database到某個特定的時間點(如果不小心刪了或修了database裡面的一些資料，我們就有還原database到特定時間點的需求)。

還原的方法可以參考這篇文章。而logging和recovery的技術細節可以參考這篇文章。

Leave a comment