-211 sysprocplan

We run a 24x7 operation, managing the state's prison system. To avoid users getting -211 (sysprocplan) errors, I created a cron job that runs 'update statistics high', immediately followed by 'update statistics for procedure' every night. But, users still get occasional -211 (sysprocplan) errors. These occasional errors usually occur while the "update statistics" jobs are actually running. The 'update statistics high' takes about an hour, and the 'update statistics for procedure' takes about 7 minutes. Since running "update statistics" is the fix, and the errors happen during the fix, what might I do to? I'd like to eliminate -211 errors. Does anyone have any suggestions? We have about 1000 tables and 1000 stored procedures in our database. -- David Grove - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "I think not," said Descartes, and disappeared.

"David E. Grove" <david_grove@correct.state.ak.us> wrote > We run a 24x7 operation, managing the state's prison system. > > To avoid users getting -211 (sysprocplan) errors, I created a cron job that > runs 'update statistics high', immediately followed by 'update statistics > for procedure' every night. But, users still get occasional -211 > (sysprocplan) errors. These occasional errors usually occur while the > "update statistics" jobs are actually running. The 'update statistics high' > takes about an hour, and the 'update statistics for procedure' takes about 7 > minutes. Since running "update statistics" is the fix, and the errors > happen during the fix, what might I do to? I'd like to eliminate -211 > errors. Does anyone have any suggestions? > > We have about 1000 tables and 1000 stored procedures in our database. this problem will come only when the stored procedure drops an object (like a table or index or a temp table) or a recently dropped object, outside the SP is accessed by the SP. Otherwise why there is a need to recompile the procedure. Also try to avoid calling the SP within a transaction, as the locks are held during the entire duration of the transaction.