Subscription of transactional replication not working as expected???

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

I dont find any problem from the verbose log.

It says A total of 1 transaction(s) with 276 command(s) were delivered. which means it successfully transffered data.

Can you explain a bit more on what is the actual issue?

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

Hi Ashwin,

this is the issue,

on publisher database i have data till 4th march , and this has been backed up and restored on the production.

Then the data load happened on the secondary server for 5th march so now data available in secondary database till 5th-march.

then i started to take snapshot against the secondary db and executed the log reader and then executed the distributor agent on the production db(primary db).

Then if i check for the data on production db, the data for 5th-march should have been replicated to the production db but it is not so done.

Note : log reader agent was set scheduled to run continuously.

If needed i can provide the log for the snapshot and log reader agents, but there also we didnt face any errors or warnings and the process said success only.But we are not able to figure out the cause

Thanks,

Aish

Answer 3

I am not clear as to which server is publisher and which is subscriber? can you explain that bit?

How did you setup replication? Are you sure that you initialized it with snaphshots? or with backups?

If you initilialized with backups then you wont be able to apply snapshots later on.

Can you share the script of replication?

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 4

hi ashwin,

here is the template of the replication publication script used :

-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'databasename_3101_01_2', @optname = N'publish', @value = N'true'
GO

exec [databasename_3101_01_2].sys.sp_addlogreader_agent @job_login = N'prd-us\lisvc01', @job_password = null, @publisher_security_mode = 1
GO
exec [databasename_3101_01_2].sys.sp_addqreader_agent @job_login = N'prd-us\lisvc01', @job_password = null, @frompublisher = 1
GO
-- Adding the transactional publication
use [databasename_3101_01_2]
exec sp_addpublication @publication = N'Replication_databasename_3101', @description = N'Transactional publication of database ''databasename_3101_01_2'' from Publisher ''MachineA''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO


exec sp_addpublication_snapshot @publication = N'Replication_databasename_3101', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'prd-us\lisvc01', @job_password = null, @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'Replication_databasename_3101', @login = N'sa'
GO
-- Adding the transactional articles
use [databasename_3101_01_2]
exec sp_addarticle @publication = N'Replication_databasename_3101', @article = N'BLSDataMSAWise10', @source_owner = N'dbo', @source_object = N'BLSDataMSAWise10', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'BLSDataMSAWise10', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_dboBLSDataMSAWise10]', @del_cmd = N'CALL [dbo].[sp_MSdel_dboBLSDataMSAWise10]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboBLSDataMSAWise10]'
GO

-- Adding the transactional subscriptions
use [databasename_3101_01_2]
exec sp_addsubscription @publication = N'Replication_databasename_3101', @subscriber = N'MachineB', @destination_db = N'databasename_3101_01_2_old', @subscription_type = N'Pull', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
GO
Sorry for the delay and please let me know if something has gone wrong here, that would be very helpful.

thanks for your support.