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 Monday, April 8, 2013 9:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
SQL Server 2005 SP4 with Transactional replication.

I have this Database Log, which keeps growing due to a Pending transaction in the Database Log. (DB in SIMPLE recovery Mode)

If i issue a DBCC OPENTRAN i get :

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (47839439:351:1)

The following Query using fn_dblog gives me LSN and Transaction ID Info.
select * from::fn_dblog(NULL,'47839439:351:1')

If i have to Shrink the Log i have to use sp_repldone to mark the transactions as replicated or skipped.

I take precautions in running this statement as this is not always what you want to do manually as it is internally done by the Log Reader Agent.

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

Now.. What if i Just want to mark just the transaction in question as done.. using the @xactid

How can i translate the CurrentLSN or TransactionID or LogRecord or Transaction SID or any other vlue to--> @xactid.....


Maninder
www.dbanation.com
Post #1439896
Posted Monday, April 8, 2013 11:37 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
Anyone..

Maninder
www.dbanation.com
Post #1439959
Posted Monday, April 8, 2013 2:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
MannySingh (4/8/2013)
Anyone..


Are you doing transaction log backups?


--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 #1440030
Posted Tuesday, April 9, 2013 7:00 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: 2 days ago @ 1:21 PM
Points: 809, Visits: 1,160
Check this url.. it may help.

http://blogs.msdn.com/b/repltalk/archive/2011/08/23/using-sp-repldone-to-skip-a-transaction.aspx
Post #1440299
Posted Tuesday, April 9, 2013 8:02 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
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..


Maninder
www.dbanation.com
Post #1440341
Posted Tuesday, April 9, 2013 8:03 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
dwivedi.neeraj (4/9/2013)
Check this url.. it may help.

http://blogs.msdn.com/b/repltalk/archive/2011/08/23/using-sp-repldone-to-skip-a-transaction.aspx


If you check my question.. I know about that command.. i am curious to now if the column values as mentioned can be converted to anything meaningful.


Maninder
www.dbanation.com
Post #1440342
Posted Tuesday, April 9, 2013 9:28 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: 2 days ago @ 1:21 PM
Points: 809, Visits: 1,160
Anything meaningful?

I though you are looking for something to "What if i Just want to mark just the transaction in question as done.. using the @xactid ", and the url which I mentioned mentions that if you have transaction id info, you can get xdesid for there.

Also use sp_repltrans to get xdesid & xact_seqno and match the xdesid from fn_dblog results.
Post #1440428
Posted Tuesday, April 9, 2013 12:00 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
yes i had gone through all of those blogs, but unfortunately none of those work.. the DBCC OPENTRAN is still showing the same info.
Does a SQL Service restart Help in this case?


Maninder
www.dbanation.com
Post #1440490
Posted Tuesday, April 9, 2013 1:23 PM
SSC Eights!

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

Group: General Forum Members
Last Login: 2 days ago @ 1:21 PM
Points: 809, Visits: 1,160
As you have mentioned that the recovery model is simple, so I believe yes. But I have never done that. But I have used sp_repldone and worked as expected. But before that I checked if the Log growth was really being caused by replication by running following command..

select log_reuse_wait,log_reuse_wait_desc from sys.databases

If there is something for replication then sp_repldone solves the issue.

Sorry I was not much help.

Post #1440521
Posted Wednesday, April 10, 2013 7:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, January 2, 2014 10:30 AM
Points: 646, Visits: 731
Yes it is the replication that is the issue.
even if i run sp_repldone, another transaction comes back, but i am not able to figure out any meaningful information from the fn_dblog like the AllocationUnit etc as they are all NULL... Or i cannot translate the LogRecord or Transaction SID to something meaningful.


Maninder
www.dbanation.com
Post #1440786
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse