Troubleshooting Transactional Replication

Question

Post reply

Troubleshooting Transactional Replication

Welsh Corgi

SSC Guru

Points: 116520
More actions
February 25, 2016 at 9:34 am

#311725

I purposely added a View (Customer) as an article without adding the dependent Table (Customer)as an article so that I could capture the error and delete the xact_seqno from the MSRepl_Commands Table.
There is no error displayed in Replication Monitor and no entry in the MSrepl_errors Table.
I'm not getting an error but I get a message that ;The initial snapshot for the customer_view is not available.
I was expecting an error. There are 4 undistributed commands.
After that I added the table as an article.
Now I get an message that 'The initial snapshot for publication 'testpub is not yet available'.
I'm trying to determine what I'm doing wrong?
I expected an error.
Thank you.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I generated a new snapshot. There are no undistributed commands but the table and view do not exist at the subscriber.

I had an action message that the Snapshot Agent that 100%. A snapshot of 2 articles(s) was generated.

I still get message that 'The initial snapshot for publication 'testpub is not yet available'.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 2

I would first try reinitialising it, perhaps scripting it out and recreating it - something's got all out of whack somewhere down the line. Is there anything relevant in the sql error log?

Why do you want to force an error anyway?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I already tried reinitializing.

The weird thing is that they show as a published article but they did not get replicated.

No relevant errors.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I'm stuck on the message 'The initial snapshot for publication 'testpub is not yet available'.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I saw the following thread that is related to my problem.

http://www.sqlservercentral.com/Forums/Topic342644-7-2.aspx">

http://www.sqlservercentral.com/Forums/Topic342644-7-2.aspx

Hmmm.:unsure:

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 6

Are all 3 agents running?

Try scripting out and recreating the subscription.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

All of the Agents were running.

I tried Dropping and recreating the subscription but that did not work.

I'm starting over by recreating distribution.

I execute the following Command from the Publisher and I get the following error:

exec sp_replicationdboption @dbname='ReplicationTestDB', @optname= 'publish', @value= 'true'

GO

exec sp_replicationdboption @dbname='ReplicationTestDB', @optname= 'publish', @value= 'true'

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

When creating the Publisher I execute the following command:

use [ReplicationTestDB]

exec sp_replicationdboption @dbname = N'ReplicationTestDB', @optname = N'publish', @value = N'true'

I get the following error:

Could not connect to server 'ASSET2629\DISTRIBUTOR' because 'ASSET2629\PUBLISHER' is not defined as a remote server. Verify that you have specified the correct server name. .

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 9

I guess you've already seen this, but just in case not: https://support.microsoft.com/en-us/kb/217395

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

I cleaned up everything and recreated the DISTRIBUTER, PUBLISHER and SUBSCRIBER.

If I add the view as an article without the dependent object (table) it does not generate an error.

I was trying to force the error so I could identify the Sequence number and the command that failed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 11

Beatrix Kiddo

SSC-Dedicated

Points: 32407

February 26, 2016 at 8:10 am

#1861406

Can I ask why?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Because I was stuck and I wanted to start over.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/