Run the "What's Running Now" SQL Script
search cancel

Run the "What's Running Now" SQL Script

book

Article ID: 289156

calendar_today

Updated On:

Products

Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

Steps to determine what SQL statements are currently running within the DAS database

Environment

  • App Control Server: All Supported Versions
  • Microsoft SQL Server: All Supported Versions

Resolution

  1. Log in to the application server as the Carbon Black Service Account
  2. Launch SQL Server Management Studio.
  3. Connect & expand the SQL Server > Databases > right click das > New Query.
  4. Copy the following script into the "New Query" window:
-- -----------------------------------------------------
-- SERVER - What SQL statements are currently running V2
-- -----------------------------------------------------
	-- -----------------------------------------------------
	-- GOAL: 
	--			This identifies what is running currently on the sql server; you can see if there are blocks, or queries that have been running a long time.
	-- -----------------------------------------------------
	IF 1 = (SELECT COUNT(1) FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
	BEGIN
		print '******* SERVER - What SQL statements are currently running V2 *******'
	  -- Do not lock anything, and do not get held up by any locks.
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    -- What SQL Statements Are Currently Running?
   SELECT DISTINCT 
		'KILL ' +LTRIM(STR(session_Id)) [KILL]
		,'DBCC INPUTBUFFER(' +LTRIM(STR(session_Id)) + ')'[Buf]
      --  ,[Spid] = session_Id
      --, ecid
      , er.Start_time
	  , GETDATE() 'Now'
      , [DATABASE] = DB_NAME(sp.dbid)
      , Hostname
      , DATEDIFF(minute,start_time,GETDATE()) 'duration'
      , [USER] = nt_username
      , [Status] = er.status
      , [Wait] = wait_type
      , [Individual Query] = SUBSTRING(qt.text, er.statement_start_offset / 2,
                                       (CASE WHEN er.statement_end_offset = -1
                                             THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                  * 2
                                             ELSE er.statement_end_offset
                                        END - er.statement_start_offset) / 2)
      , [Parent Query] = qt.text
      , Program = program_name
      , nt_domain
      
    FROM
        sys.dm_exec_requests er
        INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
    WHERE
        session_Id > 50              -- Ignore system spids.    
        AND session_Id NOT IN (@@SPID)     -- Ignore this current statement.
    ORDER BY
      er.Start_time asc,2,1
	end
	IF 0 = (SELECT COUNT(1) FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
	BEGIN
		print '******* NO ACCESS - SERVER - What SQL statements are currently running V2 *******'
	end
  1. Click the 'Execute' button on the toolbar
  2. Copy the results to a .txt document or screenshot them for analysis.