[SQL SERVER][Maintain]管理交易記錄檔 (2)
交易記錄檔對於維持資料庫的交易完整性相當重要，
當災難發生時，可以透過交易記錄檔還原資料庫到任意時間點(依交易記錄檔LSN)。
在SQL2000大家一定對 truncate_only 選項不陌生，
backup log with truncate_only可以強制截斷交易記錄檔，
但 truncate_only 是不會實際備份交易記錄檔內容(單純丟棄)，
所以當你想透過交易記錄檔還原到某一時間點時，
你將發現無法順利還原，
所以SQL2008開始後完全移除該選項(從DR角度來看該選項是相當危險的)，
雖然正確強制截斷交易記錄檔作法，應該是將資料庫復原模式切換到 simple，
但如果你不打算走正途正確方法，依然還是可以利用其他暗黑方法來達到強制截斷交易記錄檔，
下面我將簡單示範一下這詭異方法。
正常備份:完整->差異
select [current lsn], Operation,dblog.[Transaction Name],[Transaction ID],
dblog.[Begin Time]
from fn_dblog(null, null) dblog
LSN都是循序，雖然checkpoint執行完成，但還是沒有截斷交易記錄檔非活動交易內容。
執行交易記錄檔備份
可以看到當我執行備份交易記錄檔後，
自動截斷非活動(舊)交易記錄，這時交易記錄檔內容只剩下2筆，
這證明資料庫完整復原模式下，如果沒有備份交易記錄檔，
將不會自動截斷交易記錄檔內容，並且也無法重用內部空間。
我再執行一次 完整->差異 備份，可以更清楚這樣的狀況。
再次執行交易記錄檔備份
資料庫在完整復原模式下，執行備份交易記錄檔會自動截斷交易記錄檔並標示內部空間重用，
當然相關備份檔案也都會使用硬碟空間。
假設現在你的交易記錄檔已經吃光所有硬碟空間了(資料庫也變唯讀了)，
這表示你也不能備份交易記錄檔截斷舊(非活動交易)紀錄重用內部空間(假設也沒有其他storage)，
這時可以嘗試使用以下方法來強制截斷交易記錄檔(不建議)，並壓縮檔案嘗試釋放硬碟空間。
目前交易記錄檔內容和LSN
執行 BACKUP log ricotest1 to disk='nul'
截斷交易記錄檔非活動交易內容，且不佔用任何硬碟空間。
由於這時硬碟沒有空間了，所以得採用DBCC SHRINKFILE 壓縮交易記錄檔，嘗試釋放硬碟空間。
DBCC SHRINKFILE(ricotest1_log, 1) (少用 DBCC SHRINKFILE )
我這裡還是要強調一次，這樣處理肥大交易記錄檔過程是相當不建議的。
來看一下bakupset history
SELECT begins_log_chain ,
DATABASE_NAME ,TYPE ,RECOVERY_MODEL ,FIRST_LSN ,LAST_LSN ,
DATABASE_BACKUP_LSN ,DIFFERENTIAL_BASE_LSN ,IS_COPY_ONLY,
backup_start_date,backup_finish_date,backup_size
FROM msdb.dbo.backupset
where DATABASE_NAME='ricotest1'
可以看到一開始交易記錄檔LSN和資料庫備份一樣，
這表示該交易記錄檔備份是從整個資料庫一開始備份的，
而且每個交易記錄檔的LSN都是循序的。
你會發現我這裡一直強調交易記錄檔都是循序的，
這表示SQL Server寫入交易記錄檔都採取循序寫入，
而讀取資料檔案(data files)是採取隨機操作，
所以你看一些測試SQL Server IOPS文章都會測試硬碟循序、隨機寫入和讀取，
意味者，交易記錄檔和資料檔案分開存放不同儲存媒體對效能絕對是有幫助的(硬碟讀寫頭停留位置，降低讀寫頭延遲)。
backup to nul device 命令目的
在某些情況下使用這命令是有意義的，好比測試I/O備份效能(吞吐量)。
一般執行資料庫備份時，會先從資料庫讀取資料，然後停止讀取後再寫入儲存媒體，
中間過程大概就是 讀取資料庫->暫停->寫入媒體 一直重複下去，
這樣的過程不利於測試I/O備份效能，
透過 to nul device 可以不需要暫停讀取階段，
直接完整讀取後並寫入儲存媒體，另一方面還可以修正因為caching所造成假象的效能結果。
BACKUP database ricotest1 to disk='nul'
該I/O執行備份時，一秒可以處理 70.493mb，每秒可以處理約 9023 pages。
備份到正常儲存媒體
BACKUP database ricotest1 to disk ='D:\ricotest1_FULL.bak'
耗用較多時間，兩者差距 30.789 秒。
參考
DBCC SHRINKFILE (Transact-SQL)