Oldest non-distributed LSN but No Transactional Replication

  • SQLSvrStevo

    Mr or Mrs. 500

    Points: 522

    My transaction log and mdf backup file keep ballooning in size and DBCC check log gives

    Transaction information for database 'hub_live'.

    Replicated Transaction Information:

    Oldest distributed LSN : (0:0:0)

    Oldest non-distributed LSN : (23798:744:1)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    The db currently has snapshot replication. Transactional replication was removed by someone else 6 weeks ago. We go through the process of releasing the LSN (ie. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1), changing to simple recovery, shrinking the logs every fortnight, but then the log and backups balloon again.

    I can't find any SQL Agent jobs that are to do with transactional replication (only snapshot) and logread.exe isn't running. The only job step I can find is the snapshot replication agent "Replication Snapshot" set which is

    -Publisher [SVRSQL6] -PublisherDB [db_live] -Distributor [SVRSQL6] -Publication [db_live_replication] -DistributorSecurityMode 1

    I couldn't locate anything either in the log files or the the event viewer.

    Any assistance would be much appreciated.

    Stevo

  • Gail Shaw

    SSC Guru

    Points: 1004424

    See the replication section of this article: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQLSvrStevo

    Mr or Mrs. 500

    Points: 522

    Thanks for the response.

    There was no Change Data Control enabled, but select name, log_reuse_wait_desc from sys.databases did flag the issue as replication.

    I found a great article on http://blogs.msdn.com/b/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx.

    It seems snapshot replication that includes DDL changes can cause non distributed LSN problems as the LSN isn't released even after succesful (in every other sense) replication.

    Ths seems to be a known bug.

    The workaround is not to replicate schema changes OR to add sp_repldone null, null, 0,0,1 into an agent job

    Many thanks,

    Stevo

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Yup, that post and the issue is mentioned in the article I referenced (which is why I referred you to it)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • AK1516

    SSCrazy

    Points: 2705

    I have the similar issue with transactional replciation... any help..... ?

  • David Benoit

    SSC-Dedicated

    Points: 34562

    What specifically is the problem? Is your log reader agent running?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Please post new questions in a new thread and give as much detail as possible.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • AK1516

    SSCrazy

    Points: 2705

    Yes the log reader agent is running:

    Initially i had problems shrinking the log file. Later I had used exec sp_replrestart and restated the

    replication and also manually unmarked the transactions pending for replication using the following

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time

    = 0, @reset = 1

    and then could shrink the log

    just as trouble shoot i found that the database log is not being reused due replication from following :

    select name, log_reuse_wait_desc from sys.databases

    but after a while I again see the same following error

    The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

    The distributon agent is working fine but the log reader agent doesnot.....

    It keep on failing even after restarting the agent....

    One last thing... I have the sync with backup enabled between distributor and subscriber...

  • David Benoit

    SSC-Dedicated

    Points: 34562

    As Gail stated you should really start a new thread. When you do please post the link in this one so that I know where it is.

    For now though, by executing the repldone you have put yourself in an out of sync condition and have removed all transactions in the log that have not yet made it to the distribution database. With that being said, I also believe that you have a metadata inconsistency with that publication or subscription which is causing the failure. If at all possible I would recommend rebuilding the publications / subscriptions and seeing if that clears things up. You can do that without initializing the subscriber (if initializing is a problem) as one of the options when you create the subscription. Regardless, due to the sp_repldone execution you will have to do some sort of manual sync to get your data in line.

    Let me know how you decide to proceed. If you want to look for the inconsistencies in the metadata I can provide you some scripts but let me know that in the new thread.

    Thanks.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • AK1516

    SSCrazy

    Points: 2705

    I have recreated the publications and subscriptions ... again its sync and running fine... Thanks 🙂

  • David Benoit

    SSC-Dedicated

    Points: 34562

    Excellent - glad that worked and thanks for the update.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • mahbub555

    Valued Member

    Points: 64

    same ..solved... deleted the publication and subscription then recreated it.

  • Imran-Yasir

    SSCommitted

    Points: 1934

    Thank you very much, I got the same issue and fixed it with you guys help here.

  • Imran-Yasir

    SSCommitted

    Points: 1934

    Hello Respected Team, I have a issue:

    -Production database log file has grown 120GB

    -I have hourly log backups running but even than log file is not getting truncate and shrink

    -I saw we have a snapshot database created under Databases-->Database Snapshots

    -I have dropped that snapshot

    -I have run following to commands to drop any replication though I don't have any kind of replication at this server

    sp_removedbreplication [HDMPRODUCTION]

    go

    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1

    -But when I run SELECT name, log_reuse_wait_desc FROM sys.databases I still sees Replication under column log_reuse_wait_desc

    -After running above commands log file got truncated but still not able to shrink it.

    Any idea why I still sees Replication under column log_reuse_wait_desc?

    Thanks for any help

    Imran

  • David Benoit

    SSC-Dedicated

    Points: 34562

    What do you see when you run dbcc opentran(YourDBName)? Check the SPID that shows in the output.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Viewing 15 posts - 1 through 15 (of 16 total)

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