October 22, 2019 at 12:39 pm
I have a stored procedure (actually it's a developer's code) - my dev is complaining that while he is trying to debug the code that it is taking several minutes to cancel the transaction.
looking at the code it makes a call halfway through the proc to a stored procedure on a linked server
I logged into the remote server and made a call to the proc - 2 minutes 35 seconds... not great, but this is for an overnight job
On the main server, if we comment out this remote proc call, everything runs through in seconds - but if we put it back and start the main proc then immediately cancel/kill it (at this stage we're putting print statements in to debug values and behaviour, so we want to see the results of print) then the proc takes 3 minutes to rollback
but our print statements are showing that it did not get to the linked server call.
for now we have a parameter on the proc called @debug which avoids making the remote call, but is there anything I can do to reduce the rollback time?
by the way, everything in the main proc is in a transaction, so i'm guessing MSDTC is getting involved here
MVDBA
October 22, 2019 at 3:38 pm
is the remote procedure being called with a 4 part name directly or by using OPENQUERY? I try to avoid cross instance items where I can but I do try to stick to using OPENQUERY for remote objects.
October 22, 2019 at 3:51 pm
It's 4 part naming - i'll give openquery a go - I've been playing with all sorts of techniques, but I've found that even if I Kill the spid half a second after starting the proc, it still takes 2 minutes to roll back/kill
I've got a workaround for now
I've set up a SQL agent job on the remote server, I just insert a record in a table and the remote server uses the sql agent job to call the stored proc on the remote server based on whether it is in the table or not (it doesn't return any records) (poor mans service broker) -
our debugging works quickly now, but i'd like to figure out why the rollback is so slow
MVDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply