April 8, 2013 at 9:11 am
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
April 8, 2013 at 11:37 am
Anyone..
Maninder
www.dbanation.com
April 8, 2013 at 2:56 pm
MannySingh (4/8/2013)
Anyone..
Are you doing transaction log backups?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2013 at 7:00 am
Check this url.. it may help.
http://blogs.msdn.com/b/repltalk/archive/2011/08/23/using-sp-repldone-to-skip-a-transaction.aspx
April 9, 2013 at 8:02 am
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
April 9, 2013 at 8:03 am
dwivedi.neeraj (4/9/2013)
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
April 9, 2013 at 9:28 am
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.
April 9, 2013 at 12:00 pm
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
April 9, 2013 at 1:23 pm
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.
April 10, 2013 at 7:01 am
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
April 10, 2013 at 7:26 am
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
April 10, 2013 at 10:55 am
if you see my question, i have already mentioned the step i do...
Maninder
www.dbanation.com
April 23, 2013 at 12:57 pm
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
April 23, 2013 at 5:06 pm
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
Change is inevitable... Change for the better is not.
April 24, 2013 at 7:40 am
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'
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply