Post reply

Way to know Currently Running Job Step Details

  • October 15, 2009 at 4:56 am

    #210964

    Hi,

    One of my Job is running and I want to know what exactly SQL / TSQL Statement is running.

    We can find out the currently running Step but I want to know the exact SQL /TSQL Statement, is it possible if so please let me know how to find out the same.

    Thanks in advance.

    Thanks,

    Raj

  • October 15, 2009 at 5:01 am

    #1066168

    Please google first (1st result from "sql server what statement is executing")

    http://sqlserver-qa.net/blogs/t-sql/archive/2008/02/12/3428.aspx


    Clear Sky SQL
    My Blog[/url]

  • October 15, 2009 at 5:10 am

    #1066171

    Thanks Dave, I googled and got the answer. thanks for that i was really struggling with that 😀

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • October 15, 2009 at 5:10 am

    #1066172

    Thanks, I know, but my SQL Handle is null...

  • October 15, 2009 at 5:11 am

    #1066173

    you can also run the below query to get the details

    select * from msdb..sysjobactivity

    Abhijit - http://abhijitmore.wordpress.com

  • October 15, 2009 at 5:18 am

    #1066175

    SELECT * FROM msdb.dbo.sysjobactivity

    WHERE job_id IN (

    SELECT job_id FROM msdb..sysjobs WHERE name LIKE '%%')

    It will say me the step details, i want to know what exact SQL Syntax its running.

    Thanks,

    Raj

  • October 15, 2009 at 5:34 am

    #1066181

    Try this query may it full fill ur desire. It show u the average CPU time for the top 5 query execution plans in cache. Or you can change it as per ur requirement. DMV and DMF are very help full for this kind of informations. This query uses the "sys.dm_exec_query_stats" DMV and the "sys.dm_exec_sql_text" DMF

    SELECT TOP 5 total_worker_time/execution_count 'Avg CPU Time',

    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,

    ((CASE qs.statement_end_offset

    WHEN -1 THEN DATALENGTH(st.text)

    ELSE qs.statement_end_offset

    END - qs.statement_start_offset)/2) + 1)statement_text

    FROM sys.dm_exec_query_stats qs

    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

    ORDER BY total_worker_time/execution_count DESC;

    Regards,

    M.I.

    ________________________________________
    M.I.
    [font="Times New Roman"]

    Learning is a path with no destination...
    [/font]

  • October 15, 2009 at 5:45 am

    #1066185

    M.I.

    Thanks a lot 🙂

    I got it.

    Thanks,

    Raj

  • October 15, 2009 at 7:03 am

    #1066228

    rajdba (10/15/2009)

    M.I.

    Thanks a lot 🙂

    I got it.

    Thanks,

    Raj

    welcome

    ________________________________________
    M.I.
    [font="Times New Roman"]

    Learning is a path with no destination...
    [/font]

Viewing 9 posts - 1 through 8 (of 8 total)

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