[MSSQL_2014] 交易紀錄已滿導致DB crash?!!!

點閱: 227

緣起

最近在處理公司Server_A的DB資料,內容是把2017年以前的Table_A搬到Server_B中,並把Server_A中2017年以前的資料刪除,其實就是個非常簡單的SQL查詢,但是…當資料非常大的時候,會遭遇的問題完全不是之前都在處理小資料集的我能夠預見的。加上這個DB內的資料是公司主要的營收來源,當噴錯噴到整個DB掛掉而且復原要花16小時!!!!的時候實在是很想找個洞跳下去…..

為了不讓同樣的錯誤再次發生,特此紀錄


問題描述

這是我執行 delete 語法時,出現的錯誤,資料筆數大約2X億列資料

[MSSQL_2014] 交易紀錄已滿導致DB crash?!!! 1

幾個關鍵字:交易紀錄已滿、修復資料庫、重新啟動資料庫

非常不幸的,出現這個錯誤之後,DB就進入了還原(rollback)的狀態,完全不能操作,公司的production就這樣掛了!!!!!!!!!(看看下方截圖,還原DB要16hr…心都涼了)
[MSSQL_2014] 交易紀錄已滿導致DB crash?!!! 2


搜尋

搜尋了一下之後發現,這好像是在 SQL Server 2014 會出現的BUG,詳細文章在此,節錄片段如下:

However, just last week I was contacted by someone running SQL Server 2012 SP3 who’d seen similar symptoms but for a user database this time, and the user database went into recovery. An example of the error log messages is below (altered for anonymity):

During under of a logged operation in database 'mydb', an error occurred at log record ID (2445:89001:23). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
The log for database 'mydb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Error during rollback, shutting down database (location: 1)
Database mydb was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
The transaction log for database 'mydb' is full due to 'ACTIVE_TRANSACTION'.
'D:\Logs\mydb.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

I suggested that they’d hit the known bug, and they confirmed that with Microsoft. And if you hit it for tempdb, the server will shut down, as tempdb being unavailable means SQL Server has no choice but to stop. The bug is described in KB article 2963384 and is included in SQL Server 2012 SP4 and SQL Server 2014 SP1. If you’re running 2012 SP3 then you should install SP4, and if you’re running 2014 RTM then you should install the latest 2014 SP.


解決方式

  1. 在SQL Server 2014安裝更新檔

    尚未證實是否可行

  2. 刪除大量資料前,確認ldf空間是否足夠

    延伸閱讀:如何確認ldf空間以及刪除

About the Author

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Related Posts