Post reply

monitoring progress of SP executuion in real time

  • December 3, 2008 at 2:27 pm

    #124714

    Hi, thanks for reading this.

    I've got a nested SP that goes like this:

    ALTER proc lmc_sp_rs_SuperSP_Populate_Phys_Perf_v2

    @MTH_YR datetime -- formatted as '10/01/2008'

    AS

    BEGIN TRY

    BEGIN TRANSACTION;

    --Step 1:

    EXEC lmc_sp_rs_Populate_Phys_Perf_RX_DATA_v2 @MTH_YR

    --(266942 row(s) affected) for 11/01/08

    PRINT 'Step 1 is done'

    --Step 2:

    EXEC [lmc_sp_rs_Fill_PP_Membership_v2] @MTH_YR

    PRINT 'Step 2 is done'

    --(0 row(s) affected) for 11/01/08

    ....................[more steps follows]

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    the SP runs for a long time and I'd like to be able to monitor Steps' execution. I tried

    PRINT 'Step 1 is done'

    but it does not work in real-time, omly after the SP is finisehed. Is ther another way to accomplish this?

    Thanks,

    Sergei Z

  • December 3, 2008 at 2:31 pm

    #907181

    If you have the correct privileges, you can use Profiler to watch exactly what it's doing.

    Open up a trace, add Events such as SP:StmtCompleted and filter by an SPID. (If you execute the SP from QA or SSMS, your connection ID will be at the bottom in () next to your login name.)

    This will let you see exactly what your SP is doing. Be warned, this could lead to a LOT of data.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • December 3, 2008 at 3:13 pm

    #907206

    Seth,

    thanks a lot for the info and the time you spent. I'll try what you siggested.

    Sergei Z

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

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