Apparently we're getting some Long Running SQL alarms that aren't valid.


The last time I researched this, the recommended solution was to update.  Which we did.  Then the problem trailed off.  Recently they started up again.

Basically, the alarms state that the SQL has been running for X amount of time, but it's incorrect.  The SPID will only have been running for a much shorter duration.  We end up wasting time on researching things that aren't actually problems.

Anyone else have to deal with this?  Anyone have a solution?


  • Hi Bill,

    By default, the Long Running SQL alarm threshold is at 60 seconds and has a normal level severity. Adding additional thresholds will raise the alarm which I assume you've configured. Spotlight obtains the alarm value from sys.dm_exec_requests.total_elapsed_time (ms) column.

    When the alarm is triggered you can run the attached script to obtain the data Spotlight uses for this alarm. To help diagnose the alarm, you can enable Request Elapsed Time and Request Start Time columns of the Sessions dashboard. The Sessions dashboard data should reflect what the script returns when diagnosing the alarm in real time.

     I haven't see any issues regarding this alarm in the latest version of Spotlight, 13.3.

     Hope that helps out. I suggest to contact our Support team for more help if needed.


    Spotlight Support

      -- SoSSE
    -- File - Procedure\sqlserver_spotlight\2005\QS_LongRunningSQL.sql
    -- *
    -- * Copyright 2020 Quest Software Inc. ALL RIGHTS RESERVED.
    -- *
    use master
    declare @isServiceBroker char;
    declare @queryText nvarchar(4000);
    declare @sessionID int;
    declare	@sql nvarchar(100);
    set @isServiceBroker = 'N';
    if isnull(object_id(N'tempdb..#Quest_longRunningSQL'),0) <> 0 
        drop table #Quest_longRunningSQL
    if isnull(object_id(N'tempdb..#Quest_sql'),0) <> 0 
    drop table #Quest_sql
    create table #Quest_sql
    	EventType text,
        Parameters int,
        sqltext text
          convert(nvarchar, r.session_id) + '.' + convert(nvarchar, r.request_id) as session_request_id,
          left(case when r.statement_start_offset > 0 then
                  when r.statement_end_offset <= 0 then 
                      substring(qt.text, (r.statement_start_offset/2) + 1, 2147483647) 
                      substring(qt.text, (r.statement_start_offset/2) + 1, (abs(r.statement_end_offset - r.statement_start_offset))/2 + 1)   
                  when r.statement_end_offset <= 0 then 
                      left(qt.text, (r.statement_end_offset/2) +1)  
          end, 10240) as query_text,
      db_name(r.database_id) as databasename,
      rtrim(s.loginame) as loginname,
      ses.program_name as programname,
      ses.host_name as hostname,
      r.status as status
    into  #Quest_longRunningSQL 
          sys.dm_exec_requests r
    	  join master.dbo.sysprocesses s on r.session_id = s.spid
    	  left outer join sys.dm_exec_sessions ses on r.session_id = ses.session_id
          outer apply sys.dm_exec_sql_text(r.sql_handle) as qt
        not exists (select top (1) 1
    				from sys.dm_exec_sessions s where s.session_id = r.session_id and host_process_id is null)
         and r.session_id <> @@spid
         and ((lower(qt.text) not like N'%openrowset(trcdata%'
         and lower(qt.text) not like N'%openrowset(table trcdata%') or  qt.text is  null)
         and (@isServiceBroker = (case when upper(r.wait_type) in (N'BROKER_CONNECTION_RECEIVE_TASK'  , 
    					 N'BROKER_DISPATCHER'  ,                                                                     
    					 N'BROKER_ENDPOINT_STATE_MUTEX'  ,                                                           
    					 N'BROKER_FORWARDER'  ,                                                                      
    					 N'BROKER_INIT'  ,                                                                           
    					 N'BROKER_MASTERSTART'  ,                                                                    
    					 N'BROKER_REGISTERALLENDPOINTS'  ,                                                           
    					 N'BROKER_SERVICE'  ,                                                                        
    					 N'BROKER_SHUTDOWN'  ,                                                                       
    					 N'BROKER_TASK_SUBMIT'  ,                                                                    
    					 N'BROKER_TRANSMISSION_OBJECT'  ,                                                            
    					 N'BROKER_TRANSMISSION_TABLE'  ,                                                             
    					 N'BROKER_TRANSMISSION_WORK') then 'Y' else 'N' end) or (lower(qt.text) not like N'%waitfor%')) 
    declare QueryText_Cursor cursor for  
    select session_id,query_text from #Quest_longRunningSQL 
    open QueryText_Cursor;  
    fetch next from QueryText_Cursor into @sessionID, @queryText;  
    while @@FETCH_STATUS = 0  
    	if @queryText is null
    		set @sql = N'dbcc inputbuffer ('+convert(nvarchar(20),@sessionID)+')'
    		insert into #Quest_sql (EventType, Parameters, sqltext)
    		exec (@sql)									
    		select @queryText=sqltext from #Quest_sql							
    		truncate table #Quest_sql
    		if @queryText is not null
    			update #Quest_longRunningSQL set query_text = @queryText where session_id = @sessionID
    	fetch next from QueryText_Cursor into @sessionID , @queryText;  
    close QueryText_Cursor;  
    deallocate QueryText_Cursor;  
    select * from #Quest_longRunningSQL order by session_id
    drop table #Quest_longRunningSQL
    drop table #Quest_sql

  • Thank you Gita!

    For most instances we have the alarm triggering (or not triggering) at the appropriate defined margins.

    Our issue comes when Spotlight regards a SPID as running for hours when it's only been running for minutes.  I'll see if support can dig something up.

Reply Children