記錄檔不停增長的原因以及解決的相關方法

--探討記錄檔不停增長的原因以及解決的相關方法交易記錄檔是SQL Server的另一個(還有一個為資料檔案)重要組成部分。每個資料庫都有交易記錄檔，用來記錄所有事務以及每個事務對資料庫做的修改。為了提高SQL Server的整體效能，檢索資料時將資料頁讀入緩衝區快取。資料的修改不是直接在磁碟上進行，而是修改快取中的頁副本。直到資料庫中出現檢查點，或者必須將修改寫入磁碟才能使用緩衝區來容納新頁時，才會將這些修改寫入磁碟。將修改後的資料頁從高速緩衝儲存空間寫入磁碟的操作叫做重新整理頁。在快取中修改，但尚未寫入磁碟的頁稱為“髒頁”。對緩衝區中的頁進行修改時會在日誌快取中產生一條日誌記錄。SQL Server具有防止在寫入關聯的日誌記錄前重新整理髒頁的邏輯。會確保日誌記錄在提交事務時，或者在此之前一定已經被寫入磁碟。換句話說SQL Server對資料的insert,update,delete都只是在記憶體中完成後，就提交事務。這些修改並不立刻同步到硬碟的資料頁上。而SQL Server又必須保證事務的一致性，哪怕是出現異常終止，記憶體中的修改沒來得及寫入硬碟，下次重啟的時候，能夠恢複到一個事物一致的時間點。已經提交的修改要在硬碟中的頁面重新完成。為了做到這一點，必須依賴交易記錄然而在SQL Server的使用過程中，會由於種種原因造成記錄檔大小不停的增長的現象。當記錄檔達到最大限制(建立資料庫是指定的記錄檔上限)，或者是把硬碟空間佔滿後，資料庫將無法進行任何插入，修改，刪除的操作。那麼接下來我們來探討出現這種問題的一些原因以及解決的相關方法-->>TravyLee(物是人非事事休，欲語淚先流！)產生測試資料:--建立測試資料庫MyDb:IF OBJECT_ID('MyDb') IS NOT NULLDROP DATABASE MyDbGOCREATE DATABASE MyDbGO--記錄檔到底有什麼東西這裡我使用MyDb資料庫，建立一個表TestLog,只包含一個int類型的欄位，然後將記錄檔清空。接著執行DBCC LOG命令，找到記錄檔中的最後一條記錄use MyDbgocreate table TestLog(a int)gocheckpoint go--backup log MyDb with truncate_only(此條命令在2008版本已經不再支援)--2008的需要先備份資料庫BACKUP DATABASE [MyDb] TO  DISK = N'E:\MyDb_bak\MyDb_bak_20121113' GO--然後再被分交易記錄(備份交易記錄後會自動清空之前的日誌)BACKUP LOG [MyDb] TO  DISK = N'E:\MyDb_bak\MyDb_log' GOgosp_helpdb--可以看到MyDb資料庫的dbiddbcc log(7,1)go/*Current LSN             Operation                       Context                         Transaction ID LogBlockGeneration   Tag Bits Log Record Fixed Length Log Record Length Previous LSN            Flag Bits Log Reserve Description----------------------- ------------------------------- ------------------------------- -------------- -------------------- -------- ----------------------- ----------------- ----------------------- --------- ----------- ------------------------00000015:000000a0:0003  LOP_MODIFY_ROW                  LCX_BOOT_PAGE_CKPT              0000:00000000  0                    0x0000   62                      100               00000000:00000000:0000  0x0000    0           (9 行受影響)DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/--以上結果為執行dbcc log(7,1)返回的最後一條資料,一共返回了9條記錄接下來我對MyDb資料庫的TestLog表進行insert操作insert TestLog select 1godbcc log(7,3)go/*00000015:000000a5:0013LOP_INSERT_ROWSLCX_HEAP0000:0000021000x000062104*/--我們可以在結果中找到和insert相關的記錄（一共30條記錄）下面再插入一條記錄:insert TestLog select 100godbcc log(7,3)go/*00000015:000000a9:0001LOP_BEGIN_XACTLCX_NULL0000:0000021300x00006400000015:000000a9:0002LOP_INSERT_ROWSLCX_HEAP0000:0000021300x00006200000015:000000a9:0003LOP_COMMIT_XACTLCX_NULL0000:0000021300x000048*/--新的和insert相關的記錄 從這些記錄中我們可以找到剛剛的insert操作的事務，開始時間，結束時間，剛才串連的SPID等下面執行一下更新操作:update TestLogset a=2godbcc log(7,3)go/*00000015:000000aa:0001LOP_BEGIN_XACTLCX_NULL0000:0000021400x0000648800000015:000000aa:0002LOP_MODIFY_ROWLCX_HEAP0000:0000021400x00006210000000015:000000aa:0003LOP_MODIFY_ROWLCX_HEAP0000:0000021400x00006210000000015:000000aa:0004LOP_COMMIT_XACTLCX_NULL0000:0000021400x00004852*/--這次出現了和update相關的四條記錄對交易記錄進行分析可以發現:1,日誌記錄的是資料的變化，而不是使用者發過來的操作類型2,每條記錄都有唯一的LSN編號，並且記錄它屬於的事務編號3,日誌記錄的行數和實際發生變化的資料量有關係4,日誌記錄事務發生的時間  但不保證記錄發起這個事務的用  戶名，更不記錄發起者的程式名稱5,SQL Server能夠從記錄檔裡讀到資料修改前的值和修改後  的值。但是對於管理者來講，直接從日誌裡面是很難瞭解其  修改過程的。記錄檔增長的原因SQL Server會為所有的修改記錄日誌。SQL Server也設計了相應的機制，會定期記錄檔中不再需要的日誌。如果記錄檔裡的需要的記錄越來越多了，那麼就會出現記錄檔不停地增長的現象。通常的原因有以下幾個:1,資料庫復原模式不是簡單模式,但是沒有安排記錄備份對於非簡單模式的資料庫,只有做完記錄備份後記錄才會被截斷做完整備份和差異備份都不會起這個作用2,資料庫上有一個長時間沒有被提交的事務3,資料庫上有一個很大的事務正在運行4,資料庫的複製或者鏡像出了異常要避免記錄檔不停的增長,那麼就要想辦法避免上面這些情況的發生那麼如何處避免呢?對於一個最近不會去記錄備份的資料庫,設定成為簡單恢複即可如果資料庫設計成了完整復原模式,那就一定要定期安排定期做記錄備份.如果複製和鏡像任務出了問題,需要及時解決.如果一時找不到解決辦法,建議暫時拆除複製或鏡像,以防止日誌記錄越積越多.在程式設計的時候也需要避免事務時間過長,不宜用一個事物做太多的操作.如果資料庫晚上或者周末會做一些維護工作例如曆史資料清洗,資料匯入匯出,索引重建等等.這是需要為他們預留出足夠的空間,並且在做完之後及時備份.最後來說說如何定位造成日誌增長的原因--STEP ONE 檢查日誌現在使用方式和資料庫狀態:DBCC SQLPERF(LOGSPACE)GO/*MyDb0.804687576.577670*/SELECTNAME,RECOVERY_MODEL_DESC,LOG_REUSE_WAIT,LOG_REUSE_WAIT_DESCFROMsys.databasesGO/*MyDbFULL2LOG_BACKUP*/以上語句可以找出Log Space Used(%)很高的，這是我們需要定位什麼原因造成日誌記錄不能被清除掉如果資料庫的日誌從用等待狀態(LOG_REUSE_WAIT_DESC)對應的值是LOG_BACKUP，那就說明SQL Server在等待著記錄備份，這時需要檢查備份計劃，如果存在不合適的計劃，需要及時更改備份策略。如果使用者不期待做記錄備份，那就可以直接把復原模式改成簡單。STEP TWO 檢查最老的活動事務:USE MyDbGO/*這時開啟另一個視窗  輸入以下語句:USE MyDbGObegin transelect name,number into test from master..spt_values*/我們可以很清楚的看到這個事務並沒有被提交然後執行以下命令找到活動的事務相關的資訊(我們不確定這個事務是否是那個未提交的事務)DBCC OPENTRANGO/*資料庫 'MyDb' 的事務資訊。最早的活動事務:    SPID (伺服器處理序 ID): 53    UID (使用者識別碼): -1    名稱          : user_transaction    LSN           : (21:424:30)    開始時間    : 11 13 2012  3:55:18:230PM    SID           : 0x01DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/=--執行下面的語句:selectst.text,t2.*fromsys.dm_exec_sessions as t2,sys.dm_exec_connections as t1cross applysys.dm_exec_sql_text(t1.most_recent_sql_handle) as stwheret1.session_id=t2.session_idand t1.session_id>50/*begin tran  select name,number into test from master..spt_values532012-11-13 15:55:15.550SX-1Microsoft SQL Server Management Studio - 查詢3486.Net SqlClient Data Provider0x01sasleeping0x152181822012-11-13 15:55:18.2302012-11-13 15:55:18.2470711612147483647簡體中文ymd7111011112-10250600x01saNULLNULLNULL2*/這時我們可以從結果中找到該SPID (伺服器處理序 ID): 53下的未提交的活動的事務,而且通過返回的語句可以很清楚的知道是該進程中的哪些語句執行的這時我們只需殺死這個進程即可:KILL 53DBCC OPENTRANGO/*沒有處於開啟狀態的活動事務。DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。*/