potential problem with replication

Question

Hi there

I am facing a potential problem with replication,

I have setup transactional-pull subscription in production and i am starting the snapshot and its running successfully but when i invoke the subscription the data is not getting propagated to the subscriber,

The subscriber is set not to initialize meaning it has to start from the already existing database.The log reader is set to run continuously,(and also the publication articles attribute "Action if name is in use" is set to "Keep existing". tried wit the delete option without any filters as well but it seems to have no effect)

here is the log for subscription,

*******************************************************************************

2012-03-08 05:04:50.032 Microsoft SQL Server Distribution Agent 10.0.1600.22
2012-03-08 05:04:50.033 Copyright (c) 2008 Microsoft Corporation
2012-03-08 05:04:50.033 Microsoft SQL Server Replication Agent: distrib
2012-03-08 05:04:50.034
2012-03-08 05:04:50.034 The timestamps prepended to the output lines are expressed in terms of UTC time.
2012-03-08 05:04:50.034 User-specified agent parameter values:
            -Publisher SQUID
            -PublisherDB LaborInsight_3101_01_2
            -Publication Replication_LaborInsight_3101
            -Distributor SQUID
            -SubscriptionType 1
            -Subscriber DOLPHIN
            -SubscriberSecurityMode 1
            -Output F:\User\senthilp\OUTPUTFILE.txt
            -Outputverboselevel 1
            -SubscriberDB LaborInsight_3101_01_2
            -XJOBID 0x069BDB3B5C3A4340BDCFD06441C4F447
            -XJOBNAME SQUID-LaborInsight_3101_-Replication_LaborI-DOLPHIN-LaborInsight_3101_-4E2B8421-7677-4F3C-BE30-2AE4E1ED632A
            -XSTEPID 1
            -XSUBSYSTEM Distribution
            -XSERVER DOLPHIN
            -XCMDLINE 0
            -XCancelEventHandle 0000000000000780
            -XParentProcessHandle 00000000000007F0
2012-03-08 05:04:50.037 Startup Delay: 1166 (msecs)
2012-03-08 05:04:51.204 Connecting to Subscriber 'DOLPHIN'
2012-03-08 05:04:51.219 Connecting to Distributor 'SQUID'
2012-03-08 05:04:51.278 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 1
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2012-03-08 05:04:51.371 Initializing
************************ STATISTICS SINCE AGENT STARTED ***********************
03-07-2012 23:04:55

Total Run Time (ms) : 4025     Total Work Time  : 3806
Total Num Trans     : 1     Num Trans/Sec    : 0.26
Total Num Cmds      : 276     Num Cmds/Sec     : 72.52
Total Idle Time     : 0

Writer Thread Stats
  Total Number of Retries   : 0
  Time Spent on Exec        : 31
  Time Spent on Commits (ms): 0     Commits/Sec         : 0.26
  Time to Apply Cmds (ms)   : 47     Cmds/Sec            : 5872.34
  Time Cmd Queue Empty (ms) : 3791     Empty Q Waits > 10ms: 2
  Total Time Request Blk(ms): 3791
  P2P Work Time (ms)        : 0     P2P Cmds Skipped    : 0

Reader Thread Stats
  Calls to Retrieve Cmds    : 2
  Time to Retrieve Cmds (ms): 3806     Cmds/Sec            : 72.52
  Time Cmd Queue Full (ms)  : 32     Full Q Waits > 10ms : 2
*******************************************************************************

2012-03-08 05:04:55.231 A total of 1 transaction(s) with 276 command(s) were delivered.

Can anyone please do help me out to figure out the cause? This is very urgent.

Thanks,

Aish

Answer 1

Do an update like this on one of the published tables.

set rowcount 10

update publishedtable set Col1=Col1

See if this causes some data flow.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hello,

Yes if i tried something related to like this,

Updated a published table for small amount of row and it got propagated to the subscriber db.

But if i try the same for the bulk data its not working.

Answer 3

@IshNair

Did you see some error message about bulk copy? If yes, please refer to this online article http://www.replicationanswers.com/General.asp

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 4

No i didnt face any error message during bulk copy

Answer 5

What do you mean bulk data? Are you bcp'ing into the tables? Exactly what do you mean by this:

But if i try the same for the bulk data its not working.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

I tried changing the data of a single table like how you have mentioned sir and its getting replicated,

But the bulk insert done for the day, is not moving to the subscriber database.Bulk insert is the .net framework feature being used, we are not using the bulk bcp'ying sir.

Answer 7

Can you confirm you are using the SqlBulkCopy class? What version of SQL and what version of .net are you using?

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 8

Yes it is the SQlBulkCopy class.

The version of sql used is:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

And the .net framework used is: .net framework 3.5

Thanks,

Aish

Answer 9

Can you check what is value for property @fire_triggers_on_snapshot  for this article?

Merge replication track and enumerates changes via triggers and triggers will not get executed in case of Bulk Insert until unless FIRE_TRIGGERS option is specified. This is also true for the bulk insert process of transactional replication.

---

Kindly mark the reply as answer if they help

Answer 10

Hi Sunil,

I have found the value of @fire_triggers_on_snapshot set to 0 for all the articles, i have now updated to the value of @fire_triggers_on_snapshot  to 1 from dbo.sysarticles.

let me try for the snapshot and subscription for the day and get back to you.

Answer 11

i have tried with the flag(@fire_triggers_on_snapshot) set for each article, now also i dont find a any of the data getting propagated to the subscriber database.

here is the log which is not throwing me any error also : 

2012-03-19 13:54:49.979 Applied the snapshot to the Subscriber.
2012-03-19 13:54:49.982 Adding alert to msdb..sysreplicationalerts: ErrorId = 0, 
Transaction Seqno = 00012c6b0002ab53016f00000001, Command ID = 1
Message: Replication-Replication Distribution Subsystem: agent SQUID-LaborInsight_3101_01_-Replication_LaborInsi-DOLPHIN-14 succeeded. Applied the snapshot to the Subscriber.Disconnecting from OLE DB Subscriber 'DOLPHIN'