November 7, 2017 at 12:10 am
Hello, we have a procedure that runs for long periods via a SQL Agent job.
We have an issue when the nightly update statistics maintenance jobs runs the procedure crashes if a modification has been done to the proc, even hours before.
The error is: "The definition of object 'xxx' has changed since it was compiled"
If we do a modification the job will be stopped, the change made and the procedure successfully re-started.
This could happen hours before the nightly maintenance job updating statistics.
I have tried "with recompile" when the procedure is modified but it does not make any difference.
It only crashes on days when the procedure is modified and it is the Statistics job causes the issue. (it failed at a certain time, I changed the maintenance job time and the failure time also changed).
The command is the standard "sp_updatestats"
I understand that if a procedure is altered while it is executing it can fail with this error but have no idea why it is failing in this scenario.
Does anybody know why this is and how this can be prevented because we do not want the important process failing.
thanks
November 7, 2017 at 2:17 am
If I recall correctly, this usually happened when the procedure itself created changes within objects in the database. There was also an old bug in 2005. I think we'd need to see more about the code of the procedure itself to answer this fully.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 7, 2017 at 12:30 pm
Thanks for the reply. The procedure has too much code to post so a few notes about what it does.
The procedure is the main part of an automation process that runs any SSIS packages that are due to run based on time, dependencies complete etc etc.
The procedure is in a continuous loop, retrieving any processes, executing them from the catalog, pausing for a bit, trying again.
The procedure does no heavy lifting i.e querying against large datatables.
The pseudo flow is:
While Loop
Are their any processes to run --> NO- exit loop
YES
While Loop
Create Catalog Execution
Start Catalog ExecutionEnd -- loop until all processes are complete
Wait Delay for a few minutes
End
The processes are execution async from the Catalog.
The loop although continuous does pause for a number of minutes between each cycle.
Hope this helps.
November 7, 2017 at 12:59 pm
Just some quick checks:
1.) The object specified in the error message is actually the stored procedure you are using?
2.) Do any of the "processes to run" make changes to this stored procedure?
And a quick question: Why is this happening in a stored procedure? Why not an SSIS package? Seems like it would be impossible to have the same problem by putting the functionality in SSIS instead of a stored procedure. Also, why is this proc changing so often? Shouldn't this kind of thing NOT need changes on a regular basis? Ideally, only the list of tasks to run changes, and is always automatically detected.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply