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 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.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:xn0e2pqso958n30000@.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@.shadhawk.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:xn0e2pqso958n30000@.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.
>|||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 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
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 sort
things out.
Thanks,
Andy.|||A Mackie wrote:
> 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
> > 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
> 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 sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.