Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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 Feb 3 '13 at 20:28
    
DBCC LOGINFO returns 411 rows. –  slachterman Feb 3 '13 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 Feb 4 '13 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 Feb 5 '13 at 17:02
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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