SQL SERVER 2014 Agent服務異常停止案例

   生產環境一資料庫伺服器(SQL Server 2014)的Agent服務突然停掉了，檢查了錯誤日誌,發現在"SQL Server Agent"裡面沒有"SQLServerAgent terminated (normally)"的信息，只有如下錯誤信息

根據作業運行的日誌信息，以及上面錯誤信息，可以判斷SQL SERVER Agent服務應該在2016-04-24 9:20: PM（21:20）異常停止了。在“Windows Logs”下的“System”日誌下，有如下錯誤信息，我們可以確定SQL Agent在這個時間點異常結束了，但是從這些信息還不知道發生了什麼導致SQL Agent服務異常終止。

The SQL Server Agent (MSSQLSERVER) service terminated unexpectedly.  It has done this 1 time(s).

在“Windows Logs”下的“Application”可以看到更詳細的錯誤信息

The description for Event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

If the event originated on another computer, the display information had to be saved with the event.

The following information was included with the event: 

SQLServerAgent Monitor failed to restart SQLServerAgent after SQLServerAgent terminated unexpectedly (reason: SQLSCMControl() returned error 5, 'Access is denied.').

the message resource is present but the message is not found in the string/message table

在C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log裡面發現這個時間點生成了SQLDUMPER_ERRORLOG.log和SQLDmpr0001.mdmp

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () completed with status (00000514)

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 1: 9472

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 2: 0

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 3: 0:0

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 4: 0000000000A0D8D8

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parsed parameters:

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ProcessID = 9472

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ThreadId = 0

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Flags = 0x0

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     MiniDumpFlags = 0x0

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     SqlInfoPtr = 0x0000000000A0D8D8

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     DumpDir = <NULL>

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExceptionRecordPtr = 0x0000000000000000

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ContextPtr = 0x0000000000000000

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExtraFile = <NULL>

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     PatternForExtraFiles = <NULL>

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     InstanceName = <NULL>

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ServiceName = <NULL>

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

(B40:1B9C) 04/24/16 21:20:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\LOG\SQLDmpr0001.mdmp

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Total Buffer pool data pages filtered out: 0 KB

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Total Hekaton data pages filtered out: 0 KB

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Total Free memory (from non top level allocators) filtered out: 0 KB

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Total top level free memory filtered out: 0 KB

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Total Log pool memory filtered out: 0 KB

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'dbghelp.dll' : 'C:\Program Files\Microsoft SQL Server\120\Shared\dbghelp.dll'

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'C:\Program Files\Microsoft SQL Server\120\Shared\dbghelp.dll' : '6.12:2.633'

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'C:\Program Files\Microsoft SQL Server\120\Shared\dbghelp.dll' : '6.12:2.633'

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'sqldumper.exe' : 'C:\Program Files\Microsoft SQL Server\120\Shared\SQLDUMPER.EXE'

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'C:\Program Files\Microsoft SQL Server\120\Shared\SQLDUMPER.EXE' : '2014.120:2000.8'

(B40:1B9C) 04/24/16 21:20:02, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'C:\Program Files\Microsoft SQL Server\120\Shared\SQLDUMPER.EXE' : '12.0:2000.8'

(B40:1B9C) 04/24/16 21:20:02, ACTION,              SQLAGENT.EXE, Watson Invoke: No

根據這裡紅色部分信息判斷，應該是記憶體收縮（memory shrunk）導致的。追查到此處，是什麼具體操作導致，已經無法判斷，追查。特此記錄一下這個案例。