sqlserver probe crashes after some time and results in Max. restarts reached for probe

Document ID : KB000094304
Last Modified Date : 08/08/2018
Show Technical Document Details
Issue:
The sqlserver probe periodically crashes while executing its checkpoints. The controller restarts the probe but in some
   cases the probe crashes again right away resulting in a Max. restarts reached for probe 'sqlserver' error. The log file
    in many cases is overwritten by the subsequent restart attempts and only shows

Mar 27 19:14:44:769 [3048] Controller: Max. restarts reached for probe 'sqlserver' (command = sqlserver_monitor.exe)

Checking the Windows Application Event Log reveals an application crash with the following information

Faulting application name: sqlserver_monitor.exe, version: 0.0.0.0, time stamp: 0x59f9c5ba 
Faulting module name: sqlserver_monitor.exe, version: 0.0.0.0, time stamp: 0x59f9c5ba 
Exception code: 0xc0000409    (This code shows a buffer overflow)
Fault offset: 0x0000000000152181 
Faulting process id: 0x3118 
Faulting application start time: 0x01d3bb06599e75cf 
Faulting application path: C:\Program Files\Nimsoft\probes\database\sqlserver\sqlserver_monitor.exe 
Faulting module path: C:\Program Files\Nimsoft\probes\database\sqlserver\sqlserver_monitor.exe 
Report Id: 98f3e2cd-26f9-11e8-80c5-005056bc3595 
Faulting package full name: 
Faulting package-relative application ID: 

 
Environment:
sqlserver probe 5.30
Cause:
It was found that the return from the long_jobs checkpoint query in some cases returned data larger than anticipated causing
  the probe to fail with a buffer overrun error.

The sqlserver probe runs the following query for long_jobs

--long_jobs
select sp.session_id spid, sjs.step_id, sjs.step_name, sj.name jobname, datediff(s,dms.last_request_start_time,getdate()) elapsed_time,sce.name as category_name,
  sp.host_name host_name, sp.login_name loginname, dms.last_request_start_time start_time, sql_details.text sql_text from sys.dm_exec_sessions dms,
  msdb.dbo.sysjobs sj, msdb.dbo.sysjobsteps sjs,msdb.dbo.syscategories sce,
  sys.dm_exec_sessions sp, sys.dm_exec_requests rq outer apply sys.dm_exec_sql_text(rq.sql_handle) sql_details where sp.session_id <> @@SPID and
  dms.session_id = sp.session_id and sp.session_id = rq.session_id
  and STUFF(REVERSE(SUBSTRING(REVERSE(RTRIM(sp.program_name)),11,16)),5,0,'-') = RIGHT(sj.job_id, 17)
  and sp.program_name LIKE 'SQLAgent - TSQL JobStep %' and SUBSTRING(REVERSE(RTRIM(sp.program_name)),2,1) = sjs.step_id
  and sj.job_id = sjs.job_id and sj.category_id = sce.category_id
 
Resolution:
The fix for this problem has been published in sqlserver probe 5.40.

Release Notes: sqlserver probe