Store proc and his Spid ?
-
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.........
-
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
-
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.
-
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 ......
-
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
-
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
-
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