I have straight forward transactional replication set up on a SQL2000 database to a SQL 2005 database. The distribution agent is on SQL2000 and is push replication.
Recently the replication has failed with the error "There are already statistics on table" error number 1927. When this occurs I have data missing from other tables aswell which is very strange.
The error is correct, and to cure it I have to remove the offending statistic on 2005 and reinitialise the replication. What is also strange is that the statistic name is not _WA but another name (and not the name of an index). Obviously this is not the ideal situation as firstly this could occur at any time and therefore leave the database unusable (and some angry users as this is a 24/7 application), and secondly a reinitialisation of the replication could take some time.
I have found an article in the MS Knowledge Base, however it does not work. The reason it does not work is because it suggests turning off the "Copy objects to destination...Non clustered indexes" option in the article. However this option is disabled and I can not change it.
Does anyone have any insight into this error, I need a direction to investigate desparately.
I would double check the article and see if doesn't mention Merge replication as that is the only one I know you can drop that setting.
However that said check that the database you are replicating to does not have auto create statistics turned on for the database. I would think that should correct the issue.
No it's definitely a transactional replication publication.
I will turn off the auto create stats - still a little confused though. The statistic was a weird name (hind_133213 or something) which I don't think is an auto stat, and the same name existed on both databases. There is no index or column called hind, and chances of it being the same name should be pretty slim. I think it was replicated across and an untrapped error occurred, which left the replication in disarray.
What is more concerning is that there were empty tables in the database. I have the articles set to delete data from tables on the snapshot page, and I assumed that this only occurred (deletion from table) when a snapshot was produced (i.e. on reinitialisation). This seems to indicate that a snapshot was performed, but I have no idea why that should have occurred. I assumed that with a transactional replication only the new/edited/deleted rows were replicated, not the whole of the table after each change.
Anyhow, have turned off the auto create stats and will see what happens. I am interested as to what impact that will have, the 2005 database is for reporting only, so I would expect it to have different statistics from the 2000 database (which is the data entry database) as the data is used in a different way.