Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"There are already statistics on table" error 1927 - can not resolve Expand / Collapse
Author
Message
Posted Wednesday, May 9, 2007 4:41 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 424, Visits: 245

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.

Thanks,

Brian Jones

Post #364313
Posted Wednesday, May 9, 2007 7:36 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745

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.




Post #364351
Posted Wednesday, May 9, 2007 8:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 424, Visits: 245

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.

Post #364373
Posted Thursday, April 17, 2008 11:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 8, 2008 4:03 PM
Points: 36, Visits: 65
Hi,

I am having the same problem. I have transactional replication from 2000 to 2005. Were you able to resolve your problem? Can you post your experience here.

Thanks a lot.
Post #486659
Posted Friday, April 18, 2008 12:06 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 424, Visits: 245
Turning off the auto-statistics on the destination database worked for me, and performance is fine for my needs. So not pursued this any further.
Post #487410
Posted Friday, April 18, 2008 1:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 8, 2008 4:03 PM
Points: 36, Visits: 65
Thanks for the response.

I already have auto create and auto update statistics off on the destination database. my source is sql 2000 and my destination is sql 2005. Do you have any idea?

Thanks
Post #487452
Posted Monday, April 21, 2008 4:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:09 AM
Points: 424, Visits: 245
Unfortunately I've changed contract since this posting so can't check what the settings are now on the database. From what I recall I turned off all the replication of objects so that only the data was being transferred. Sorry can't help any further.
Post #487796
Posted Thursday, May 17, 2012 7:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 3, 2014 7:05 AM
Points: 32, Visits: 147
Hi,

I faced the same issue , on analysing I found that Article setting for "if the object with same name already exists" was to "Truncate table and reload data" . I modified it to "Delete the object and re-create" then it worked fine.

Solution :

Modified Article properties for the table

Property : Action if name in use

Earlier Value : Truncate All data in the existing object

Fixed value : Drop Existing object and create a new one

Regards,
Avi
Post #1301776
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse