SQL Server為什麼日誌文件越來越大？

12-27

近期碰到一個問題，很是迷惑。
SQL server 2008 R2下，業務系統運行一周後，就發現日誌文件ldf很大，而mdf文件不大。ldf文件9G，mdf只有2G大小。恢復模式=簡單。請問這是什麼原因？
我的做法：
1、雖然使用分離資料庫，刪除ldf文件再附加回來，好像數據也沒有變化。這麼做雖然不好，但是還是節省了不少空間。
2、使用checkpoint有效果嗎？

1.檢查日誌使用率
DBCC SQLPERF(LOGSPACE)
2.如果使用率比較高，去看看是什麼在影響資料庫日誌文件重用
select log_reuse_wait_desc from sys.databases

數據其實是保存在log里的，你看到的那個db文件其實只是個快照，用來提高速度的。你把db刪掉了你還能從log裡面還原出完整的db，你把log刪掉了，以後硬碟斷電，db整個毀。

有了log你還可以吧db回滾到過去的任何一秒鐘，就跟蘋果的time machine具有一樣的功能

當然了，也並不是所有人都需要回滾到任何一秒鐘的，因此你可以使用一些命令，讓log把當前的db整個寫進去，然後刪掉過去所有的log，以後你就只能回滾到這裡了。

綜上所述，log肯定是要越來越大的，為了回滾。

我和樓上對關係資料庫的理解不太一樣，下面是我的回答

----------------------------背景知識分割線---------------------------

在關係資料庫系統中，我們需要資料庫可靠，所謂的可靠就是當遇見如下兩種情況之一時保證資料庫的一致性:

在系統崩潰/故障等情況下，保證資料庫的一致性
數據不能在多個DML語句同時修改數據的情況下，導致不一致或數據損壞

實際上，上述第二種情況就是並發性所需要解決的問題，傳統關係資料庫中，我們用鎖來解決這個問題，而對於內存資料庫或帶有樂觀並發控制的資料庫系統，通過多版本並發控制（MVCC）來解決這個問題。因為本篇文章的主旨是討論日誌而不是並發，因此對於上述第二種情況不會詳細解釋。

我們上面還多次提到了一致性（Consistence），在開始了解日誌如何維持一致性之前，我們首先要明白什麼是一致性。一致性在資料庫系統中所指的內容比較廣，一致性不僅僅需要資料庫中的數據滿足各種約束，比如說唯一約束，主鍵約束等，還需要滿足資料庫設計者心中的隱式約束，簡單的業務約束比如說性別這列只允許男或女，這類隱式約束通常使用觸發器或約束來實現，或是在資料庫所服務的應用程序中進行約束。

下面我們把一致性的範圍縮減到事務一致性，事務一致性的概念學術上的解釋為：

如果事務執行期間沒有出現系統錯誤或其他事務錯誤，並且資料庫在事務開始期間是數據一致的，那麼在該事務結束時，我們認為資料庫仍然保證了一致性。

因此，引申出來事務必須滿足原子性，也就是事務不允許部分執行。事務的部分執行等同於將資料庫置於不一致的境地之下。此外多事務並發執行也可能導致資料庫不一致，除非資料庫系統對並發進行控制。

關於上面的顯式約束，由資料庫系統來實現，比如說違反了一致性約束的語句會導致資料庫系統報錯並拒絕執行。但一些隱式的事務約束，比如說寫語句的開發人員對系統設計者所設計的規則並不了解，導致了違反業務規則的數據修改，這種情況在資料庫端很難探查。但是這種問題通常可以規則到許可權控制的領域，我們認為授予某個用戶修改特定數據的許可權，就認為這個用戶應該了解資料庫中隱式和顯式的規則。

除去這些業務上的數據不一致之外，我們需要在系統崩潰等情況下保證數據的一致性，而可能導致這類數據不一致的情況包括但不限於下面這些情況：

