Post reply

SNAPSHOT Replication Preventing Log Truncation on Full RecoveryModel

  • January 12, 2010 at 7:25 am

    #214498

    I have not been able to manually shrink a particular database's log nor have regular t-log back-ups been able to do this. I found out through sys.databases that this is being prevented because of replication. This does not seem to make sense because from what I have read, SNAPSHOT repliation should not be doing this.

    Any thoughts would be greatly appreciated.

    Thanks.

  • January 12, 2010 at 7:54 am

    #1102434

    This might help you to figure out issue

    Also, check BOL for sp_repldone which might help you shrink your log

    HTH...

    The_SQL_DBA
    MCTS

    "Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

  • January 12, 2010 at 7:59 am

    #1102440

    Can you check and confirm that there is absolutely no transactional replication in that database?

    What does DBCC OPENTRAN return?

    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

  • January 12, 2010 at 8:38 am

    #1102479

    I'm positive that only Snapshot replication is what we are using. DBCC OPENTRAN returns:

    Replicated Transaction Information:

    Oldest distributed LSN : (0:0:0)

    Oldest non-distributed LSN : (162249:138085:1)

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

    Thanks.

  • January 12, 2010 at 9:16 am

    #1102512

    Can you just go through all of the publications, make sure none of them are transactional, because if there is one, the advice I'm going to give will break 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

  • January 12, 2010 at 9:34 am

    #1102530

    See =http://www.sqlserverspecialists.co.uk/blog/_archives/2009/5/5/4175469.html

    You may have the same issue.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • January 12, 2010 at 9:35 am

    #1102532

    Thanks Gail. When I go over the publication ( there is only one), the type is SNAPSHOT. However, when I look at the PUBLICATION databases under PUBLICATION Properties, I get the window that shows two columns for TRANSACTIONAL and MERGE. The checkbox under transactional is ON for the database in question. Could this be the culprit? It's weird because as I stated, there is only one publication and it is of the SNAPSHOT variety. I don't know if this was defaulted to when I set this SNAPSHOT publication up as while back.

    Thanks again.

  • January 12, 2010 at 9:38 am

    #1102535

    Hi Carolyn,

    That's interesting. I'll check it out as well.

    Thanks.

  • January 12, 2010 at 11:12 am

    #1102618

    Can you uncheck the checkbox under publication properties?

    If not (or if it doesn't change the output of DBCC opentran), please try this.

    Create a transactional replication publication.

    Add a single table as an article (a small one). It doesn't matter what table.

    run sp_repldone

    Drop the publication that you just created.

    Check DBCC OPENTRAN. It should no longer mention replicated and unreplicated LSNs.

    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

  • January 12, 2010 at 11:18 am

    #1102620

    Thanks Gail! I appreciate the help. I'll let you know what happens.

  • January 12, 2010 at 11:23 am

    #1102626

    Unchecking the checkbox for transactional replication yields:

    No active open transactions.

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

    I think that I'm good now. Thank you all!!!

Viewing 11 posts - 1 through 10 (of 10 total)

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