Resolved: Could not complete cursor operation because the table schema changed after the cursor was declared.

I have written about how a reindex job can interrupt cursor operations:

Could not complete cursor operation because the table schema changed after the cursor was declared.

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I got the chance to have another go at this issue. In previous posts, the cursor was declared as is without specifying any attributes.

After going through all the attributes, I have decided to test a few and see if they would make any difference. I will spare you the trouble and list the findings below:

FAST_FORWARD

Could not complete cursor operation because the table schema changed after the cursor was declared.

FORWARD_ONLY

Could not complete cursor operation because the table schema changed after the cursor was declared.

READONLY

Could not complete cursor operation because the table schema changed after the cursor was declared.

STATIC

It worked!

It was quite obvious why it worked in hindsight, according to the trusted Books Online:

STATIC

Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications.

There you go, problem solved.

Regardless of WAIT_AT_LOW_PRIORITY, index operations can still break cursor operations

Today I had the pleasure to read about the new index rebuild option WAIT_AT_LOW_PRIORITY. It’s a great addition to the DBA arsenal and you can read all about it here: Testing ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY in SQL Server 2014

I have written about how index operations can interrupt cursor operations. Now naturally I wondered whether the new index option makes any difference to this scenario. Let’s work through an example.

1. Create table and index

create table temp
(
    id int not null identity(1, 1) primary key
    , data varchar(10) not null default('test')
)
go

insert temp default values
go 50

create index idx_temp on temp (data)
go

2. Start a cursor loop

declare cur cursor
for
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
begin
    print @id
    waitfor delay '00:00:01'
    
    fetch cur into @id
end

close cur
deallocate cur

3. Rebuild index with the new option

alter index idx_temp 
on temp 
rebuild with 
(
    online = on 
    (
        wait_at_low_priority
        (
             max_duration = 1 minutes
             , abort_after_wait = self
        )
    )
)

We still get the error:

1
2
3
4
5
6
Msg 16943, Level 16, State 4, Line 18
Could not complete cursor operation because the table schema changed 
after the cursor was declared.

I guess the lesson for today is that no matter how seemingly harmless/efficient the index operation is, you still need to test it thoroughly before applying to prod.

Could not complete cursor operation because the table schema changed after the cursor was declared.

Msg 16943, Level 16, State 4, Line 16
Could not complete cursor operation because the table schema changed after the cursor was declared.

Adding or removing non clustered indexes seems like a low risk performance tuning operation, right? Well, under normal circumstances it is true as you are not modifying the underlying data nor changing the table schema. I found out the hard way today that it could cause rather nasty errors like the one above if it is performed while there is a cursor loop running in another session.

 

I will show you in a quick demo. Let’s start by creating a table and inserting some data.

 

 

create table temp
(
    id int not null identity(1, 1) primary key
    , data varchar(10) not null default('test')
)
go

insert temp default values
go 50

We will then kick off a simple cursor looping through the table.

declare cur cursor
for
    select id from temp

open cur

declare @id int

fetch cur into @id

while @@fetch_status = 0
begin
    print @id
    waitfor delay '00:00:01'
    
    fetch cur into @id
end

close cur
deallocate cur

 

 

While the session is running, we will pretend that we have no knowledge of the running cursor code and we’ve identified a performance tuning opportunity to add a non clustered index to the table. We will do just that but in a separate session window.

 

create index idx_temp on temp (data)

 

 

Once the index is created, we then switch back to the session window with the cursor and should expect to see the error in the Messages tab.

 

1
2
3
4
5
Msg 16943, Level 16, State 4, Line 16
Could not complete cursor operation because the table schema changed after the cursor was declared.

So there you have it, I’ve just demonstrated that no matter how seemingly harmless an operation is, there is always a potential risk for unintended side effects.