After setting up backups on a client’s servers, I started to get the error message below each night at approximately the same time. Sometimes on the same database and other times on other database. The only constant appeared to be the time in which the error occurred.
Processed 1 pages for database ‘MyDatabase’, file ‘MyDatabase_log’ on file 1. [SQLSTATE 01000] Msg 16943, Sev 16, State 4, Line 116 : Could not complete cursor operation because the table schema changed after the cursor was declared. [SQLSTATE 42000] Msg 3014, Sev 16, State 1, Line 82 : BACKUP DATABASE successfully processed 329 pages in 1.141 seconds (2.362 MB/sec). [SQLSTATE 01000]
The error message is complaining that the schema for a table that is being used by a CURSOR has changed. At 1 AM, this seemed to be a very bizarre event to be occurring.
It turns out that the error message actually had nothing to do with the backup commands, instead it was due to the CURSOR that was being used in the backup script to loop through the databases. The backup script uses a progress table to monitor the backups as they are occurring and this table is used to populate the CURSOR.
Around the time the backup script failed, the index maintenance script had also started executing. It just so happened that the index maintenance script was re-indexing the table that the CURSOR was based on. A little re-coding around the use of the CURSOR resolved this issue. And plans to remove the CURSOR entirely are forthcoming.
If you see error messages similar to this in your environment, try looking for CURSOR that are having their underlying tables defragmented. This could very well be your issue.
Strate SQL 