存儲系統損壞，比如說磁碟上位元組級別的損壞，這類問題通常可以通過磁碟上的奇偶校驗發現，另外還有一些大一些的問題，比如說整個存儲系統崩潰。這類問題的修復手段取決於前期工作，比如說備份策略，高可用性架構，SAN Replication等技術。
機房整體損壞，這類問題比較極端，只有異地機房容災可以解決。
系統故障，修改數據的進程都需要事務作為上下文，和其他概念一樣，事務也是有狀態的。而事務狀態通常存儲在易丟失的主存中，因此，當出現系統故障、進程崩潰等系統失敗時，可能導致事務狀態的丟失，此時，我們就無法得知事務中的哪部分已經執行而哪部分還未執行，重新運行事務並不會解決這類問題，因為有可能導致事務中某部分的重複執行。因此解決這類問題的方式就是將事務的狀態以及對資料庫修改的詳細步驟與內存中的數據分開存放，並存儲於磁碟等穩定的介質中，當系統故障等情況下，我們可以通過這些記錄來將系統恢復到一致性的狀態之下，我們對這類存儲，稱之為日誌。

---------------------------------回答正文-------------------------------

SQL Server中日誌主要用於下面三個部分：

事務回滾（Rollback）
事務前滾（Roll Forward）
幫助數據冗餘（利用事務日誌進行備份，搭建冗餘系統，在SQL Server中指的是鏡像、事務日誌傳送、複製、AlwaysOn等技術，或者其他第三方利用SQL Server日誌同步數據的技術）

SQL Server還提供了兩種恢復模式：簡單模式和完整模式

簡單模式指的是日誌被不在被當前未結束事務需要後，就可以回收重複利用。

完整模式指的是日誌被不在被當前未結束事務需要後，就可以留著等待將這部分日誌歸檔（日誌備份）

而樓主使用的是簡單恢復模式，不存在忘記備份日誌導致日誌越來越大的問題。因此可能造成日誌越來越大的原因主要是：

1.當前存在活動日誌，這個可以使用DBCC OPENTRAN命令查看是否有當前活動日誌導致日誌無法截斷。

2.當前庫用於複製，因為日誌需要被複制的Log Reader進程讀取，在讀取之前日誌是無法被清理掉的，原因可以通過sys.databases的log_resue_wait列查看到原因。

3.日誌之前曾經因為大的操作暴漲，忘記收縮，嘗試直接收縮一下日誌。

---------------------------------------------------------------

CheckPoint通常沒用，因為SQL Server會定期CheckPoint，而樓主貌似問題存在一段時間了。

另外，刪了日誌再附加會有風險，假如資料庫當前沒有活動事務時，你刪了文件，這就是所謂的clean shutdown，不會有問題，但加入當前有事務產生的數據需要被rollback或roll forward,那麼日誌文件的缺失會導致SQL Server無法做資料庫的recovery,因此資料庫會質疑，如果資料庫比較重要，那麼存在風險。

從大的層面說，是SQL server的設計上的一大缺陷， MS 的SQL 是從sybase的SQL 合作時代後延續的產物，在sybase時代就種下了這個根，只不過後面這麼多年，微軟都不敢改進這個重大設計隱患。看看oracle就明顯了，online redo 與history redo（archive log）分離就很好的解決了這種問題。現在的SQL server早已比sybase的正宗SQL server強大了太多，微軟應該下決心考慮把這個重大設計局限缺陷給修整好！！!

怎麼刪除log ,可以詳細的熟悉嗎？！

這個問題可能廣大DBA同學在從業經歷中都會遇到多次，解決它是有一個固定的方法學的，步驟如下：

執行下面語句看看具體為什麼log truncation沒有發生：
SELECT database_id, name, log_reuse_wait_desc FROM sys.databases;
根據1的結果，也就是為什麼log不能被重用，來採用相應的措施。比如1告訴你log不能reuse的原因是log backup，那你就去做一個log backup，也許你也要看看是不是自己的log backup agent job執行的頻率不夠高。如果是有active transaction，那麼你要檢查檢查是哪個session在執行什麼，為什麼執行這麼久？需要等著它執行結束還是需要kill這個session？如果1告訴你是因為replication，那麼你要檢查檢查是不是replication有什麼問題，還能正常replicate transaction么？同時也需要檢查是不是CDC有問題（CDC也用replication agents去掃描log file的！）。等等等等，反正就是要針對1裡面的原因去解決。

最後歡迎大家關注我的公眾號，abitaboutmylife，這裡只討論資料庫以及相關內容。

存在未提交事務，用dbcc opentran 看一下未提交事務的spid是多少，然後 kill spid，kill後可能會需要回滾一段時間，完成後執行checkpoint ，再看看日誌文件能不能收縮

如果還是不行再聯繫我

@vczh SQL SERVER 把DB 刪掉了，如何從LOG裡面還原出來？