Procedure not completing

Question

Procedure not completing

dhjackal

Valued Member

Points: 69
More actions
June 29, 2010 at 8:36 am

#157041

Hello everyone,
I'm hitting upon a strange problem. I have a procedure that runs and calls a subsequent procedure which rebuilds / reorganises indexes in all my user databases. When i run the proc in SSMS it happily completes however when i run the proc via a job in SQL Server Agent it randomly stops (without issue or error).
Anybody any ideas why this might occur?
Thanks in advance
dhjackal

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try tracing it with a server side trace.

Select at least statement completed, stetment starting and user error as events and set up a filter on application name=SSIS.

This should help you at least identifying the issue, then you can go on and fix it.

-- Gianluca Sartori

dhjackal Valued Member Points: 69 More actions · Answer 2

Thanks Gianluca,

I guess that's not a bad place to start. I've been trying to reproduce the issue on another test server but so far without luck. I'll put a server side trace in place to see what I can find.

Thanks again

thhamlin SSC Veteran Points: 244 More actions · Answer 3

It's possible that the job is locking up with another process. As an interactive job (when you manually run it), it would take priority. But as a scheduled job, it would not have priority and could be the victim if there is a deadlock.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Deadlock priority can be set at session level with SET DEADLOCK_PRIORITY, or can be determined automatically by the sql engine calculating the cost of the rollback for each process involved. No consideration is made on the interactive/batch nature of the session. Moreover, that information isn't available at all to the SQL Server: how could it distinguish between batches and interactive applications?

That said, it could definitely be a deadlock issue, but not for the reasons you posted.

-- Gianluca Sartori

thhamlin SSC Veteran Points: 244 More actions · Answer 5

I know there may not be any set rules for SQL Server choosing which process to kill in a deadlock situation, but I have seen this behavior from SQL Server consistently.

We were having similar issues with optimization processes, but when I ran them interactively, I never had the issue.

Just sharing my experiences when I had the same problem and what worked for me . .

dhjackal Valued Member Points: 69 More actions · Answer 6

Thanks for the replies.

It's certainly possible that the process is being chosen as a deadlock victim but i would have thought that this would be reflected in the history for the job and the status would be failed. I'm going to try and rerun the proc in batches and under different conditions and see if i can isolate what's happening. I'll also look into deadlocking priority.

Thanks again