Post reply

Find the last executed part of a stored procedure

  • September 15, 2015 at 2:27 am

    #303084

    Hello

    I have a stored procedure that performs a number of tasks (inserts, updates etc.)

    It's been running for a number of hours now and I'm not sure how far through it is

    sp_who2 and dbcc inputbuffer don't reveal an awful lot (other that the sp that is running)

    Is there a way to find out what the last part executed was or what it is in the middle of doing?

    I'm aware of profilers but, as the sp is running, can't really set this up now

    Also aware (for future reference), I should really break this up as that makes it easier to track

    Any thoughts?

    Thanks

    - Damian

  • September 15, 2015 at 2:35 am

    #1826636

    DamianC (9/15/2015)

    I'm aware of profilers but, as the sp is running, can't really set this up now

    SQL Profiler should be able to show you the next statement to be executed (WITHIN the SProc) ...

    .. but not the one currently executing, e.g. if that is the ONE statement that is taking forever ...

  • September 15, 2015 at 2:37 am

    #1826638

    If you do a select from sys.dm_exec_requests for the session_id in question it will give you the start and end statement offsets.

    Its the number of characters into the statement the task is up to, if you have a large proc, trying to get the character position might be tricky.

  • September 15, 2015 at 3:06 am

    #1826654

    Thanks for the response

    Profiler doesn't quite give the answer

    sys.dm_exec_requests for the session_id is useful

    If I look at the start and end offset (then divide by 2 to get characters), it's right in the middle of an update

    Is there an easy way to character count as I'm not sure I'm doing this correctly

    Thanks

    - Damian

  • September 15, 2015 at 3:17 am

    #1826657

    Look at the Books Online entry for either sys.dm_exec_requests or sys.dm_exec_query_stats (or maybe sys.dm_exec_sql_text) as one of them has an example query using the statement offsets.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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