transaction log growing

  • pols

    SSC Eights!

    Points: 861

    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..

  • c00ler01

    Ten Centuries

    Points: 1070

    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

  • GRE (Gethyn Ellis)

    SSCrazy Eights

    Points: 9582

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • pols

    SSC Eights!

    Points: 861

    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

  • Gail Shaw

    SSC Guru

    Points: 1004484

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Johan Bijnens

    SSC Guru

    Points: 134286

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • pols

    SSC Eights!

    Points: 861

    I am M getting as 'No active transactions'....:(

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply