Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Transaction Log Growing due to Pending Transaction (Replication) Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 7:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 20, 2014 2:56 PM
Points: 835, Visits: 1,191
You have to use 3 commands in sequence
1) exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
2) Truncate the Transaction log
3)sp_replflush
Post #1440800
Posted Wednesday, April 10, 2013 10:55 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
if you see my question, i have already mentioned the step i do...

Maninder
www.dbanation.com
Post #1440944
Posted Tuesday, April 23, 2013 12:57 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
I have not found a solution as yet...
I am Just maintaining the Logs by using sp_repldone and then shrinkfile..
Any solutions...?
I see the 2 subscribers are continually running and these are the ones that open up the transaction(begin tran)
One more thing: This command "exec sp_executesql N'update MSreplication_subscriptions set transaction_timestamp " is running every second on the subscription database..


Maninder
www.dbanation.com
Post #1445648
Posted Tuesday, April 23, 2013 5:06 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
MannySingh (4/9/2013)
Jeff Moden (4/8/2013)
MannySingh (4/8/2013)
Anyone..


Are you doing transaction log backups?

if you check my question.. i have already mentioned the DB is in simple recovery mode, so Log backups are out of question..


My apologies. I missed that.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1445723
Posted Wednesday, April 24, 2013 7:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 5:15 PM
Points: 398, Visits: 571
The following select statement will scan fn_dblog and construct the sp_repldone statement for each individual command in the log that is marked for replication. It converts PreviousLSN and CurrentLSN into the format used by sp_repldone parameters @xactid and @xact_segno. It may be helpful in your situation:

select  'EXEC sp_repldone 
@xactid = 0x'+ left(cast([Previous LSN] as varchar(22)),8)+substring(cast([Previous LSN] as varchar(22)),10,8)+right(cast([Previous LSN] as varchar(22)),4)+
', @xact_segno = 0x'+left(cast([Current LSN] as varchar(22)),8)+substring(cast([Current LSN] as varchar(22)),10,8)+right(cast([Current LSN] as varchar(22)),4)+
', @numtrans = 0, @time = 0' as 'Commands'
from fn_dblog(null,null) where Operation ='LOP_COMMIT_XACT' and [Description] = 'REPLICATE'

Post #1445970
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse