Alloocation Errors in Database

I have a customer, who had a problem with their RAID which among others was
hosting their database. They managed to get the data recovered but the
database is facing a lot of problems. In their application they get the
following error :
"Could not continue scan with NOLOCK due to data movement". I get the same
error trying query some of the tables and I get a lot of errors about
allocation errors. I've tried CheckDB and CheckTable. Both reporting that
the errors are so severe that they are unrepairable. So my conclusion is
that the only solution is to restore the latest backup or is there any other
options.. The reason I'm asking is that my customer, of course, doesn't have
a backup!
It come to my mind that maybe a rebuld of the indexes could do the trick ?
Is it worth a try ?
Any suggestions ?
It's a SQL 2005 SP2.
Regards:)
Bobby HenningsenBobby
Remove NOLOCK hint from the queries
http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>I have a customer, who had a problem with their RAID which among others was
> hosting their database. They managed to get the data recovered but the
> database is facing a lot of problems. In their application they get the
> following error :
> "Could not continue scan with NOLOCK due to data movement". I get the same
> error trying query some of the tables and I get a lot of errors about
> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
> the errors are so severe that they are unrepairable. So my conclusion is
> that the only solution is to restore the latest backup or is there any
> other
> options.. The reason I'm asking is that my customer, of course, doesn't
> have
> a backup!
> It come to my mind that maybe a rebuld of the indexes could do the trick ?
> Is it worth a try ?
> Any suggestions ?
> It's a SQL 2005 SP2.
> Regards:)
> Bobby Henningsen
>|||Hi Uri,
thanks for your answer. I'm very aware of this solution.. My problem is that
it's not the case here.. When using a simple "Select * from table" I get the
error. I'm not using NOLOCK and I'm not using "Read Uncommitted". This has
to be about allocation. And as you can se it's not even possible to run
CheckDB.. So far I've found a couple of tables with this problem.. And
CheckTable won't work, either.
All I wanna know is what my options are :) 'cause if only option is to
restore from a backup I know that my customer is pretty f.... :)
Bobby :)
Bobby Henningsen
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
> Bobby
> Remove NOLOCK hint from the queries
> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>
> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others
>>was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the
>> same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any
>> other
>> options.. The reason I'm asking is that my customer, of course, doesn't
>> have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick
>> ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>|||Bobby
It is really sad that he does not backup of the database as in that case it
would be better solution.
Since you have identified 'problematic' tables try to delete all indexes on
that table a re-create again See if it helped you
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:F1505CF4-E8EC-4F51-871C-2701374326C1@.microsoft.com...
> Hi Uri,
> thanks for your answer. I'm very aware of this solution.. My problem is
> that it's not the case here.. When using a simple "Select * from table" I
> get the error. I'm not using NOLOCK and I'm not using "Read Uncommitted".
> This has to be about allocation. And as you can se it's not even possible
> to run CheckDB.. So far I've found a couple of tables with this problem..
> And CheckTable won't work, either.
> All I wanna know is what my options are :) 'cause if only option is to
> restore from a backup I know that my customer is pretty f.... :)
> Bobby :)
> Bobby Henningsen
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Bobby
>> Remove NOLOCK hint from the queries
>> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>>
>> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
>> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others
>>was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the
>> same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting
>> that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any
>> other
>> options.. The reason I'm asking is that my customer, of course, doesn't
>> have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick
>> ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>>
>|||Hi Bobby!
I noticed this in the MCT group, but I felt I didn't have much to suggest. However, just a few
thoughts:
Be prepared for "restore from backup" alternative. Yes, I hear what you are saying, no backups. But
this is at the psychological level, to set the expectation level right for your customer.
The database is most probably toast. So I'd export as much as possible into a new database. Say you
have for instance a SELECT INTO to the other database. Now, SQL Server will stop when it encounters
the physical corruption. So, for the corrupt tables, you have to work in steps. Like forcing an
index forwards and then backwards, and stopping before the corruption with a WHERE clause. Depending
on where the corruption exists, this might not even be doable (perhaps access to the data pages
isn't allowed at all because of the corruption). Looking at it from the bright side, you will tick
income, as you sit and do this... ;-). This work can be anything from mundane to a complete
nightmare or even not doable (depending of type of corruption, complexity and size of database).
I guess you can Google on tools to salvage a corrupt database. I know of one such tool
(http://www.officerecovery.com/mssql/). I've never used any such tools myself...
Open a case with MS support. Whether or not this will get you further than working on your own, I
don't know. But in most cases, the $'s for a support case is dwarfed by the value of the data.
Bobby, can you drop me an email? I have one private matter I like to talk to you about. See MCT
newsgroup if you can't figure out my email address... ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bobby Henningsen" <bobhen@.mail.dk> wrote in message
news:F1505CF4-E8EC-4F51-871C-2701374326C1@.microsoft.com...
> Hi Uri,
> thanks for your answer. I'm very aware of this solution.. My problem is that it's not the case
> here.. When using a simple "Select * from table" I get the error. I'm not using NOLOCK and I'm not
> using "Read Uncommitted". This has to be about allocation. And as you can se it's not even
> possible to run CheckDB.. So far I've found a couple of tables with this problem.. And CheckTable
> won't work, either.
> All I wanna know is what my options are :) 'cause if only option is to restore from a backup I
> know that my customer is pretty f.... :)
> Bobby :)
> Bobby Henningsen
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:uSN0feV5HHA.3940@.TK2MSFTNGP05.phx.gbl...
>> Bobby
>> Remove NOLOCK hint from the queries
>> http://blogs.msdn.com/craigfr/archive/2007/06/12/query-failure-with-read-uncommitted.aspx
>>
>> "Bobby Henningsen" <bobhen@.mail.dk> wrote in message
>> news:D0FAE134-963A-4624-BD83-F00A9F85118E@.microsoft.com...
>>I have a customer, who had a problem with their RAID which among others was
>> hosting their database. They managed to get the data recovered but the
>> database is facing a lot of problems. In their application they get the
>> following error :
>> "Could not continue scan with NOLOCK due to data movement". I get the same
>> error trying query some of the tables and I get a lot of errors about
>> allocation errors. I've tried CheckDB and CheckTable. Both reporting that
>> the errors are so severe that they are unrepairable. So my conclusion is
>> that the only solution is to restore the latest backup or is there any other
>> options.. The reason I'm asking is that my customer, of course, doesn't have
>> a backup!
>> It come to my mind that maybe a rebuld of the indexes could do the trick ? Is it worth a try ?
>> Any suggestions ?
>> It's a SQL 2005 SP2.
>> Regards:)
>> Bobby Henningsen
>>
>|||On Thu, 23 Aug 2007 11:33:05 +0300, "Uri Dimant" <urid@.iscar.co.il>
wrote:
>Since you have identified 'problematic' tables try to delete all indexes on
>that table a re-create again See if it helped you
I would try that on a COPY of the database, but I sure would not want
to try it on the original. I would want to preserve the original
untouched - or at least with no more changes - and only experiment
with copies.
Roy Harvey
Beacon Falls, CT