管理 SQL Server 的事務日志

2019-11-03 08:35:02

字體：大中小

來源：轉載

供稿：網友

在 SQL Server 實例中的每一個數據庫，都有一個日志，它記錄著數據庫的所有更改。由于這個日志是獨立的，在更改發(fā)生之前，事務日志允許在硬件故障或應用程序錯誤時，對數據庫回滾或保存事務。由于它的角色的重要性，事務日志被保存在一個或多個與數據庫文件獨立的日志文件中；日志記錄是在內容的變更從緩存寫到數據庫文件中以前發(fā)生的。

對每個數據庫，事務日志支持以下操作：
當發(fā)出一個回滾操作或數據庫引擎檢測到一個錯時，進行事務回滾；
當服務器失改時，進行一個完整的事務回滾。這個事務在SQL Server 重啟時進行回滾。
當服務器失敗時，將未完成的事務寫入到日志文件，而不是數據文件中。當 SQL Server 重啟時，這些未完成的事務將會寫入數據文件。
當發(fā)生硬件錯誤時，對恢復的數據庫、文件組、文件或頁向前滾動到失敗點。事務將滾動到最后一個完整備份或差異備點。
對事務復制、數據庫鏡像、日志傳輸提供支持。

這些記錄事務日志的文件，會由數據庫引擎根據物理文件的實際情況，自動地分解為多個虛擬文件。數據庫引擎也會判斷在何時對哪些虛擬文件進行截斷。你可以指定物理日志文件的最小值和最大值，并可以配置擴展文件時的增長率。另外，你可以向日志增加物理文件、刪除文件、增加日志的大小或收縮日志。

在這篇文章中，我將解釋如何執(zhí)行這些任務，以開始管理你的事務日志，同時，我提供了一些例子以演示每一個任務如何工作。在這些例子中，我使用位于本地 SQL Server 2008 實例上的 EmplyeeDb 數據庫：

USE master;

IF EXISTS
(
  SELECT name FROM sys.databases
  WHERE name = 'EmployeeDB'
)
DROP DATABASE EmployeeDB;

CREATE DATABASE EmployeeDB
ON
(
  NAME = EmployeeDB_dat,
  FILENAME = 'C:/SqlData/EmployeeDb.mdf'
)
LOG ON
(
  NAME = EmployeeDB_log,
  FILENAME = 'C:/SqlData/EmployeeDb.ldf'
);

注意：我是在一個指定的位置上創(chuàng)建了一個數據庫文件，而不是 SQL Server 默認的位置。如果運行這段代碼，可以將數據庫定位到你想指定的位置。創(chuàng)建數據庫以后，我可以使用 SELECT...INTO 語句從AdventureWorks2008 數據庫創(chuàng)建表，并傳輸數據。

USE EmployeeDB;

IF OBJECT_ID('Employees', 'U') IS NOT NULL
DROP TABLE dbo.Employees;

SELECT BusinessEntityID,
  FirstName,
  LastName,
  JobTitle,
  PhoneNumber,
  EmailAddress,
  AddressLine1,
  AddressLine2,
  City,
  StatePRovinceName,
  PostalCode,
  CountryRegionName
INTO dbo.Employees
FROM AdventureWorks2008.HumanResources.vEmployee;

你不一定非要在這個數據庫中執(zhí)行這段代碼，但是它作為一個小小的測驗，有助于你學習事務日志的有關內容。如果你計劃使用另外的數據庫，只需要替換一下例子代碼中的數據庫名稱即可。

配置恢復模式

每個 SQL Server 數據庫都有一個恢復模式屬性，(the Recovery Model), 它指示事務日志如何記錄，如：事務日志是否可以被備份，以及恢復操作的許可類型。默認情況下，一個新的數據庫從 Model 數據庫繼承了一個恢復模式。當然，你也可以修改默認設置為其它模式。

