June 20, 2009 at 8:22 pm
that's right.
run that command, backup log and let us know the result.
June 20, 2009 at 8:33 pm
ps (6/20/2009)
that's right.run that command, backup log and let us know the result.
Thanks Pradeep , doing this now. Will post back the result.
June 20, 2009 at 8:46 pm
That worked! Yay, thanks heaps guys 😎
Is there anything I need to re-do after running that command?
June 20, 2009 at 8:52 pm
JamesNZ (6/20/2009)
That worked! Yay, thanks heaps guys 😎Is there anything I need to re-do after running that command?
Glad you got it running after so long :-P.
1. You need to re-setup replication(after removing current setup) if you need to.
2. For one time you might want to shrink you log file so that u have ample free space on ur server. If space is not a constraint, you might not want to do this.
June 20, 2009 at 9:32 pm
The replication seems to be working just fine. I've just tested it and don't see a problem. Is there something I'm missing? Do I really have to re-set it up again?
June 20, 2009 at 9:57 pm
JamesNZ (6/20/2009)
The replication seems to be working just fine. I've just tested it and don't see a problem. Is there something I'm missing? Do I really have to re-set it up again?
Make a transaction on the primary and see if it's replicated successfully on secondary. If it gets replicated, everything is working fine.
Also check for sys.databases if log_reuse_wait_desc is not replication.
June 20, 2009 at 10:01 pm
ps (6/20/2009)
JamesNZ (6/20/2009)
The replication seems to be working just fine. I've just tested it and don't see a problem. Is there something I'm missing? Do I really have to re-set it up again?Make a transaction on the primary and see if it's replicated successfully on secondary. If it gets replicated, everything is working fine.
Also check for sys.databases if log_reuse_wait_desc is not replication.
log_reuse_wait_desc now shows a status of "NOTHING". Is that ok?
Yes, I tested a transaction on the primary and it did get replicated across successfully.
June 20, 2009 at 10:12 pm
That's right. If you get NOTHING in that column, nothing is preventing log file from getting truncated. Everything is running fine including your replication.
Also check if your distribution db log size. Sometimes when the distribution db's log is full, it's unable to bring commands from publisher because of which publisher's log keeps growing, preventing user from truncating the log. (however i doubt this is your case)
June 20, 2009 at 10:16 pm
ps (6/20/2009)
That's right. If you get NOTHING in that column, nothing is preventing log file from getting truncated. Everything is running fine including your replication.Also check if your distribution db log size. Sometimes when the distribution db's log is full, it's unable to bring commands from publisher because of which publisher's log keeps growing, preventing user from truncating the log. (however i doubt this is your case)
Great - thanks 🙂
I checked that as well actually, and it was a-ok.
Thanks a lot for your help guys, it was really appreciated in getting this fixed.
June 21, 2009 at 3:01 am
If I'm understanding you correctly, this is not completely fixed. You're running only snapshot replication. Snapshot replication DOES NOT use transaction log. Hence the reference to replication in log_reuse and the reference to distributed transactions in OPENTRAN indicates that there's a half-setup transactional replication config.
Running repldone will not affect snapshot replication. It only affects transactional replication. If there's a partially setup transactional publication, running that's a temporary fix only.
Can you please check again in a few hours, log_reuse_wait_descr and DBCC opentran. If Opentran references replicated LSNs or log_reuse indicates replication, please post the results of opentran and I'll walk you through fixing this permanently.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2009 at 3:16 am
Gail, I think he is running Transaction replication coz i asked him to make a transaction and it replicated to subscriber in almost no time. Also he told that his replication is running fine (in earlier posts) which means log reader was able to retrieve details from publisher and storing in distribution db, however it was not firing sp_repldone. What could be probable reasons(if i'm going in the right direction)?
Also if transactions were not moving to distribution db, his replication must have failed when he manually fired sp_repldone but it didnt.
To me, it seems problem lies in some of the job steps where log reader was unable to execute sp_repldone. But in the last step he again checked sys.databases after making a transaction and log_reuse_wait_desc showed NOTHING. does this mean that sp_repldone was fired by log reader agent this time?
I'm little perplexed here..
June 21, 2009 at 3:43 am
ps,
That was my general understanding too.
(It also left me slightly confused - but I guess he'll be back if/when problems resurface)
Well done everyone for getting James sorted.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 21, 2009 at 3:47 am
Yes, very uncommon situation. waiting for Gail for her thoughts on this, on the probability of error chances :-).
June 21, 2009 at 4:35 am
Sorry for the confusion guys, certainly not meant to cause it 🙂
Is there any screen-prints I can take to help clear it up?
June 21, 2009 at 4:43 am
ps (6/21/2009)
Gail, I think he is running Transaction replication coz i asked him to make a transaction and it replicated to subscriber in almost no time.
Check the output of syspublications that he posted earlier. Snapshot replication.
Also the output of OPENTRAN indicates that the last replicated transaction was LSN 0, which mean that no transaction has ever been moved to the distributor.
Easy way to check. James, can you take a screen shot of Object explorer with the replication folder expanded. Also with the job folder expanded?
What could be probable reasons(if i'm going in the right direction)?
Also if transactions were not moving to distribution db, his replication must have failed when he manually fired sp_repldone but it didnt.
Which tells me that he's not running transactional replication at all. If he had been, either he would not have been able to run sp_repldone (because the log reader's connected and hence no one else could run sp_repldone) or it would have completely broken replication.
But in the last step he again checked sys.databases after making a transaction and log_reuse_wait_desc showed NOTHING. does this mean that sp_repldone was fired by log reader agent this time?
No, it's because you had him manually run it which told SQL that all the transactions should be considered replicated. Hence they're not preventing log truncation at the moment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 46 through 60 (of 66 total)
You must be logged in to reply to this topic. Login to reply