Microsoft SQL Server交易記錄的應用

來源：http://www.study-code.com/database/sql-server/76961.htm

 

我們知道，SQL Server交易記錄主要是用來記錄所有事務對資料庫所做的修改，如果系統出現故障，它將成為最新資料的唯一來源。日誌的操作常有以下幾個應用：

一、交易記錄檔LDF的丟失

當我們不小刪除或者LDF檔案丟失的時候，資料庫只剩下MDF檔案，此時直接通過附 加MDF是無法恢複資料庫的，那我們怎麼樣才能恢複資料庫呢?我們可以把SQL Server的記錄檔分為兩種形式：一類是無活動事務的日誌，另一類是有活動事務的日誌，我們分別根據兩種情況來進行資料庫恢複。

1、無活動事務的日誌恢複

當檔案並沒有發生活動性的日誌，我們就可以很容易的利用MDF檔案就可以直接恢複資料庫了，具體操作方法如下：

(1)資料庫要是沒有日誌，就會處於置疑的狀態，我們先可以通過企業管理器中在對應資料庫中點擊右鍵，然後在“所有任務”下選擇“分離資料庫”把資料庫進行分離;

(2)利用MDF檔案附加資料庫產生新的記錄檔，可用企業管理器中資料庫點擊右鍵選擇“所有任務”下的“附加資料庫”把資料庫附加上。

這樣就可以直接恢複好資料庫了，而如果資料庫的記錄檔中含有活動事務，利用此方法就不能恢複資料庫，所以得使用下面的方法。

2、有活動事務的日誌恢複

當日誌發生了事務的記錄，丟失的時候，我們採用如下的方法來實現：

(1)建立一個同名的資料庫，如原資料庫名為MYDB，然後停止SQL Server伺服器，再把資料庫主要資料MDF檔案移走，然後重新啟動SQL Server伺服器，建立一個同名的資料庫MYDB，然後再停止SQL Server伺服器，把移走的MDF檔案再覆蓋回來，然後再重新啟動SQL Server伺服器，在預設的情況下，系統資料表是不允許被修改的，我們需要運行以下語句才可以，在查詢分析器中，選擇Master資料庫，然後執行：

Sp_configure 'allow updates',1

Reconfigure With Override

接著運行以下語句，把Sysdatabases表中MYDB資料庫的status屬性設為‘37268’,把MYDB資料庫設定為緊急模式。

update sysdatabases set status=32768 where name=’MYDB’

然後再把資料庫MYDB設定為單一使用者模式，然後重啟SQL Server伺服器，並把資料庫MYDB設為單一使用者模式

Sp_dboption 'MYDB','single user', 'true'

再運行以下語句，檢查資料庫MYDB

DBCC CHECKDB(‘MYDB’)

(2)還原資料庫的狀態

運行以下語句，就可以把資料庫的狀態還原：

Update Sysdatabases Set status=28 Where name=’MYDB’

Sp_Configure ’allow updates’,0

Reconfigure With Override

此時的資料庫仍不能工作，還要進行以下的操作，才能恢複。

(3)利用DTS的匯入匯出嚮導，把資料庫MYDB匯入到一個建立資料庫 MYDBNEW中，然後建立一個資料庫MYDBNEW，右擊MYDBNEW，選擇“所有任務”下的“匯出資料”功能，開啟匯入嚮導，把表結構、資料檢視和 預存程序匯入到MYDBNEW中，然後再用此功能把MYDBNEW庫替換成原來的MYDB庫即可。

可以知道，恢複一個有活動事務的日誌是麻煩多了，所以在資料庫維護的時候，切不要小看交易記錄。

二、事務在不斷增大的時候如何縮小日誌

當資料如在頻繁修改或者刪除的同時，事務的日誌就會不斷的增加，甚至超過了碰盤的大小，這時候就不能因此而直接刪除了交易記錄的LDF檔案，否則可能會帶來很大的麻煩。為了避免這種情況，我們需要有如下的操作：

(1)盡量避免tempdb 日誌與使用者資料庫日誌放在同一磁碟上，tempdb 資料庫和交易記錄具有足夠的空間來處理索引操作。不能在索引操作完成之前截斷 tempdb 交易記錄。

(2)通過執行下列命令來縮小交易記錄

DBCC SHRINKDATABASE

DBCC SHRINKFILE

操作會立即嘗試將物理記錄檔收縮為所要求的大小。

如果虛擬記錄檔中的邏輯日誌未超出 target_size 標記，則釋放 target_size 標記之後的虛擬記錄檔，並成功完成 DBCC 語句，不顯示任何資訊。

如果虛擬日誌中的邏輯日誌超出了 target_size 標記，SQL server Database Engine 將釋放儘可能多的空間並顯示一個資訊性訊息。該訊息告訴您必須執行什麼操作來從檔案尾部的虛擬日誌中刪除邏輯日誌。執行完該操作後，可以重新發出 DBCC 語句以釋放剩餘的空間。

DBCC SHRINKFILE 語句還顯示一個資訊性訊息，指出它不能釋放所要求的全部空間，並告訴您可以執行 BACKUP LOG 語句來釋放剩餘的空間。

三、交易記錄的還原

交易記錄在還原的時候可以選擇三種復原模式：簡單模式、完整模式和大容量記錄模式。

簡單復原模式

此模式簡略地記錄大多數事務，所記錄的資訊只是為了確保在系統崩潰或還原資料備份之後資料庫的一致性。

由於舊的事務已提交，已不再需要其日誌，因而日誌將被截斷。截斷日誌將刪除備份和還 原交易記錄。但是，這種簡化是有代價的，在災難事件中有遺失資料的可能。沒有記錄備份，資料庫只可恢複到最近的資料備份時間。如果您使用的是 sql server Enterprise Edition，需要考慮此問題。此外，該模式不支援還原單個資料頁。

完整復原模式

此模式完整地記錄了所有的事務，並保留所有的交易記錄記錄，直到將它們備份。在 sql server Enterprise Edition 中，完整復原模式能使資料庫恢複到故障時間點。

大量記錄復原模式

此模式簡略地記錄大多數大容量操作(例如，索引建立)，完整地記錄其他事務。

大容量日誌恢複提高大容量操作的效能，常用作完整復原模式的補充。大容量日誌恢複模 式支援所有的恢複形式，但是有一些限制，備份包含大容量日誌記錄操作的日誌時，需要訪問資料庫內的所有資料檔案。如果資料檔案不可訪問，則無法備份最後的 交易記錄，而且該日誌中所有已提交的操作都將丟失。