你可以配置一個 SQL Server 數據庫的恢復模式為以下幾種之一：
簡單模式(Simple): 在這種模式下，事務日志的備份是不安全的，這意味著你不能對備份之后的事務日志進行管理。這種模式也會自動的擴展日志空間，所以幾乎不需要去管理事務日志的空間。然而，這種模式也是風險最大的一種模式，數據庫只能被恢復到最后一次備份的時間點，而在最后一次備份之后執(zhí)行的事務將會丟失。這種模式通常用于系統數據庫、或者用于測試和開發(fā)階段。或者是幾乎僅有只讀情況的數據倉庫數據庫。這種情況下，一些操作只是盡可能少的被記錄。
完整模式(Full): 由于這種模式可以提示指定時間點的恢復，因此它可以備份并且也應當進行備份。這種模式比簡單模式的風險要小。但是，在完整模式下，所有的操作都被完整的記錄，包括大數據量操作。這種模式適用于生產環(huán)境。
大數據量記錄模式(Bulk Logged): 這種模式可以看作是完整模式的補充，因為在這種模式下，大數量操作只是被最小化的記錄。例如，你可能要大量的加載數據但你不希望這些事務日志被記錄，因為你只是希望加載數據而已。在這種情況下，你可以在導入數據時，將模式由完整模式切換到大數據量模式，執(zhí)行完后，再恢復到完整模式。（需要注意的是：在切換回完整模式后，你應當做一次完整備份）
你可以在數據庫上通過執(zhí)行 ALTER DATABASE 語句，和指定 Set Recovery 來切換這些模式，例子如下：

USE master;

ALTER DATABASE EmployeeDB
SET RECOVERY FULL;

在上面的代碼中，我修改了 EmployeeDB 數據庫，并將恢復模式設置為完整模式 FULL。注意：由于默認的 model 數據庫是被配置為完整模式 Full ，這也意味著 EmployeeDB 數據庫被自動配置為完整模式，因為它是繼承自 model 數據庫的。所以，如果在你的服務器上， model 數據庫的默認設置沒有被更改的話，上面的的例子中 ALTER DATABASE 并不會改變什么，但是你要注意，當你將數據從簡單模式切換到完整模式時，有時候必須執(zhí)行一些其它步驟，例如進行一個完整備份。在SQL Server 在線教程中主題 "Considerations for Switching from the Simple Recovery Model" 描述了將數據庫的恢復模式從簡單模式轉換為完整模式或大容量模式時，有哪些步驟要執(zhí)行。
你也可以在 SQL Server Management Studio中設置恢復模式。在對象瀏覽器中右鍵單擊數據庫名稱，并選擇“屬性”，在數據庫屬性對話框中，單擊選項頁，并設置恢復模式屬性。

監(jiān)控日志文件
在維護數據庫的事務日志過程中，你可能需要經常獲取日志的一些信息以便檢查其設置或者已經使用了多少日志空間。一種方法是使用 sys.database_files 分類視圖，這個視圖返回關于數據庫文件的詳細信息，包括：文件類型、當前文件大小、以及文件增長設置。
在下面的例子中，我使用 sys.database_files 來獲取 EmployeeDB 數據庫的日志文件的數據：
USE EmployeeDB;

SELECT name,
  size, -- in 8-KB pages
  max_size, -- in 8-KB pages
  growth,
  is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'
這條語句返回了當前的文件大小（按8-KB的頁面大小），文件可增長到的最大大小 (同樣按 8_KB 的頁面大小)，增長率和是否按百分比增長標記。該標記指示數據庫文件的大小按何種方式增長。如果標記被設置為0，那么增長率就是 8-KB ，如果設置為 1，則按百分比增長。
上述代碼返回的結果大致如下：
Name            size  max_size  growth  is_percent_growth
EmployeeDB_log  128   268435456 10      1
如結果中所示，這條語句只返回一行記錄。這是因為 EmployeeDB 只配置了一個日志文件。上面的結果還反映了 EmployeeDB_log 的當前文件大小是 128 個8-KB 的頁面大小，它可以增長到 268,435,456 個8-KB 的頁面大小，增長率是按 10%的速率。
你還可以使用 DBCC SQLPERF 語句返回一個 SQL Server 實例的每個數據庫的事務日志信息，要獲取日志數據，你必須在參數中使用 LOGSPACE 關鍵字，如下所示：
DBCC SQLPERF(LOGSPACE);
這條語句返回以 MB 計算的日志大小，日志空間使用的百分比，以及你的 SQL Server 實例中每個數據庫的日志狀態(tài)。 EmployeeDB 數據庫的信息如下：
Database Name  Log Size (MB)  Log Space Used (%)  Status
EmployeeDB     0.9921875      40.05906            0
這個例子中 EmployeeDB 日志大約是 1 MB 大小，并且使用了 40% 的日志空間。
你也可以在 SQL Server Management Studio 中生成一個圖形化的報表，結果類似于執(zhí)行 DBCC SQLPERF 語句。方法是：在對象瀏覽器中，右鍵單擊數據庫名稱，選擇報表，再選擇標準報表，最后點擊磁盤利用率。

