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.
> > > >
> > > >
> > >
> > >
> >
> >
>

Check Sysobjects Error during Snapshot

During the snapshot generation, I get this eror while the snapshot agent is
generating the schema script.
Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
snapshot/transactional replications.
On my sole publisher on its own server, I have 4 publications, two per
published databases (A and B), each publication is slightly different (row
filtering).
One distributor on a different server, One subscriber (1) on this same server.
Another subscriber(2) on a third server.
I can replicate database A publication A1 to to Subscriber1 and publication
A2 to subscriber 2 without a problem.
Next, I can replicate database b publication B2 to Subscriber2 without a
problem.
When I try running the snapshot for database b publication B1 to Subscriber1
it start running along, and then gets the checksysobjects error. There are no
other snapshots running concurrently and all other agents are idle.
Any ideas here?
can you post the entire error message here?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> During the snapshot generation, I get this eror while the snapshot agent
is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same
server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and
publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to
Subscriber1
> it start running along, and then gets the checksysobjects error. There are
no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>
|||Here are the Snapshot Agent Error Details (I substituted the actual
servername with "<My Server Name>":
'. Check sysobjects.
(Source: <My Server Name>(Data source); Error number: 2501)
"Hilary Cotter" wrote:

> can you post the entire error message here?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
> news:8F9965DC-0049-4C21-BB34-B211F1FE531D@.microsoft.com...
> is
> server.
> publication
> Subscriber1
> no
>
>
|||does this post help?
http://groups.google.com/groups?hl=e...GP10. phx.gbl
It seems that when you apply the snapshot one of the objects might already
exist on the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Need more Zzzz" <NeedmoreZzzz@.discussions.microsoft.com> wrote in message
news:E7E74D1F-C27D-4E4E-B767-0F57A7689D98@.microsoft.com...[vbcol=seagreen]
> Here are the Snapshot Agent Error Details (I substituted the actual
> servername with "<My Server Name>":
> '. Check sysobjects.
> (Source: <My Server Name>(Data source); Error number: 2501)
>
> "Hilary Cotter" wrote:
message[vbcol=seagreen]
agent[vbcol=seagreen]
(row[vbcol=seagreen]
a[vbcol=seagreen]
are[vbcol=seagreen]
|||I used the MS Online Assisted Report to help me resolve the problem very
quickly (their initial response within 24hrs pointed me in the right
direction).
Basically, each article's filter name and view name must be DATABASE-UNIQUE
in addition to publication-unique. The names are used to create stored
procedures and views in the publication database. If you create only one
publication there is no problem, but if you create two publications, then
there is name overlap and then you'll probably see the problem during
snapshot generation of the 2nd publication.
So when calling sp_articlefilter and sp_articleview, make sure that
@.filter_name and @.view_name are database-unique!
I hope this saves somebody else from the headache I went through.
- Cynthia
"Need more Zzzz" wrote:

> During the snapshot generation, I get this eror while the snapshot agent is
> generating the schema script.
> Here's my setup (all win2k sql2k servers 3 diffrent machines) all pull
> snapshot/transactional replications.
> On my sole publisher on its own server, I have 4 publications, two per
> published databases (A and B), each publication is slightly different (row
> filtering).
> One distributor on a different server, One subscriber (1) on this same server.
> Another subscriber(2) on a third server.
> I can replicate database A publication A1 to to Subscriber1 and publication
> A2 to subscriber 2 without a problem.
> Next, I can replicate database b publication B2 to Subscriber2 without a
> problem.
> When I try running the snapshot for database b publication B1 to Subscriber1
> it start running along, and then gets the checksysobjects error. There are no
> other snapshots running concurrently and all other agents are idle.
> Any ideas here?
>