SQL Server Performance Forum – Threads Archive
Snapshot replication
HiPeriodically 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
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
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
Merge and Snapshot replications are not tlog depedent so log should not effect.
No idea what cuased this…
MohammedU.
Moderator
SQL-Server-Performance.com
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
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
]]>