monitoring progress of SP executuion in real time

• Sergei Zarembo

  Hall of Fame

  Points: 3887

  More actions

  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

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  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]

• Sergei Zarembo

  Hall of Fame

  Points: 3887

  More actions

  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