Replication fails after snapshot delivery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Replication (2008)

Replication fails after snapshot delivery

Author

Topic

PhilipLewis
Starting Member

30 Posts

Posted - 2014-09-22 : 12:39:37

Environment:

SQLServer 2008R2 10.50.4297 Publisher/Distributor
SQLServer 2008R2 10.50.4319 Subscriber

I have created a bi-directional queued replication.
Immediately after applying the first snapshot, it fails with a syntax error, and then restarts itself. This is an endless loop of crashes.

Last 3 lines of log (directly from the monitor)

Incorrect syntax near the keyword 'WHERE'.
Delivered snapshot from the 'unc\UPDATEABLEDATA\20140922175831\' sub-folder in 667606 milliseconds
Applied script 'T_datalog_6.dri'
.
and so on ...

I scripted all the stored procedures created on the subscriber database, and they all parse correctly. I take this as an indication that it is not the insert/update/delete of rows after the snapshot delivery message that has a syntax error.

So what happens after the “Delivered snapshot …” message and before the first row is replicated?
What is generated with a syntax error?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-22 : 12:55:10

Run a trace to determine what is being run when the syntax error occurs.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

PhilipLewis
Starting Member

30 Posts

Posted - 2014-09-22 : 14:37:12

Well, that was a circus - The profiler is abominable to use.
However, the generated command in error has been found (so thanks for the suggestion) and is ...

IF @@microsoftversion >= 0x0A000000 EXEC(N'CREATE NONCLUSTERED INDEX [mail_idx] ON [dbo].[T_statistic_fenet_log]([log_date]) WITH (DATA_COMPRESSION = PAGE ) WHERE ([level]=''error'' AND [server_name]=''www.MyCo.net'') ON [PRIMARY]')

Clearly, this is a filtered index. Are they unsupported?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-22 : 15:02:56

We have a filtered index in our subscriber database, so they are definitely supported. That system is SQL Server 2008 R2.

What is the compatibility level of the subscriber database?

Also, we create the indexes manually after the snapshot is complete. You could try doing that instead of including them in the snapshot. I should say that at my last job, we let the snapshot create the indexes. But at my new job where we do have a filtered index on the subscriber database, we have run a script after the snapshot is done that adds all of the non-clustered indexes, including the filtered index.

I suspect this is a filtered index issue with the snapshot or a compatibility level issue.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-09-22 : 19:13:18

Could it be that your setting for set Quoted_Identifier is erroneous? What value do you have?

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

PhilipLewis
Starting Member

30 Posts

Posted - 2014-09-23 : 04:58:56

The scripted version from the source database looks like this.

USE [MyDb]
GO

/****** Object: Index [mail_idx] Script Date: 23-09-2014 10:08:50 ******/
CREATE NONCLUSTERED INDEX [mail_idx] ON [dbo].[T_statistic_fenet_log]
(
[log_date] ASC
)
WHERE ([level]='error' AND [server_name]='www.MyCo.net')
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100, DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO

Finally, I did put the version numbers in the OP, and the compatibility levels are the same (100)

Anyway, I simply dropped the index, did my replication and added it again afterwards to both databases. Reinitializing in the future might be an issue :(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-23 : 12:09:50

You should open a case with Microsoft on this as this sounds like a bug.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources