SQL Server Performance

Snapshot replication

Discussion in 'SQL Server 2005 Replication' started by emamuthu, Mar 14, 2007.

  1. emamuthu New Member

    Hi
    Periodically my SQL 2005 snapshot publication database starts to grow it's log
    file without truncating.
    - The recovery model is 'simple'
    - There is no logreader agent, as it's snapshot only.
    - DBCC OPENTRAN, returns non-zero values for non-distributed LSN, but never
    returns any SPID!

    All of my searching on the 'net has led me back to executing DBCC opentran,
    to find the offending spid, to kill it ... but DBCC OPENtran has never
    returned any Spids! it returns this sort of thing:

    "Transaction information for database '<myDB>'.

    Replicated Transaction Information:
    Oldest distributed LSN : (0:0:0)
    Oldest non-distributed LSN : (202506:21682:1)
    DBCC execution completed. If DBCC printed error messages, contact your
    system administrator."

    Dropping the publication, and shrinking the log file always works, but is a
    bit extreme, and difficult to do on a production DB. This issue occurs about
    once a week, with no apparent pattern that I can figure out...yet.
    Any help is appreciated.
    Thanks
  2. MohammedU New Member

    Try the following at your own risk...check BOL for info...

    sp_repldone updates the record that identifies the last distributed transaction of the server. This stored procedure is executed at the Publisher on the publication database.

    Caution:
    If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only be used for troubleshooting replication as directed by an experienced replication support professional.


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





    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. emamuthu New Member

    Thanks for the reply. I want know why log wait desc in sys.database is marked as replication even though my snapshot replication compelted successfully.

    Thanks
    emamuthu

  4. MohammedU New Member

    Merge and Snapshot replications are not tlog depedent so log should not effect.
    No idea what cuased this...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  5. emamuthu New Member

    Mohammed,
    I tried to reset the replication status in sys.databases using
    sp_repldone, but still it's shows up .

    Do we have any other options other than droping publication & recreating.

    Thanks
    Emamuthu
  6. MohammedU New Member

    What do you mean by "I tried to reset the replication status in sys.databases using sp_repldone"???

    sp_repldone Updates the record that identifies the last distributed transaction of the server. This stored procedure is executed at the Publisher on the publication database...

    Try running this in
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page