December 31, 2009 at 4:02 am
Hi,
I configured peer to peer replication.
In replication monitor i found the error msg that 'transaction log for respcetive database is full'
But the recovery model for the db is 'Simple'
I am not able to understand why the t-log is keep on increasing.
If i execute this below command i found that log_reuse_wait_desc option is marked as 'Replication' for perticular databse.
SELECT [name], [log_reuse_wait], [log_reuse_wait_desc]FROM sys.databases
To free the space i hv executed below command.(sucessfully the counter :log spaceused% ' is reduced.But after some time again the its keep on increasing..)
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
Please give me the solution to resolve this issue..
Thanks..
December 31, 2009 at 4:51 am
Simple recovery model implies that you are not taking the backup of the log file. If log file growth is set to some %, for example 10%, it means that once it hits 1Gb it will grow stupidly fast.
Take the backup of the log file and run the DBCC to shrink the log file with Concat_Only option. This should do the job.
Change recovery mode to: Full
January 1, 2010 at 5:57 pm
I don't think you can take a log backup in simple mode...Cooler might be right about Full recovery though.
I'm no expert on peer to peer replication but I would suggest the problem maybe to do with the 'sync with backup' option on the distribution database, with this option set SQL Server will not truncate the log of the publication database until all transactions delivered to the distribution database are backed up.
with a DB in Simple mode these will only be backed up when a Full or Diff backups occur. I'm guessing you take a Full backup regulary, perhaps daily but nothing throughout the day
As a start you could follow Coolers advice and put the DB in Full recovery and ensure you take regular log backup to see if that helps.
Gethyn Elliswww.gethynellis.com
January 2, 2010 at 2:22 am
In simple recovery the log is auto-truncated on checkpoints and transaction log backups cannot be run. C00ler, why are you recommending a switch to full recovery?
Pols, is the replication working? Are transactions being replicated around? Is the log reader agent running?
What does DBCC OPENTRAN say?
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
January 6, 2010 at 3:09 am
Gail Shaw,
I have executed dbcc opentran.
their is no open transactions .
And after configuing peer-peer topology,In replication monitor Publication is running in poor condition. And for other server its not atall running.(getting transaction log full)Means subscriber is not wrking.
I dont understand while configuration i didnt got any error..But after some time y its giving tht error?
{For your info, Previously i have deleted replication from servers once.. then again configued correctly .. }
Thanks
pols
January 6, 2010 at 3:21 am
Then you need to investigate why the replication isn't working. That's what's causing the log to grow.
Does DBCC OPENTRAN say anything about replication?
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
January 6, 2010 at 3:21 am
Is your target transaction log big enough to be able to process all incoming replication commands ?
- file size
- autogrow enabled ? ( grow by reasonable MB !)
- can your drive which hosts the log file, cope with the expand requests ? (SQLServer will only wait for a certain timeout for the file extend to be processed. If the timeout is reached, it will raise a "log file full" condition)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 6, 2010 at 4:09 am
I am M getting as 'No active transactions'....:(
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply