December 11, 2006 at 4:42 am
Hi Folks,
Don't know if anyone can help me here...
I have an integration between two systems which has suddenly and inexplicably started causing record locks.
I know which procedure is causing this, but there is a certain amount of confusion as to when the procedure actually runs (I don't believe I'm updating the "changed date" field in the receiving db on write).
The procedure is triggered from SQL server 7.0 and is writing to Oracle 8i.
I need to bottom out whether the change date I am seeing on the receiving table is being updated or not. The easiest way for me to see this (I believe) is if there is some way to see a log of the procedure call and the parameters it has used, this will tell me absolutely everything I need to know.
Is this possible on SQL 7.0? I do have transaction log backup, but I'm not entirely sure of what other audit trails might be available or relevant....
Thanks
Richard
December 11, 2006 at 7:52 am
Richard,
Have a look at Profiler. That will allow you to run a trace and hopefully track when and how this procedure is being executed.
You'll want to set up a filter for this specific procedure and just bear in mind that running profiler can place a load on the SQL Server.
December 11, 2006 at 8:09 am
Thanks for that Karl - is this the only way to do it??
Reason I'm asking is that this problem comes up max. once a month - it is by no means consistent. There are also some other issues.
I know when the lock is being created because it halts the next table write - ergo look for the last successful table write and that's when the lock occured.
But it suits me that the SP does not alter the date field on the destination server because I am trying to prove there is another process involved in the locking problem, the alteration date written onto the oracle table is actually (I think) the date that the transaction PRIOR to my SQL 7.0 transaction occurs. This is what I need to prove.
So it doesn't really help me much to monitor the SP itself, it may be weeks before it happens again. What I need are forensics, sequence of events I think is :
1) Lock problem occurs.
2) Overnight sometime my SQL procedure kicked off
But currently I can't prove it, if I had the exact time of the SP running with the parameters it was using I could. It is the difference between farming the problem out to my programming suppliers and having to deal with it myself, at the moment it is very much my problem.
I am currently flagging the Oracle table with my SP to fill in the "last changed by" field, I'm currently thinking I'll leave this out and maybe this will tell me the "last but one change".
Meantime I will set up a trace on this procedure, although as I say I may well have cracked it before it happens again.
Thanks
Richard
December 11, 2006 at 8:46 am
IF Profiler won't work, and you have some good reasons why it could be a pain, then you need to buy one of the log reader tools.
Red Gate Log Rescue
Lumigent Log Explorer
ApexSQL Log
Golden Gate - Log PI Reader (renamed somewhere)
One of these can read your log and let you peer into the transactions that have occurred.
The other thing to do (if change management allows) is add a logging statement in the stored procdure to write the parameters and other information into some table you create.
PS. SQL 7 is getting close to losing support. I'd at least recommend you also look at a 2000 or 2005 upgrade.
December 11, 2006 at 8:51 am
Thanks Steve, actually I've never actually called Microsoft for anything anyway, so it is kind of irrelevant, but I'm bouncing my finance server up to 2005 next year and this app'll get the hand me down 2000 licence.
I'll just insert another write into the proc....
Thanks
Richard
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply