Data corruption on a replicated database

We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server
We have recently setup a database for replication using transactional replication every ten minutes
Shortly after implementing this, we started receiving integrity errors on the database on a daily basis
Many of the errors are requiring checktable with repair data loss to correct the problem. The corruption has shown up on both replicated and non-replicated tables, but only occurs if replication is running. As soon as replication was stopped, the database quit reporting integrity errors
Please let me know if you need further information
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing to something else causing the problem
1. All of the hardware is brand new, and the server is running at about a quarter of our target performance. (This server is not yet hosting databases that will be added later this year... During peak time we are seeing a max utilization of 15% across all processors). The hardware vendor has already been over this server and hasn't found anything, and there is no hardware related errors in the server event log
2. This is the first enterprise class server we have run in a SAN environment. To eliminate the SAN hardware or network we moved all of the data to internal drives on the server. The problem continues to occur
3. This server is hosting multiple application databases. The only database with errors is the replicated database. If this was hardware related we would expect to see the other databases having problems, as they were using the same internal hardware and SAN array to hold their data as the replicated database
Any thoughts|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables
Below is excerpt from the maintenance log on the errors we are getting
[1] Database ServiceCenter: Check Data and Index Linkage..
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driver][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 owned by data record identified by RID = (1:202550:3)
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen in the scan
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The text, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 is not referenced
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or invalid key for the keys
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (application = 'cc.find.problem' and label = 'build.s) points to the data row identified by (RID = (1:348126:8))
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3 consistency errors in table 'problemm1' (object ID 525244926)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'applicationm1' (object ID 1589580701)
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4 consistency errors in database 'ServiceCenter'
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).

Data corruption on a replicated database

We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional replic
ation every ten minutes.
Shortly after implementing this, we started receiving integrity errors on th
e database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and non-replic
ated tables, but only occurs if replication is running. As soon as replicat
ion was stopped, the databa
se quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?I don't think replication has anything to do with it. Probably replication
is stressing your hard disk subsystem, leading to errors. So the real
culprit could be hardware. Check your event viewer and any hardware logs for
clues.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:B6B0118A-C15D-41A5-9E11-FA01F4AD14BD@.microsoft.com...
We are running SQL 2000 EI SP3 on a Windows 2000 Adv. Server.
We have recently setup a database for replication using transactional
replication every ten minutes.
Shortly after implementing this, we started receiving integrity errors on
the database on a daily basis.
Many of the errors are requiring checktable with repair data loss to correct
the problem. The corruption has shown up on both replicated and
non-replicated tables, but only occurs if replication is running. As soon
as replication was stopped, the database quit reporting integrity errors.
Please let me know if you need further information.
Has anyone else ran into this?|||Our first thought was hardware as well, however, a few things are pointing t
o something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a q
uarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max u
tilization of 15% across
all processors). The hardware vendor has already been over this server and
hasn't found anything, and there is no hardware related errors in the server
event logs
2. This is the first enterprise class server we have run in a SAN environme
nt. To eliminate the SAN hardware or network we moved all of the data to in
ternal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only databas
e with errors is the replicated database. If this was hardware related we w
ould expect to see the other databases having problems, as they were using t
he same internal hardware
and SAN array to hold their data as the replicated database.
Any thoughts?|||Okay, could you post the exact errors you are seeing in the SQL Server error
logs?
Are you replicating text or image columns?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:34BB1D32-6935-40A8-B061-1372260A868B@.microsoft.com...
Our first thought was hardware as well, however, a few things are pointing
to something else causing the problem:
1. All of the hardware is brand new, and the server is running at about a
quarter of our target performance. (This server is not yet hosting databases
that will be added later this year... During peak time we are seeing a max
utilization of 15% across all processors). The hardware vendor has already
been over this server and hasn't found anything, and there is no hardware
related errors in the server event logs
2. This is the first enterprise class server we have run in a SAN
environment. To eliminate the SAN hardware or network we moved all of the
data to internal drives on the server. The problem continues to occur.
3. This server is hosting multiple application databases. The only
database with errors is the replicated database. If this was hardware
related we would expect to see the other databases having problems, as they
were using the same internal hardware and SAN array to hold their data as
the replicated database.
Any thoughts?|||Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL Server Driv
er][SQL Server]Object ID 525244926: Errors found in text ID 462078345216 own
ed by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:40468), slot 12, text ID 462078345216 is
referenced by page (1:202550), slot 3, but was not seen in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 525244926. The te
xt, ntext, or image node at page (1:695828), slot 12, text ID 462078345216 i
s not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database 'ServiceCenter', i
ndex 'applicationm1.applicationm1_P' (ID 1589580701) (index ID 2). Extra or
invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with values (app
lication = 'cc.find.problem' and label = 'build.s) points to the data row id
entified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 3
consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1
consistency errors in table 'applicationm1' (object ID 1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 4
consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum re
pair level for the errors found by DBCC CHECKDB (ServiceCenter ).|||There are a lot of bugs/problems, replicating text/image columns. First make
sure all participating servers are on the latest service pack. Then see if
you find any hits for those error numbers at: http://support.microsoft.com
And consider contacting Microsoft support.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ghost" <anonymous@.discussions.microsoft.com> wrote in message
news:07926FA4-64B3-4038-8934-E2A3A631DDE3@.microsoft.com...
Yes, There are both image and text columns in some of the tables.
Below is excerpt from the maintenance log on the errors we are getting:
[1] Database ServiceCenter: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8929: [Microsoft][ODBC SQL
Server Driver][SQL Server]Object ID 525244926: Errors found in text ID
462078345216 owned by data record identified by RID = (1:202550:3).
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:40468), slot 12, text
ID 462078345216 is referenced by page (1:202550), slot 3, but was not seen
in the scan.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID
525244926. The text, ntext, or image node at page (1:695828), slot 12, text
ID 462078345216 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Database
'ServiceCenter', index 'applicationm1.applicationm1_P' (ID 1589580701)
(index ID 2). Extra or invalid key for the keys:
[Microsoft][ODBC SQL Server Driver][SQL Server]Index row (1:81744:140) with
values (application = 'cc.find.problem' and label = 'build.s) points to the
data row identified by (RID = (1:348126:8)).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 3 consistency errors in table 'problemm1' (object ID 525244926).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 1 consistency errors in table 'applicationm1' (object ID
1589580701).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation
errors and 4 consistency errors in database 'ServiceCenter'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the
minimum repair level for the errors found by DBCC CHECKDB (ServiceCenter ).

data archiving and replication

Hi Guys,
i am using sql 2000 transactional replication to have
reporting server.
in the oltp server tables i want to keep only one years
data (around 1o million) while in the reporting server i
will export the archived data and keep all the data so
that i can take reportfor any period.
will this make any problem for replication.like
syncronisation or anything?
pls advice me.
Right click on your publication select publication properties, go to the
articles tab, click on command. Change your delete command to NONE on your
publisher.
"biju george" <bijupg@.hotmail.com> wrote in message
news:11d3a01c410d3$b68b68b0$a401280a@.phx.gbl...
> Hi Guys,
> i am using sql 2000 transactional replication to have
> reporting server.
> in the oltp server tables i want to keep only one years
> data (around 1o million) while in the reporting server i
> will export the archived data and keep all the data so
> that i can take reportfor any period.
> will this make any problem for replication.like
> syncronisation or anything?
> pls advice me.
|||Biju,
the way you implement this depends on what type of replication you're
considering. For snapshot replication ,you could use static filters. Each
time you want to archive off some data, you modify the filter clause. Be
sure to modify the article properties, naming conflicts to "Delete data in
existing table that matches the row filter statement." - before the
synchronization, there won't be any matching data as it only exists on the
publisher. After that you remove the data on the publisher. For
transactional you could set it up as per usual and just select not to
replicate delete commands - all new records will go to the subscriber and on
the publisher old records can be deleted with this not affecting the
subscriber. Finally, just to give you some other options, you could archive
the data to another table on the publisher. On the publisher create a view
which unions the data, and then create an index on the view and replicate
the indexed view. Actually this would probably be my choice, as it maintains
the source data on the publisher.
HTH,
Paul Ibison
|||i am using transactional replication. i deleted old data
from publisher and i copied the archived data in another
table on publisher . now the data on the publisher and
subsriber are SAME,
but for reporting to get all the data i will export the
old data From the archived table at publisher to the
orginal table at subscriber using DTS.
using the indexed view will be slower in report generation
is'nt it?
>--Original Message--
>Biju,
>the way you implement this depends on what type of
replication you're
>considering. For snapshot replication ,you could use
static filters. Each
>time you want to archive off some data, you modify the
filter clause. Be
>sure to modify the article properties, naming conflicts
to "Delete data in
>existing table that matches the row filter statement." -
before the
>synchronization, there won't be any matching data as it
only exists on the
>publisher. After that you remove the data on the
publisher. For
>transactional you could set it up as per usual and just
select not to
>replicate delete commands - all new records will go to
the subscriber and on
>the publisher old records can be deleted with this not
affecting the
>subscriber. Finally, just to give you some other options,
you could archive
>the data to another table on the publisher. On the
publisher create a view
>which unions the data, and then create an index on the
view and replicate
>the indexed view. Actually this would probably be my
choice, as it maintains
>the source data on the publisher.
>HTH,
>Paul Ibison
>
>.
>
|||Hilary's reply is the way to go to continuously keep the reporting server
upto date. For his reply to work you'll need all the data in one table on
the publisher initially, then remove the 'archived' rows. If you are to
continue partitioning the data on the publisher, then you can use my
snapshot advice to append the data - no need for DTS, although it is
functionally the same methodology here. My suggestion for indexed views also
works. It wouldn't be slower for reporting, because the indexed view
amalgamates the data on the publisher and replicates it to one table on the
subscriber. However, changes to data on the publisher will be fractionally
slower as the indexed view will get updated behind the scenes.
Regards,
Paul Ibison
|||for this i have to create indexed view or only replicate
the view?
will it give more load to the publisher.?
>--Original Message--
>Hilary's reply is the way to go to continuously keep the
reporting server
>upto date. For his reply to work you'll need all the data
in one table on
>the publisher initially, then remove the 'archived' rows.
If you are to
>continue partitioning the data on the publisher, then you
can use my
>snapshot advice to append the data - no need for DTS,
although it is
>functionally the same methodology here. My suggestion for
indexed views also
>works. It wouldn't be slower for reporting, because the
indexed view
>amalgamates the data on the publisher and replicates it
to one table on the
>subscriber. However, changes to data on the publisher
will be fractionally
>slower as the indexed view will get updated behind the
scenes.
>Regards,
>Paul Ibison
>
>.
>
|||Biju,
actually I just tried this out for you and unfortunately the restrictions on indexed views are too severe for this to be a viable solution - sorry. So, that leaves you with Hilary's advice in which case you need to initially amalgamate the data on the pub
lisher, create the publication, synchronize then delete the archived data on the publisher. Or, use a linked server or snapshot replication. Also, if you want to keep the tables separate, you could use replication to copy the data to separate tables on th
e subscriber (with different names) and have a view there to amalgamate the data.
Regards,
Paul Ibison
|||already i have the archived data in a seperate table at
publisher.so i will export this data to the orginal table
at subscriber so that report will be generated from table
having complete table.
will this idea give any problem in future ?from my testing
it works fine.
pls advice me.
>--Original Message--
>Biju,
>actually I just tried this out for you and unfortunately
the restrictions on indexed views are too severe for this
to be a viable solution - sorry. So, that leaves you with
Hilary's advice in which case you need to initially
amalgamate the data on the publisher, create the
publication, synchronize then delete the archived data on
the publisher. Or, use a linked server or snapshot
replication. Also, if you want to keep the tables
separate, you could use replication to copy the data to
separate tables on the subscriber (with different names)
and have a view there to amalgamate the data.
>Regards,
>Paul Ibison
>.
>
|||Biju,
if you want to be able to report on ALL your data at the subscriber, you initially need to amalgamate the records from 2 tables - your live table and your archive table - on the publisher into one on the subscriber. The easiest way to do this is to first
do it manually (eg using linked servers). Then apply Hilary's solution to the production table, stating that the schema already exists on the subscriber and making sure to not replicate deletes.
Regards,
Paul Ibison
|||Paul,
If I understand properly, there are actually two types of deletes that would
occur:
1. Deletes relating to normal transaction management.
2. Deletes relating to archiving data. Here data is copied to the archive
table and deleted from the live table.
If deletes are not allowed to happen at the subscriber then it affects both
cases of deletes. What should actually happen is that deletes for case 1
should be replicated but not those for case 2. This is because if we did not
allow case 1 deletes to replicate to subscriber, the latter would contain
data that does not exist in publisher anymore, with all the consequences
thereof (wrong reporting at subscriber?).
I think the other solutions suggested should work, namely:
1. Publisher has tables Live and Archive.
2. Subscriber has same tables and each table gets data from the
corresponding tables from Publisher. Synchronisation should happen normally
for both tables.
3. Create a normal view on subscriber with UNION of data from both tables.
Use this view for reporting purposes.
Hoping I am not shooting off-target!
Raj Moloye
Mauritius

Data added before publication created does not replicate - why?

Hello,

I have a small three server development environment where I am getting my feet wet with replication. I have set up peer-to-peer transactional replication and it works fine for data added to the publication's table after the publication was created. However, rows in the table that existed prior to the publication's creation have never replicated. If any of the "old" rows are edited they cause an error on the subscribing servers when the replicator attempts to apply updates to rows that do not exist.

How can I get the old rows that predate the publication to replicate?

Thanks,

BCB

I believe to setup peer-to-peer replication, you'll need to initialize the data at each node through backup and restore. You can find more info here.

http://msdn2.microsoft.com/en-us/library/ms146914.aspx

Gary