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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply