Post reply

Script - Long running Queries/Sessions

  • May 23, 2016 at 9:31 am

    #400191

    Anyone has a script to alert for Long running queries which can schedule as a sql server agent job.

    SQL server 2012/2014.

    Many thanks!

  • May 23, 2016 at 10:14 am

    #1880827

    sp_whoisactive can send it's results to a table.

    a simple example:

    CREATE TABLE [master].[dbo].[MonitoredActivity] (

    [dd hh:mm:ss.mss] VARCHAR(8000) NULL,

    [session_id] SMALLINT NOT NULL,

    [sql_text] XML NULL,

    [sql_command] XML NULL,

    [login_name] NVARCHAR(128) NOT NULL,

    [wait_info] NVARCHAR(4000) NULL,

    [CPU] VARCHAR(30) NULL,

    [tempdb_allocations] VARCHAR(30) NULL,

    [tempdb_current] VARCHAR(30) NULL,

    [blocking_session_id] SMALLINT NULL,

    [reads] VARCHAR(30) NULL,

    [writes] VARCHAR(30) NULL,

    [physical_reads] VARCHAR(30) NULL,

    [query_plan] XML NULL,

    [used_memory] VARCHAR(30) NULL,

    [status] VARCHAR(30) NOT NULL,

    [open_tran_count] VARCHAR(30) NULL,

    [percent_complete] VARCHAR(30) NULL,

    [host_name] NVARCHAR(128) NULL,

    [database_name] NVARCHAR(128) NULL,

    [program_name] NVARCHAR(128) NULL,

    [start_time] DATETIME NOT NULL,

    [login_time] DATETIME NULL,

    [request_id] INT NULL,

    [collection_time] DATETIME NOT NULL)

    EXEC sp_whoisactive @destination_table = 'master.dbo.MonitoredActivity'

    WAITFOR DELAY '000:00:02'

    EXEC sp_whoisactive @destination_table = 'master.dbo.MonitoredActivity'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply