SQL Server Performance

replication versus log file growth

Discussion in 'SQL Server 2005 Replication' started by awba, Nov 2, 2007.

  1. awba New Member

    Two weeks ago I created a merge replication between our live web server (A) and its backup device (B).
    Also a developer server (C) will regularily be updated by a snapshot replication from (B) - so (B) is the publisher.
    (Merge between (A) and (B) is required since transactions will have to be made on both sides)
    The transact log file has grown to 2 GB since then and it was impossible to shrink it. In the meantime I had to grant a maximum file size of 3 GB to keep the system running but of course I can't go on like this.
    Recovery mode is set to SIMPLE on all data bases envolved.
    I'm almost sure that deleting publications and the corresponding subscriptions would immediately solve this problem - but for the price of having a new one...
    MS online help gives lots of informations on replication as well as on shrinking the LDF file. Unfortunately I could not find any hints concerning the problem described above.
    Does anyone have an idea of what I could do?
  2. satya Moderator

    Welcome to the forum!
    Do you have free disk space issues, if not why not set a optimum size to transaction log file. By default log file is used for number of processes and transactions used within SQL database. Also ensure to perform intermittent (frequent) log backups to keepup the size.
  3. awba New Member

    I'm not sure if I get you right - with the recovery mode set to SIMPLE I can't execute BACKUP LOG.
    On the database as a whole I'm running a regular backup every 24 hours.
    Also what do you mean by "set an optimum size"?
    I suppose the ideal size should be far below 100 MB - there are no deeply nested transactions that have to be kept until commit or rollback on that db - at least as far as I can see. To me it seems as if SQL Server tries to keep every single transaction in its log file to ensure a general rollback "as if there had never been a replication" - could that be possible?
    And if so, how can I get rid of this?
  4. satya Moderator

    What is the reason behind keeping the database in SIMPLE recovery model?
    They should be in FULL recovery model for transaction logs backups (I should be clear this before), but in this case if the transactions are kept in smaller batches then your log file will not have problem, so as you can continue the log backups. In your case Merge replication may not affect the log size, but keepup the log file size.
    http://msdn2.microsoft.com/en-us/library/ms345414.aspx & http://msdn2.microsoft.com/en-us/library/ms190925.aspx fyi.
  5. awba New Member

    I've been playing around with it over the last couple of weeks. It seems as if the file growth was not caused by the merge replication but a snapshot publication I have on a different schema of the same database (I should have mentioned that).
    As soon as I create the publication -regardless if there are any subscribers or not- the LDF starts growing with anytransaction and can only be shrinked after removing the publication object. It looks as if MS had a different understanding of the word "snapshot" than I have. For me it would be perfectly what I need to have the publication object ready to create a snapshot any time I need the actual state of the database to update/overwrite the subscriber(s) - no matter how many or what kind of transactions might have been made in the meantime.
    Does anyone know how to set it up this way?
  6. ashwin.bajoria New Member

    I too am trying to set up a replication with the same situation. I see the LDF file grown to over 4 Gigs. I have to bring down the server and delete the files manually to get some space before restarting.
    I am in process of setting the recovery mode to SIMPLE and check the Auto Shrink checkbox in the properties box.

  7. ashwin.bajoria New Member

    Themagnitude of data the system logs depends on its verbosity setting. Simply put - moreverbose settings, means more overhead and vice-versa. Speaking in general - there are twoverbosity settings:
    HistoryVerboseLevel and OutputVerboseLevel. (But be sure to check the default values)

Share This Page