Post reply

Store proc and his Spid ?

  • October 20, 2005 at 3:02 am

    #89791

    Hi ......everybody......

    I have created a store proc and is scheduled to run during the night.....

    It takes long time ....sometime also more than 2 hours......(I don't know the time....it depends on the job to do).

    Sometimes an error occurs and my problem is:

    How can verify with another sql code (store proc, udf...) if it is blocked for an error and get free ......

     

    thank very much.........

  • October 20, 2005 at 5:03 am

    #598617

    I'm sorry, but it's not clear what you want here. You have to be more specific and give us some more details.

    /Kenneth

  • October 20, 2005 at 5:16 am

    #598623

    You are not being very clear but hers some general ideas. Use sp_who2 to inform you if blocking locks are taking place at run time. The part about logging and trapping errors would need to be added to the proc itself. Create a log file or log table for the procedure to log info as it hits major steps in its procesing giving you run time info. Add raiserrors to the procedure to report when errors take place during processing.

  • October 20, 2005 at 3:39 pm

    #598961

    I mean:

    using t-sql,  how can I verify if another process like a store proc (named usp_myproc) is running or not (without using enterprise manager, sql profiler and so on...)?

    thank ......

     

  • October 21, 2005 at 2:00 am

    #599026

    Well, the 'simple' answer would be that you can't. It's not the 'proper' way to audit what you want, you should use profiler for this.

    /Kenneth

  • October 21, 2005 at 6:33 am

    #599081

    I have a small script that I turn ON before I start a certain job that does INSERTS into a table.  The script does an INSERT every minute with the SELECT statement below.  When the job finishes I turn OFF the "logging".  On days where I have problems I look at the log.  You can also add a WHERE clause to look at a specific database.

    SELECT DISTINCT b.nt_userName, 

      b.hostName,

      c.name,

      GETDATE()

     FROM master.dbo.sysLockInfo a

     JOIN master.dbo.sysProcesses b ON a.req_spID = b.spID

     JOIN master.dbo.sysDatabases c ON b.dbID = c.dbID

    Steve

  • October 21, 2005 at 4:55 pm

    #599328

    Thank for your suggestion...

    I'll try the steve solution....very interesting..

Viewing 7 posts - 1 through 6 (of 6 total)

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