備份日志文件
如果你將數據庫的恢復模式配置為完全模式或大容量模式，你就應當有規(guī)律的備份事務日志，這樣你就可以截斷日志并釋放不活動的日志空間。備份也可以用于恢復數據庫（通常與數據庫備份一起使用）。
在事務日志備份之前，必須先執(zhí)行過一個數據庫的完整備份。通常，在我使用本文中的日志備份前，一般都先執(zhí)行下面的數據庫備份語句：
BACKUP DATABASE EmployeeDB
TO DISK = 'E:/DbBackup/EmployeeDB_dat.bak';
注意：執(zhí)行這段代碼時，確認指定路徑存在或指定一個另外的路徑。
執(zhí)行完數據庫備份后，我一般運行下面的數據修改語句，以使當前日志不包含已備份的內容：

USE EmployeeDB;

UPDATE Employees
SET JobTitle = 'To be determined';

UPDATE Employees
SET CountryRegionName = 'US'
WHERE CountryRegionName = 'United States';

DELETE Employees
WHERE BusinessEntityID > 5;
然后我再運行 DBCC SQLPERF 查看日志空間的統計信息，該語句返回下面的結果：
Database Name  Log Size (MB)  Log Space Used (%)  Status
EmployeeDB     0.9921875      64.41929            0
你可以看到，日志空間的使用率已從40%提升到接近65%。
備份完數據庫，你就可以備份事務日志了。執(zhí)行事務日志的備份，使用 BACKUP LOG 語句，并指定備份位置，如下：
-- back up transaction log
BACKUP LOG EmployeeDB
TO DISK = 'E:/LogBackup/EmployeeDB_log.bak';
同樣要注意路徑的問題。
這里我指定了備份路徑，然而， BACKUP 還支持其它選項，可以在SQL Server Books Online查看 “BACKUP (Transact-SQL)” 主題以獲得更多信息。
執(zhí)行完事務日志的備份以后， SQL Server 數據庫引擎會自動截斷不活動的日志空間。（注意：截斷事務日志只是移除了不活動的虛擬日志空間，并不減小文件大小）要減小日志文件，你應當對文件進行收縮。要檢查是否截斷了日志，請再次運行 DBCC SQLPERF 語句。現在的結果應當如下面所示：
Database Name  Log Size (MB)  Log Space Used (%)  Status
   EmployeeDB     0.9921875      44.88189            0
現在日志空間已下降到45%。

修改日志文件
你可以使用 ALTER DATABASE 語句來修改日志文件。在執(zhí)行語句時，必須使用適當的選項指定修改文件的原因（ MODIFY FILE clause）。除了給日志文件指定適當的邏輯名外，還有以下三個參數可用：
SIZE: 為日志文件指定大小。你可以指定以 KB, MB, GB, 或 TB為單位的文件大小，例如： 10 MB 或 1 GB。如果在添加文件時沒有指定文件大小，數據庫引擎使用默認的大小：1MB。新的文件大小必須比現在的大，否則在運行語句時會報錯。
MAXSIZE: 這個參數指定該文件最大可以是多大。同樣，你可以以 KB, MB, GB, 或 TB 為單位指定。如果你沒有指定最大的文件大小，文件會一直變大，直到占滿整個磁盤空間。
FILEGROWTH: 增量用于文件擴展時。可以KB, MB, GB, 或 TB 指定，或者用百分比。如 10%。如果沒有指定增量的單位，則默認為MB，如果沒有指定增量，則默認為10%。如果指定增量為0，則不允許自動增加。
下面用 ALTER DATABASE 語句來修改 EmployeeDB 數據庫的日志文件 EmployeeDB_log：
-- modify log file
ALTER DATABASE EmployeeDB
MODIFY FILE
(
    NAME = EmployeeDB_log,
    SIZE = 2MB,
    MAXSIZE = 200MB,
    FILEGROWTH = 10MB
);
如上所示，我在指定了日志文件的邏輯名稱以后，設置了文件的大小 (2 MB)，最大值 (200 MB)，和增量 (10 MB)。
執(zhí)行 ALTER DATABASE 語句后，可以查詢 sys.database_files 的分類視圖，以查看更改，結果如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   256   25600     1280    0
現在文件的大小是 256 個 8-KB 頁面，最大值 25,600 個 8-KB 增量是 1,280 個 8-KB 頁面。

