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

Visits: 0





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

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


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


搜尋了一下之後發現,這好像是在 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空間是否足夠


About the Author


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

You may also like these