SQL Server管理人員最怕看到錯誤823, 824, 1788x, 3624.......等重大錯誤,
一旦發生了總是要花相當多的心力來處理, 甚至有資料丟失的可能性.
不過今天踩了個雷, 發生了不是3624的錯誤3624......
PS. 本文的問題已在SQL Server 2016修正.
相信在經常性管理SQL Server的人員, 對於SQL Server幾個重大的錯誤代號是不會陌生的, 例如823, 824, 605, 1788x, 3624等, 這些都表示著, 要嘛SQL Server Instance有問題, 要嘛資料庫的資源 (Memory、Physical Disk) 發生異常, 或是資料庫儲存物件發生錯誤. 多數的情況下, 這些錯誤是必須透過DBCC CHECKDB、SQL DUMP來進一步分析問題, 並且多數的情況是得透過restore backup來搶救資料的.
今天踩到一個很大的雷, 情境是這樣的……
正準備迎接下班的同時, 訊息框跳出一個來自應用程式的錯誤截圖, 主要是說明應用程式在執行某支Procedure時, 會不固定的發生SQL exception ERROR 3624. 看到這個錯誤, 心中當下毛毛的, 這…….不會是資料庫異常吧!
當大家在Google這個錯誤時, 不過因為過去遇過數次這個錯誤, 索性直接找上了DBA, 請他們進行DBCC CHECKDB的檢查 – 對的, 多數文章都是這麼說, 先檢查DBCC CHECKDB. 然而, DBCC CHECKDB是沒有異常的…… 很奇妙的說.
之後再一個一個資料表做DBCC CHECKTABLE, 竟然也是都正常的...... 好在錯誤3624是會產生dump的, 相信這個錯誤是與DB本質無關 (雖然心中還是毛毛的). 回頭去看dump, 發生點是在procedure執行階段, 就檢查了程式, 左看右看都不像有問題的.
直到…… 一個一個參數去帶入後, 找到原因是下列組合引發的錯誤
* 程序中有使用option (recompile)
* 同時使用offset … fetch next … rows
* 並且offset帶入的值是負值.
直接來看reproduce這個問題吧
use DEMO;
go
--建立測試資料表
create table tbl_test
(c1 int, c2 varchar(10));
go
--寫入測試資料
insert into tbl_test values (1, 'a'),(2,'b'),(3,'c');
go
--一個常見的案例, 分頁顯示
--建立procedure
create procedure page_list
@page int,
@size int
as
begin
select * from tbl_test
order by c1 asc
offset (@page - 1) * @size rows fetch next @size rows only;
end
go
--測試正常回傳
exec page_list 1,2;
/*
c1 c2
1 a
2 b
*/
exec page_list 2,2;
/*
c1 c2
3 c
*/
--測試負值在OFFSET上時
--錯誤是10724
exec page_list 0,2;
/*
訊息 10742，層級 15，狀態 1，程序 page_list，行 7 [批次開始行 34]
The offset specified in a OFFSET clause may not be negative.
*/
至此的錯誤是一個可預期的, 在OFFSET指定負值會產生10724的錯誤. 不過加上option (recompile) 後就不一樣了.
--對procedure加入option (recompile)
alter procedure page_list
@page int,
@size int
as
begin
select * from tbl_test
order by c1 asc
offset (@page - 1) * @size rows fetch next @size rows only
option (recompile);
end
go
--再次帶入負值
--此時會停頓一下 => 打dump
--然後報錯
exec page_list 0,2;
/*
Location: op_ppqte.cpp:12267
Expression: llSkip >= 0
SPID: 51
Process ID: 652
訊息 3624，層級 20，狀態 1，程序 page_list，行 6 [批次開始行 52]
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
訊息 596，層級 21，狀態 1，行 52
Cannot continue the execution because the session is in the kill state.
訊息 0，層級 20，狀態 0，行 52
在目前的命令上發生嚴重錯誤。如果有任何結果，都必須捨棄。
*/
百思不得其解, 為什麼OFFSET配上option (recompile) 後會引發corruption, 而且還打dump出來.
(部份dump)
Memory
MemoryLoad = 30%
Total Physical = 2047 MB
Available Physical = 1413 MB
Total Page File = 2431 MB
Available Page File = 1714 MB
Total Virtual = 134217727 MB
Available Virtual = 134212073 MB
**Dump thread - spid = 0, EC = 0x00000000F7F3AC60
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDump0001.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 11/23/17 00:04:28 spid 51
*
* Location: op_ppqte.cpp:12267
* Expression: llSkip >= 0
* SPID: 51
* Process ID: 652
*
* Input Buffer 62 bytes -
* exec page_list -4,2;
然後從各方資訊, 就是要做DBCC CHECKDB的檢查, 可是測試資料庫才建好, 裡頭也就這麼一張表……. 怎麼檢查都沒有錯啊…….
--從ERRORLOG可以看到dump部份內容
--與錯誤發生的建議處理方式
sp_readerrorlog
/*
Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: <op_ppqte.cpp>, line=12267 Failed Assertion = 'llSkip >= 0'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Error: 3624, Severity: 20, State: 1.
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a Hotfix from Technical Support.
*/
本想發個BUG或是Design issue, 這個行為不像ERROR 3624, 最終取得BUG FIX的文件, 也確認相同的程序在SQL Server 2016已經被修復.