5

I am trying to get (pull) Transactional Replication working again for a subscriber.

Currently, in Replication Monitor I am seeing messages in the Publisher to Distributor tab and the Distributor to Subscriber tab.

In the distributor to subscriber history, I see 'concurrent snapshot for publication 'XX' not available'. In the publisher to distributor history, I see the Log Reader Agent initializing and then waiting for a response from the server (every 5 minutes the message is logged again).

Ultimately the execution of sp_replcmds fails.

I think there may be many unreplicated transactions which are causing the Log Reader Agent to hang. Following Troubleshooting LogReader Timeouts

I ran dbcc opentran and then attempted to run

select  [Transaction ID], count([Transaction ID]) as 'Count log entries'
from::fn_dblog('42006:31381:2',null) 
where [Description] = 'REPLICATE'
group by [Transaction ID]

This query did not return any results after over 40 minutes. Moreover, when I just run SELECT count(*) FROM ::fn_dblog(NULL, NULL) I am not getting results after a long time (20 minutes).

I am starting to think there is something up with the size of my transaction log, or some kind of corruption, or something.

Any help would be greatly appreciated.

Improve this question
4
  • 1
    How many VLFs do you have in that log file? You can use DBCC LOGINFO to get a row per VLF. What's the count?
    – Thomas Stringer
    Commented Feb 3, 2013 at 20:28
  • DBCC LOGINFO returns 411 rows.
    – slachterman
    Commented Feb 3, 2013 at 22:40
  • The Replication Monitor has a screen where it shows the number of unreplicated commands. Can you find the number from there? (Sorry, I don't have the exact steps in front of me right now.) Also, is the transaction log file a reasonable size, or is it really large?
    – Jon Seigel
    Commented Feb 4, 2013 at 1:41
  • The transaction log is 932624 pages. There are 0 unreplicated commands on the Undistributed Commands tab. I am inclined to delete and re-create the publication at this point.
    – slachterman
    Commented Feb 5, 2013 at 17:02

1 Answer 1

Reset to default
1

If fn_dblog is hanging you may try some third party tools from ApexSQL, RedGate or Idera. Or you can try to backup the transaction log file and use fn_dump_dblog just to exclude problems with an online transaction log. You can find more info on this DBA post

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.