收縮日志文件
前面已經說過，要截斷事務日志，你必須先備份日志，這樣數據庫引擎就可以自動的截斷不活動的記錄。然而截斷日志并不會減小它的文件大小。要減小日志文件的大小，必須收縮日志文件，它會移除一個或多個不活動的虛擬日志文件。
要收縮日志文件，你可以使用指定了日志文件名稱、目標大小（MB）的 DBCC SHRINKFILE 語句，下面的例子，使用DBCC SHRINKFILE 語句收綜 EmployeeDB_log 文件：
-- shrink log file
DBCC SHRINKFILE(EmployeeDB_log, 1);
上面例子中文件的目標大小是 1 MB (128 8-KB pages)，執(zhí)行該語句時，數據庫引擎會將文件收縮到指定大小，但必須是在有足夠的虛文件時。執(zhí)行完后，可以通過查詢 sys.database_files 分類視圖，以檢查是否文件已縮小，結果應當如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log     128    25600       1280       0
你可以看到，文件的大小已從 256 個 8-KB 減小到 128。如果數據庫引擎無法釋放空間，它會提示你一些建議的步驟，你可以按提示重新運行 DBCC SHRINKFILE 語句。

添加或刪除日志文件
如果你在增加日志文件的大小，一種方法是為日志增加文件。
你可以在執(zhí)行ALTER DATABASE語句時，使用 ADD LOG FILE 子句，在子句中，除了新的日志文件的邏輯名和物理名，你還可以指定以下參數：
SIZE: 日志文件的初始大小。你可以使用 KB, MB, GB, or TB為單位，如 10 MB 或 1 GB。如果不指定，則數據庫引擎指定為默認值 1MB。
MAXSIZE: 日志文件的最大值，單位同上。如果不指定此參數，日志文件會一直增長，直到占滿所在的磁盤空間。
FILEGROWTH: 增量，單位同上，也可以是百分比。如果指定了數字，但沒有單位，則按 MB 。如果未指定此參數，則使用 10% 。如果指定為0，則不允許自動增長。
下面的例子添加 EmployeeDB_log2 到 EmployeeDB 的事務日志中。
ALTER DATABASE EmployeeDB
ADD LOG FILE
(
    NAME = EmployeeDB_log2,
    FILENAME = 'C:/SqlData/EmployeeDB2.ldf',
    SIZE = 2MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 10%
);
注意：我先是指定了邏輯名和物理名，隨后定義了初始大小，最大值和增量。運行此語句以后，我可以通過查詢 sys.database_files 分類視圖來確認是否文件已被加入：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
EmployeeDB_log2  256   6400      10      1
結果顯示， EmployeeDB_log2 已被添加到數據庫，其初始大小是：256個 8-KB 頁面，最大值 6,400 個 8-KB 增量 10%.
你也可以使用 ALTER DATABASE 和子名 REMOVE FILE 來移除日志文件：
    ALTER DATABASE EmployeeDB
REMOVE FILE EmployeeDB_log2;
查詢 sys.database_files 分類視圖，返回的結果如下：

Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
EmployeeDB_log2  1     6400      10      1
你可能注意到： EmployeeDB_log2 還在，但大小是 1 個 8-KB 頁，這個物理文件已經被刪除，但邏輯文件還在，你必須通過備份事務日志來移除它。備份以后，再查詢  sys.database_files 分類視圖，結果如下：
Name             size  max_size  growth  is_percent_growth
EmployeeDB_log   128   268435456 10      1
顯示邏輯文件已移除。

總結
很明顯，事務日志在 SQL Server 數據庫中扮演著非常重要的角色，上面的內容指導您如何用它們來工作。上面關于事務日志，我沒講到的東西有：如何支持事務日志的復制、數據庫的鏡像和事務日志的發(fā)布，也沒有講到如何使用事務日志進行數據庫的恢復。這些內容每個都是一個專題，但至少你現在對事務日志有了一個基本的認識，這是基礎。不過，我還是強烈建議您認真閱讀 SQL Server 在線幫助上關于事務日志的不同主題和其它資源，這樣你就會對如何使用日志來工作，并最好的把握它。

上一篇：sql server視圖的作用

下一篇：SQL Server為何走上虛擬化之路？