autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"

Has anyone saw this before ?

If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens ever
y
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.

Autoshrink was turned on as a way to keep physical transaction log size unde
r
control [yes, I know, not a good idea]

Thanks,
Andy Mackie.

That is not the way to keep the log file under control.  Either it needs
that much space or it doesn't.  If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions.  If it does need more space than you have you are only hurting
the situation by shrinking it.

--
Andrew J. Kelly  SQL MVP


"A Mackie" <andrew@mackie14.freeserve.co.uk> wrote in message
 news:xn0e2pqso958n30
000@news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
>
> Has anyone saw this before ?
>
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
>
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
>
> Thanks,
> Andy Mackie.

And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.

Thanks

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:#qXFTUhYFHA.3096@TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control.  Either it needs
> that much space or it doesn't.  If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions.  If it does need more space than you have you are only
 hurting
> the situation by shrinking it.
>
> --
> Andrew J. Kelly  SQL MVP
>
>
> "A Mackie" <andrew@mackie14.freeserve.co.uk> wrote in message
>  news:xn0e2pqso958n30
000@news.microsoft.com... 
 cause
 
>
>

Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure bu
t
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
>
> Thanks

OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenanc
e
plans and re-indexing, that is the real problem (all 2nd hand info).

I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups ar
e
scheduled, to keep the log file under control. Hopefully that will sort
things out.

Thanks,
Andy.

A Mackie wrote:

> Paul S Randal [MS] wrote:
> 
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
>
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sor
t
> things out.
>
> Thanks,
> Andy.

Looks like there was a maintenance plan that included a re-index scheduled t
o
run at the same time as some cursor-processing.

Andy.