Fatal errors with SQL (asserts, invalid page reads etc)

[Rene]

Suddenly in one database we have a lot of errors, it seams some things are
corrupted. I tried to start maintanance / database repair, but this fails
too.

When selecting in Query Analyzer a range of records from a table I get the
following message:

Location: p:\sql\ntdbms\s toreng\drs\incl ude\record.inl: 1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 68
Process ID: 1208

When I select the record that causes this error, the following error is
reported in Query Analyzer:

Could not find the index entry for RID '163748993200' in index page
(3:373352), index ID 0, database 'sal'.

In the log I see a lot of these messages:

Stack Signature for the dump is 0x&D179C48
Could not open FCD for invalid file ID 21761 in database'sal'
I/O error (bad page ID) detected during read at offset 0x00000b64d2000


How can this be fixed?
How can I rebuild the index for one table / check integrity of one table?
What kind of actions may caused this corruption (if it is corruption) ?
How can it be prevented?

I hope someone can help.

Regards,

Rene

[Dejan Sarka]

Re: Fatal errors with SQL (asserts, invalid page reads etc)

To check a single table, use DBCC CHECKTABLE. If there is only a
non-clustered index problem, rebuild the index using DBCC DBREINDEX. Check
for integrity of the whole database with DBCC CHECKDB. If ou have major
problems, it could be that your HDD has errors.
To prevent errors, use RAID 1, RAID 5 or RAID 10 arrays of disks for your
database.
Always have backup strategy.

--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Rene" <no@spam.nl> wrote in message
news:vg7lujcbog fg06@corp.super news.com...[color=blue]
> Suddenly in one database we have a lot of errors, it seams some things are
> corrupted. I tried to start maintanance / database repair, but this fails
> too.
>
> When selecting in Query Analyzer a range of records from a table I get the
> following message:
>
> Location: p:\sql\ntdbms\s toreng\drs\incl ude\record.inl: 1447
> Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> SPID: 68
> Process ID: 1208
>
> When I select the record that causes this error, the following error is
> reported in Query Analyzer:
>
> Could not find the index entry for RID '163748993200' in index page
> (3:373352), index ID 0, database 'sal'.
>
> In the log I see a lot of these messages:
>
> Stack Signature for the dump is 0x&D179C48
> Could not open FCD for invalid file ID 21761 in database'sal'
> I/O error (bad page ID) detected during read at offset 0x00000b64d2000
>
>
> How can this be fixed?
> How can I rebuild the index for one table / check integrity of one table?
> What kind of actions may caused this corruption (if it is corruption) ?
> How can it be prevented?
>
> I hope someone can help.
>
> Regards,
>
> Rene
>
>
>[/color]

[Rene]

Re: Fatal errors with SQL (asserts, invalid page reads etc)

Thanks for you answer. We have a raid 5 and daily backup, but this record
was insert in may but we discovered it yesterday. I don't think a backup
strategy can handle this.

It seems that it is small problem since the server was stil operational for
more then a month, it just caused some extra heartbeats since this is the
first time I've got such problem.

Thanks,

Rene


"Dejan Sarka" <dejan_please_r eply_to_newsgro ups.sarka@avten ta.si> schreef in
bericht news:OmW$sjUQDH A.560@TK2MSFTNG P10.phx.gbl...[color=blue]
> To check a single table, use DBCC CHECKTABLE. If there is only a
> non-clustered index problem, rebuild the index using DBCC DBREINDEX. Check
> for integrity of the whole database with DBCC CHECKDB. If ou have major
> problems, it could be that your HDD has errors.
> To prevent errors, use RAID 1, RAID 5 or RAID 10 arrays of disks for your
> database.
> Always have backup strategy.
>
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Rene" <no@spam.nl> wrote in message
> news:vg7lujcbog fg06@corp.super news.com...[color=green]
> > Suddenly in one database we have a lot of errors, it seams some things[/color][/color]
are[color=blue][color=green]
> > corrupted. I tried to start maintanance / database repair, but this[/color][/color]
fails[color=blue][color=green]
> > too.
> >
> > When selecting in Query Analyzer a range of records from a table I get[/color][/color]
the[color=blue][color=green]
> > following message:
> >
> > Location: p:\sql\ntdbms\s toreng\drs\incl ude\record.inl: 1447
> > Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
> > SPID: 68
> > Process ID: 1208
> >
> > When I select the record that causes this error, the following error is
> > reported in Query Analyzer:
> >
> > Could not find the index entry for RID '163748993200' in index page
> > (3:373352), index ID 0, database 'sal'.
> >
> > In the log I see a lot of these messages:
> >
> > Stack Signature for the dump is 0x&D179C48
> > Could not open FCD for invalid file ID 21761 in database'sal'
> > I/O error (bad page ID) detected during read at offset 0x00000b64d2000
> >
> >
> > How can this be fixed?
> > How can I rebuild the index for one table / check integrity of one[/color][/color]
table?[color=blue][color=green]
> > What kind of actions may caused this corruption (if it is corruption) ?
> > How can it be prevented?
> >
> > I hope someone can help.
> >
> > Regards,
> >
> > Rene
> >
> >
> >[/color]
>
>[/color]

[Erland Sommarskog]

Re: Fatal errors with SQL (asserts, invalid page reads etc)

Rene (no@spam.nl) writes:[color=blue]
> Thanks for you answer. We have a raid 5 and daily backup, but this record
> was insert in may but we discovered it yesterday. I don't think a backup
> strategy can handle this.[/color]

Nope. But a good backup strategy may make it possible to easy recover
from a problem like this.

The error you get has number 644. Search for 644 in Books Online, and
you will find an article that describes you should handle this problem.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Rene]

Re: Fatal errors with SQL (asserts, invalid page reads etc)

"Erland Sommarskog" <sommar@algonet .se> schreef in bericht
news:Xns93AE948 88CCCYazorman@1 27.0.0.1...[color=blue]
>
> Nope. But a good backup strategy may make it possible to easy recover
> from a problem like this.
>[/color]

I've read some article on the net where the corruption was undetected for a
long time, even the oldest backup was corrupted. Should be hard to recover,
not?

Rene

[Erland Sommarskog]

Re: Fatal errors with SQL (asserts, invalid page reads etc)

Rene (no@spam.nl) writes:[color=blue]
> I've read some article on the net where the corruption was undetected
> for a long time, even the oldest backup was corrupted. Should be hard to
> recover, not?[/color]

Yes, I have run into a case where a DBCC CHECKDB did not show any errors,
but when you restroed the backup, there were errors. I should add that
this was with SQL Server 6.0.

But it is definitely a good idea to every now and then restore a backup
and run a DBCC on the restored copy. As it is to every now and then to
restore a database, and then apply transaction log dumps to a certain
point in time. That is also part of a backup strategy: make certain
that if the disaster strikes that you actually know that the restore
procedures are working.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp