錯誤描述：數(shù)據(jù)庫的事務(wù)日志已滿 (SQL Server Error 9002)。
千萬不要直接進行日志文件收縮是整，除非你不需要考慮數(shù)據(jù)庫的恢復(fù)文件肖揣。
事務(wù)日志文件（Transaction Log File）是用來記錄數(shù)據(jù)庫更新情況的文件，擴展名為ldf浮入。當(dāng)出現(xiàn)下面的情況時龙优，就會報“事務(wù)日志已滿”的錯誤：
- 日志文件沒有設(shè)置了自動增長，當(dāng)記錄超過初始大惺滦恪彤断；
- 日志文件設(shè)置了自動增長，“最大文件大小”設(shè)置了具體的大幸准！（如2000M）瓦糟，當(dāng)文件達到設(shè)置的大小時；
- 日志文件設(shè)置了自動增長赴蝇，最大文件大小”設(shè)置了“無限制”菩浙，當(dāng)磁盤已滿時；
如果出現(xiàn)這種情況標(biāo)識的日志文件已滿，那么當(dāng)執(zhí)行更新或者插入新記錄時就會報錯劲蜻，此時如果通過系統(tǒng)視圖 sys.databases 中的 log_reuse_wait_desc 列可以看到日志中的空間無法重用的原因陆淀。
SELECT log_reuse_wait , log_reuse_wait_desc from sys.databases where name='database name'
執(zhí)行上面的語句得到的結(jié)果：
關(guān)于事務(wù)日志
事務(wù)日志，用于記錄所有事務(wù)以及每個事務(wù)對數(shù)據(jù)庫所做的修改先嬉。 如果系統(tǒng)出現(xiàn)故障轧苫，你將需要依靠該日志將數(shù)據(jù)庫恢復(fù)到一致的狀態(tài)。
每個事務(wù)都在事務(wù)日志中保留空間疫蔓，以確保當(dāng)出現(xiàn)由回滾語句或遇到錯誤引起的回滾時含懊，有足夠的日志空間。 保留的空間量取決于在事務(wù)中執(zhí)行的操作衅胀，但通常等于用于記錄每個操作的空間量岔乔。 事務(wù)完成后將釋放此保留空間。
數(shù)據(jù)庫的日志文件被分成多個虛擬日志文件（VLF）滚躯，而VLF的狀態(tài)有4種：
- active雏门， 示VLF中存在活動的事務(wù)（即未完成的事務(wù)）。
- recoverable掸掏，表示VLF中的事務(wù)全部已經(jīng)完成茁影，但是某些操作（例如數(shù)據(jù)庫鏡像、復(fù)制等）還需要用到這些數(shù)據(jù)丧凤，因此不可以被覆蓋募闲。
- reusable，表示VLF中的數(shù)據(jù)已經(jīng)不需要了愿待，可以被覆蓋蝇更。
- unused，表示VLF從未被使用呼盆。
我們可以執(zhí)行下面的語句查看相關(guān)信息：
DBCC loginfo
查詢結(jié)果如下圖：
Status列就是VLF的狀態(tài)年扩，0代表reusable或者unused，2則代表active或者recoverable访圃；只有處于reusable和unused狀態(tài)時厨幻，VLF才可以通過日志截斷來釋放空間。
日志截斷
日志截斷將釋放日志文件的空間腿时，以便由事務(wù)日志重新使用况脆。 必須定期截斷事務(wù)日志，防止占滿分配的空間批糟。 幾個因素可能延遲日志截斷格了，因此監(jiān)視日志大小很重要。 某些操作可以最小日志量進行記錄以減少其對事務(wù)日志大小的影響徽鼎。
日志截斷從 SQL Server 數(shù)據(jù)庫的邏輯事務(wù)日志中刪除不活動的虛擬日志文件 (VLF)盛末，弹惦，釋放邏輯日志中的空間以便物理事務(wù)日志重用這些空間。 如果事務(wù)日志從不截斷悄但，它最終將填滿分配給物理日志文件的所有磁盤空間棠隐。
為了避免空間不足，除非由于某些原因延遲日志截斷檐嚣，否則將在以下事件后自動進行截斷：
- 簡單恢復(fù)模式下助泽，在檢查點之后發(fā)生。
- 在完整恢復(fù)模式或大容量日志恢復(fù)模式下嚎京，如果自上一次備份后生成檢查點嗡贺，則在日志備份后進行截斷（除非是僅復(fù)制日志備份）。
日志截斷并不減小物理日志文件的大小鞍帝。 若要減少物理日志文件的物理大小诫睬，則必須收縮日志文件。 如果在日志收縮后還需要存儲空間膜眠，則會再次增加事務(wù)日志，導(dǎo)致在增加日志操作期間產(chǎn)生性能開銷溜嗜。
在下面這些情況下宵膨，日志會自動截斷：
- 系統(tǒng)設(shè)置日志恢復(fù)模型為簡單。
- 創(chuàng)建數(shù)據(jù)庫以來從未進行過完全備份炸宵。
- 曾經(jīng)使用BACKUP LOG WITH NO_LOG / TRUNCATE_ONLY
備選解決方案
- 備份日志（首選）
- 非簡單恢復(fù)模式下辟躏，先備份數(shù)據(jù)庫，然后執(zhí)行備份日志
- 如果從未備份日志土全，則 必須創(chuàng)建兩個日志備份 捎琐，以允許 數(shù)據(jù)庫引擎 將日志截斷到上次的備份點。
- 若要防止日志再次填滿裹匙，請經(jīng)常執(zhí)行日志備份
- 釋放磁盤空間以便日志可以自動增長瑞凑。
- 如果是因為磁盤已滿導(dǎo)致的，最直接的就是想辦法釋放磁盤空間概页；
- 將日志文件移到具有足夠空間的磁盤驅(qū)動器籽御。
- 增加日志文件的大小。
- 在其他磁盤上添加日志文件惰匙。
- 完成或取消長時間運行的事務(wù)技掏。
我們需要根據(jù)實際情況選擇不同的解決方案。
Simple 簡單恢復(fù)模式
在簡單恢復(fù)模式下项鬼，直接備份數(shù)據(jù)庫哑梳，將會自動截斷事務(wù)日志
Full 完整恢復(fù)模式和大容量日志模式
一般情況下，在實際生產(chǎn)環(huán)境绘盟，我們都會完整恢復(fù)模式以保證數(shù)據(jù)的安全鸠真，最大限度的保證數(shù)據(jù)不丟失悯仙。在這個模式下，當(dāng)數(shù)據(jù)文件丟失或損壞時弧哎，任然可以將數(shù)據(jù)恢復(fù)到任意時間點雁比。
Full模式的舊稱叫”Checkpoint without truncate log“，這時撤嫩，當(dāng)數(shù)據(jù)庫進行完整備份后并不會截斷事務(wù)日志偎捎，必須進行日志備份才會截斷日志，否則事務(wù)日志文件會一直增大序攘，直到到達設(shè)置最大限制或者撐爆硬盤茴她，此時如果要更新數(shù)據(jù)則會報錯（“事務(wù)日志已滿”）。
實際上程奠，在非簡單恢復(fù)模式下丈牢，都必須定期備份事務(wù)日志，使VLF變更為可用狀態(tài)瞄沙，釋放邏輯日志中的空間己沛。
備份日志
通過T-SQL進行日志備份如下：
-- 如果數(shù)據(jù)庫沒有執(zhí)行過完整備份，需要先備份數(shù)據(jù)庫
Backup Database [dbname] To disk=@dbBackupPath
BACKUP LOG [dbname] TO DISK = @tranBackupPath1
-- 如果從未備份日志距境，則 必須創(chuàng)建兩個日志備份
BACKUP LOG [dbname] TO DISK = @tranBackupPath2
同樣申尼，也可以通過數(shù)據(jù)看管理工具（）實現(xiàn)：
-
備份數(shù)據(jù)庫
backupdb1.jpg
-
備份日志文件
backup log.jpg
長期解決方案
-
數(shù)據(jù)庫選項設(shè)置 “ 完整恢復(fù)模式（Full ）”；
db_option.jpg
或者執(zhí)行下面語句：
USE master
ALTER DATABASE dbname SET RECOVERY FULL
- 每周完整備份數(shù)據(jù)庫
Backup Database [dbname] To disk=@dbBackupPath
- 每天差異備份數(shù)據(jù)庫
BACKUP DATABASE [dbname] TO DISK=@dbBackupPathVar WITH DIFFERENTIAL;
- 每30分鐘備份事務(wù)日志（間隔時間根據(jù)實際需求垫桂，時間越短數(shù)據(jù)丟失幾率越低）
BACKUP LOG [dbname] TO DISK = @tranBackupPath
-- 關(guān)于選項 TRUNCATE_ONLY（已經(jīng)在2005版廢棄师幕，2008版后不在支持），只截斷不備份诬滩。
-- 選項 With NO_TRUNCATE霹粥，不截斷日志，通常用于備份受損的數(shù)據(jù)庫
-- WITH NO_LOG 已經(jīng)廢棄
收縮日志文件
長時間沒有備份日志文件疼鸟，日志文件會變動越來越大后控，占用了大量的磁盤空間。當(dāng)我們執(zhí)行完日志截斷后空镜，文件占用的物理空間是不會變小的忆蚀，如果想減小日志文件，我們就要收縮日志文件姑裂。
若要減少物理日志文件的物理大小馋袜，則必須收縮日志文件。 知道事務(wù)日志文件包含未使用空間時舶斧，此方法很有用欣鳖。 僅當(dāng)數(shù)據(jù)庫處于聯(lián)機狀態(tài)，而且至少一個虛擬日志文件 (VLF) 可用時茴厉，才能收縮日志文件泽台。
可以執(zhí)行下面語句進行事務(wù)日志收縮：
-- 注意： 在SQL 2008中清除日志必須在簡單模式下進行什荣，等清除動作完畢在調(diào)回到完整模式（否則數(shù)據(jù)庫就不支持時間點備份）
ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT --簡單模式
USE [dbname]
GO
DBCC SHRINKFILE(N'dbnameFilename_log',11,TRUNCATEONLY)
ALTER DATABASE [56kong] SET RECOVERY FULL WITH NO_WAIT --還原為完全模式
如果你不知道你的日志文件名稱是什么，可以執(zhí)行下面語句查看：
-- 獲取數(shù)據(jù)文件列表怀酷， 列name就是文件名稱稻爬， --type=1代表是日記文件
SELECT name FROM sys.database_files WHERE type=1
你也可以通過管理工具操作實現(xiàn)：
-
數(shù)據(jù)庫設(shè)置成簡單恢復(fù)模式
simple mode1.jpg
simple mode2.jpg
-
收縮數(shù)據(jù)庫日志文件
shrink01.jpg
-
改回完整恢復(fù)模式
full01.jpg
關(guān)于日志文件大小設(shè)置
- 為避免因事務(wù)日志滿而報錯，建議開啟事務(wù)日志的自動增長蜕依。
- 文件增長（FILEGROWTH ）選項設(shè)置的事務(wù)日志的自動增長 (autogrow) 增量必須足夠大桅锄，以領(lǐng)先于工作負載事務(wù)的需求。 因此样眠，為了避免經(jīng)常向日志文件中擴充內(nèi)容友瘤，應(yīng)該采用足夠大的文件增量。 要正確設(shè)置事務(wù)日志的大小檐束，建議監(jiān)視以下時間內(nèi)所占用的日志數(shù)量：
- 執(zhí)行完整備份所需的時間辫秧，因為日志備份在其完成后才能進行。
- 最大型索引維護操作所需的時間被丧。
- 在數(shù)據(jù)庫中執(zhí)行最大批操作所需的時間盟戏。
- 使用 FILEGROWTH 選項設(shè)置數(shù)據(jù)和日志文件的 autogrow 時，建議首選使用 size 而不是使用 percentage 進行設(shè)置甥桂，以便更好地控制增長比柿究，因為 percentage 表示的是日益增長量 。
- 如果不知道設(shè)置多少才適合自己的數(shù)據(jù)庫格嘁，可以參考官方提供的分析腳本笛求，點擊查看：事務(wù)日志設(shè)置大小分析腳本
執(zhí)行腳本后的結(jié)果下圖：
logfile_Setting.jpg
我們可以根據(jù)分析結(jié)果進行相應(yīng)設(shè)置廊移「獠荆可以定期的執(zhí)行分析，看看隨著數(shù)據(jù)的增長狡孔，是否需要更改設(shè)置懂诗，以使得設(shè)置的更加合理。
注意事項
-
千萬不要直接進行日志文件收縮苗膝，除非你不需要考慮數(shù)據(jù)庫的恢復(fù)文件殃恒。否則請先備份事務(wù)日志！
- 在數(shù)據(jù)庫中有多個日志文件不會以任何方式提升性能辱揭，因為事務(wù)日志文件不會像同一文件組中的數(shù)據(jù)文件一樣使用比例填充离唐。
- 日志文件可以設(shè)為自動收縮。 但是问窃，不建議這樣做亥鬓，auto_shrink 數(shù)據(jù)庫屬性默認設(shè)為 FALSE 。 如果 auto_shrink 設(shè)置為 TRUE域庇，則僅當(dāng)其空間的 25% 以上未使用時嵌戈，自動收縮才會減少文件的大小覆积。
參考文件:
