CHECKDB errors

SQL2K.
In the interests of research, how would one go about introducing errors
into a database to get CHECKDB to report errors?
Any changes I've made just corrupt the entire database which then gets
marked as suspect.
Any help appreciated.There is no publicly-available way to do this through T-SQL (obviously we
have internal tools to allow us to test CHECKDB and repair thoroughly). I'm
not in a position to publicly describe how to corrupt a database, as you can
imagine, but if you can describe what you'd like to research I can answer
any questions you have and save you the hassle.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<KJGNews@.hotmail.com> wrote in message
news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> SQL2K.
> In the interests of research, how would one go about introducing errors
> into a database to get CHECKDB to report errors?
> Any changes I've made just corrupt the entire database which then gets
> marked as suspect.
> Any help appreciated.
>|||Paul, you seem like a great person to ask re data
corruption. I'll list some of the errors I'm seeing below
and if you could elaborate / offer any insight I'd really
appreciate it.
What I've got is a ~12 GB db, 4 tables in the millions of
records (largest is 60+, others are ~25, ~10 million).
Because of the size I have been trying to keep up
performance by shrinking the DB daily (NOTRUNCATE option),
and defragging indexes weekly.
SQL Books indicates disk errors as the cause of these
errors, and CHECKTABLE/CHECKALLOC/CHECKDB find a bunch of
errors but can't fix the one(s) causing the real problems.
Basically what happens is that some SELECT statements
began returning errors like these:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]
ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Server: Msg 823, Level 24, State 2, Line 2
I/O error (bad page ID) detected during read at offset
0x000001a7c2c000 in file 'F:\SQLData\WBReports_Data.mdf'.
The SELECTs that returned those errors were related to one
key so I tried to delete that data (which would have been
~1/2 mil. records), this stuck the DB in "suspect" status
from which we have not been able to undo. I've done other
testing by restoring from backups, but to no avail.
Some of the other errors from the system logs are:
17066 :
SQL Server Assertion: File: <recbase.cpp>, line=1378
Failed Assertion = 'm_offBeginVar < m_SizeRec'.
Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset
0x000001a7c2c000 in file 'F:\SQLData\WBReports_Data.mdf'.
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=MAXDATAROW'.
TIA for any insight you may have.
Cheers, Ken H.
>--Original Message--
>There is no publicly-available way to do this through T-
SQL (obviously we
>have internal tools to allow us to test CHECKDB and
repair thoroughly). I'm
>not in a position to publicly describe how to corrupt a
database, as you can
>imagine, but if you can describe what you'd like to
research I can answer
>any questions you have and save you the hassle.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
><KJGNews@.hotmail.com> wrote in message
>news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
>> SQL2K.
>> In the interests of research, how would one go about
introducing errors
>> into a database to get CHECKDB to report errors?
>> Any changes I've made just corrupt the entire database
which then gets
>> marked as suspect.
>> Any help appreciated.
>>
>
>.
>|||Hi Ken,
The 823 indicates you've got a bad page on the F: drive - i.e. the disk
hardware cannot physically read the page. All the other errors you describe
have the same root cause. You'll need to replace that disk and restore your
database from the last known good backup.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Hillyer" <mrstick@.beer.com> wrote in message
news:041801c3d494$f6bcc300$a101280a@.phx.gbl...
> Paul, you seem like a great person to ask re data
> corruption. I'll list some of the errors I'm seeing below
> and if you could elaborate / offer any insight I'd really
> appreciate it.
> What I've got is a ~12 GB db, 4 tables in the millions of
> records (largest is 60+, others are ~25, ~10 million).
> Because of the size I have been trying to keep up
> performance by shrinking the DB daily (NOTRUNCATE option),
> and defragging indexes weekly.
> SQL Books indicates disk errors as the cause of these
> errors, and CHECKTABLE/CHECKALLOC/CHECKDB find a bunch of
> errors but can't fix the one(s) causing the real problems.
> Basically what happens is that some SELECT statements
> began returning errors like these:
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
> ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Server: Msg 823, Level 24, State 2, Line 2
> I/O error (bad page ID) detected during read at offset
> 0x000001a7c2c000 in file 'F:\SQLData\WBReports_Data.mdf'.
> The SELECTs that returned those errors were related to one
> key so I tried to delete that data (which would have been
> ~1/2 mil. records), this stuck the DB in "suspect" status
> from which we have not been able to undo. I've done other
> testing by restoring from backups, but to no avail.
> Some of the other errors from the system logs are:
> 17066 :
> SQL Server Assertion: File: <recbase.cpp>, line=1378
> Failed Assertion = 'm_offBeginVar < m_SizeRec'.
> Error: 823, Severity: 24, State: 2
> I/O error (bad page ID) detected during read at offset
> 0x000001a7c2c000 in file 'F:\SQLData\WBReports_Data.mdf'.
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'.
> TIA for any insight you may have.
> Cheers, Ken H.
>
> >--Original Message--
> >There is no publicly-available way to do this through T-
> SQL (obviously we
> >have internal tools to allow us to test CHECKDB and
> repair thoroughly). I'm
> >not in a position to publicly describe how to corrupt a
> database, as you can
> >imagine, but if you can describe what you'd like to
> research I can answer
> >any questions you have and save you the hassle.
> >
> >Regards.
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >
> ><KJGNews@.hotmail.com> wrote in message
> >news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> >> SQL2K.
> >>
> >> In the interests of research, how would one go about
> introducing errors
> >> into a database to get CHECKDB to report errors?
> >>
> >> Any changes I've made just corrupt the entire database
> which then gets
> >> marked as suspect.
> >>
> >> Any help appreciated.
> >>
> >>
> >
> >
> >.
> >|||Paul,
Thanks for your reply,
I am looking to write something to run a consistency check against a number
of databases and to automate the checking of the output. Getting return
values
from these commands is fiddly at best!
I've been through several iterations of looking for the best way and have
settled
on DBCC CHECKDB(db) WITH TABLERESULTS (which appears to be undocumented).
Various attempts with DMO (CheckTables/CheckTablesWithResult) have ended in
tears, as
the return values seem to well, er, not return anything in QueryResults
object (tried both VB
and Perl).
On the way to doing this, I was hoping to learn something about the internal
structure of databases, but can understand your reluctance to share this
info<g>
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:utJ5ydH1DHA.1760@.TK2MSFTNGP10.phx.gbl...
> There is no publicly-available way to do this through T-SQL (obviously we
> have internal tools to allow us to test CHECKDB and repair thoroughly).
I'm
> not in a position to publicly describe how to corrupt a database, as you
can
> imagine, but if you can describe what you'd like to research I can answer
> any questions you have and save you the hassle.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> <KJGNews@.hotmail.com> wrote in message
> news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> > SQL2K.
> >
> > In the interests of research, how would one go about introducing errors
> > into a database to get CHECKDB to report errors?
> >
> > Any changes I've made just corrupt the entire database which then gets
> > marked as suspect.
> >
> > Any help appreciated.
> >
> >
>|||Much appreciated Paul, thanks!
>--Original Message--
>Hi Ken,
>The 823 indicates you've got a bad page on the F: drive -
i.e. the disk
>hardware cannot physically read the page. All the other
errors you describe
>have the same root cause. You'll need to replace that
disk and restore your
>database from the last known good backup.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Ken Hillyer" <mrstick@.beer.com> wrote in message
>news:041801c3d494$f6bcc300$a101280a@.phx.gbl...
>> Paul, you seem like a great person to ask re data
>> corruption. I'll list some of the errors I'm seeing
below
>> and if you could elaborate / offer any insight I'd
really
>> appreciate it.
>> What I've got is a ~12 GB db, 4 tables in the millions
of
>> records (largest is 60+, others are ~25, ~10 million).
>> Because of the size I have been trying to keep up
>> performance by shrinking the DB daily (NOTRUNCATE
option),
>> and defragging indexes weekly.
>> SQL Books indicates disk errors as the cause of these
>> errors, and CHECKTABLE/CHECKALLOC/CHECKDB find a bunch
of
>> errors but can't fix the one(s) causing the real
problems.
>> Basically what happens is that some SELECT statements
>> began returning errors like these:
>> [Microsoft][ODBC SQL Server Driver][DBNETLIB]
>> ConnectionCheckForData (CheckforData()).
>> Server: Msg 11, Level 16, State 1, Line 0
>> General network error. Check your network documentation.
>> Server: Msg 823, Level 24, State 2, Line 2
>> I/O error (bad page ID) detected during read at offset
>> 0x000001a7c2c000 in
file 'F:\SQLData\WBReports_Data.mdf'.
>> The SELECTs that returned those errors were related to
one
>> key so I tried to delete that data (which would have
been
>> ~1/2 mil. records), this stuck the DB in "suspect"
status
>> from which we have not been able to undo. I've done
other
>> testing by restoring from backups, but to no avail.
>> Some of the other errors from the system logs are:
>> 17066 :
>> SQL Server Assertion: File: <recbase.cpp>, line=1378
>> Failed Assertion = 'm_offBeginVar < m_SizeRec'.
>> Error: 823, Severity: 24, State: 2
>> I/O error (bad page ID) detected during read at offset
>> 0x000001a7c2c000 in
file 'F:\SQLData\WBReports_Data.mdf'.
>> 17066 :
>> SQL Server Assertion: File:
>> <p:\sql\ntdbms\storeng\drs\include\record.inl>,
line=1447
>> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=>> MAXDATAROW'.
>> TIA for any insight you may have.
>> Cheers, Ken H.
>>
>> >--Original Message--
>> >There is no publicly-available way to do this through
T-
>> SQL (obviously we
>> >have internal tools to allow us to test CHECKDB and
>> repair thoroughly). I'm
>> >not in a position to publicly describe how to corrupt a
>> database, as you can
>> >imagine, but if you can describe what you'd like to
>> research I can answer
>> >any questions you have and save you the hassle.
>> >
>> >Regards.
>> >
>> >--
>> >Paul Randal
>> >Dev Lead, Microsoft SQL Server Storage Engine
>> >
>> >This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>> >
>> ><KJGNews@.hotmail.com> wrote in message
>> >news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
>> >> SQL2K.
>> >>
>> >> In the interests of research, how would one go about
>> introducing errors
>> >> into a database to get CHECKDB to report errors?
>> >>
>> >> Any changes I've made just corrupt the entire
database
>> which then gets
>> >> marked as suspect.
>> >>
>> >> Any help appreciated.
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||Your best bets are to use the value of @.@.ERROR after the command has run or
to output to a file and then parse. I would warn against using the
tableresults output as it is undocumented and will be changing substantially
in Yukon. Many of the database internals are documented in Kalen's Inside
SQL Server 2000 book but they're beyond the scope of this forum to get into.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<KJGNews@.hotmail.com> wrote in message
news:#ZN#8RP1DHA.2000@.TK2MSFTNGP11.phx.gbl...
> Paul,
> Thanks for your reply,
> I am looking to write something to run a consistency check against a
number
> of databases and to automate the checking of the output. Getting return
> values
> from these commands is fiddly at best!
> I've been through several iterations of looking for the best way and have
> settled
> on DBCC CHECKDB(db) WITH TABLERESULTS (which appears to be undocumented).
> Various attempts with DMO (CheckTables/CheckTablesWithResult) have ended
in
> tears, as
> the return values seem to well, er, not return anything in QueryResults
> object (tried both VB
> and Perl).
> On the way to doing this, I was hoping to learn something about the
internal
> structure of databases, but can understand your reluctance to share this
> info<g>
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:utJ5ydH1DHA.1760@.TK2MSFTNGP10.phx.gbl...
> > There is no publicly-available way to do this through T-SQL (obviously
we
> > have internal tools to allow us to test CHECKDB and repair thoroughly).
> I'm
> > not in a position to publicly describe how to corrupt a database, as you
> can
> > imagine, but if you can describe what you'd like to research I can
answer
> > any questions you have and save you the hassle.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > <KJGNews@.hotmail.com> wrote in message
> > news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> > > SQL2K.
> > >
> > > In the interests of research, how would one go about introducing
errors
> > > into a database to get CHECKDB to report errors?
> > >
> > > Any changes I've made just corrupt the entire database which then gets
> > > marked as suspect.
> > >
> > > Any help appreciated.
> > >
> > >
> >
> >
>|||The fun I've had trying to ouput to a file<g>....
Running as a scheduled job outputs in unicode, which i have had so
much trouble parsing, it's unbelievable.
I'll continue looking for a tidy solution before going down the multipart
road.
Thanks very much for your time.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:O0R9z%23T1DHA.3140@.tk2msftngp13.phx.gbl...
> Your best bets are to use the value of @.@.ERROR after the command has run
or
> to output to a file and then parse. I would warn against using the
> tableresults output as it is undocumented and will be changing
substantially
> in Yukon. Many of the database internals are documented in Kalen's Inside
> SQL Server 2000 book but they're beyond the scope of this forum to get
into.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> <KJGNews@.hotmail.com> wrote in message
> news:#ZN#8RP1DHA.2000@.TK2MSFTNGP11.phx.gbl...
> > Paul,
> >
> > Thanks for your reply,
> >
> > I am looking to write something to run a consistency check against a
> number
> > of databases and to automate the checking of the output. Getting return
> > values
> > from these commands is fiddly at best!
> >
> > I've been through several iterations of looking for the best way and
have
> > settled
> > on DBCC CHECKDB(db) WITH TABLERESULTS (which appears to be
undocumented).
> > Various attempts with DMO (CheckTables/CheckTablesWithResult) have ended
> in
> > tears, as
> > the return values seem to well, er, not return anything in QueryResults
> > object (tried both VB
> > and Perl).
> >
> > On the way to doing this, I was hoping to learn something about the
> internal
> > structure of databases, but can understand your reluctance to share this
> > info<g>
> >
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:utJ5ydH1DHA.1760@.TK2MSFTNGP10.phx.gbl...
> > > There is no publicly-available way to do this through T-SQL (obviously
> we
> > > have internal tools to allow us to test CHECKDB and repair
thoroughly).
> > I'm
> > > not in a position to publicly describe how to corrupt a database, as
you
> > can
> > > imagine, but if you can describe what you'd like to research I can
> answer
> > > any questions you have and save you the hassle.
> > >
> > > Regards.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > <KJGNews@.hotmail.com> wrote in message
> > > news:uI9kioE1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> > > > SQL2K.
> > > >
> > > > In the interests of research, how would one go about introducing
> errors
> > > > into a database to get CHECKDB to report errors?
> > > >
> > > > Any changes I've made just corrupt the entire database which then
gets
> > > > marked as suspect.
> > > >
> > > > Any help appreciated.
> > > >
> > > >
> > >
> > >
> >
> >
>