Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Transaction Replication- Two log files

Answered Transaction Replication- Two log files

  • Friday, December 07, 2012 6:51 PM
     
     
    Sign In to Vote
    0
    For some reason we have two log files on one of our production database. We have transactional replication setup on it. We are experiencing a latency issue during index rebuild/heavy inserts/ heavy deletes  operation. I think it's expected. My question is as there will be a increase in the log file size during the above operations, both the logs are growing at the same rate. Not sure why. As log files are written sequentially why both the log files are growing at the same rate?  Does the log reader agent tries to search for replicated transactions in both the log files?
     

All Replies

  • Sunday, December 09, 2012 12:52 AM
    Moderator
     
     
    Sign In to Vote
    0
    My question is as there will be a increase in the log file size during the above operations, both the logs are growing at the same rate. Not sure why. As log files are written sequentially why both the log files are growing at the same rate?
    The log files are growing at the same rate because the auto-growth settings.  The engine writes log records sequentially but what you are seeing is the log files become full, and not resusable.  The engine will then grow one log file, fill the space, then grows the other and fills that space, and so on.
      Does the log reader agent tries to search for replicated transactions in both the log files?
    Yes, the Log Reader Agent will search for replicated transactions in both log files.

    Brandon Williams (blog | linkedin)

     
  • Tuesday, December 11, 2012 2:26 PM
     
     
    Sign In to Vote
    0

    Both the logs are set to restricted growth  to 2097152 MB and both in the same drive. Currently the log file sizes is 149  GB and the free space in both the log files is 148 GB. The log sizes are growing continuosly because of the rebuild and delete operations and both the files used to be 60 GB which increased to 150 GB in 8 months.  Why the both log sizes are growing when a log file is set to grow to 2 TB?

     
  • Wednesday, December 12, 2012 2:16 AM
    Moderator
     
     
    Sign In to Vote
    0

    The restricted growth 2097152 MB is your MAXSIZE setting.  The log files will not exceed this size.

    What you need to examine is your FILEGROWTH growth_increment setting.  This will explain the growth.

    Brandon Williams (blog | linkedin)

     
  • Wednesday, December 12, 2012 12:53 PM
     
     
    Sign In to Vote
    0

    Autogrowth is set to 50 MB for both the log files.

     
  • Wednesday, December 12, 2012 1:28 PM
    Moderator
     
     
    Sign In to Vote
    0
    Try to empty one of the log files into the other. This may take many attempts to do and should be done during a time of low activity. Each time you try it limit the amount the size the file can grow. At some point you should be able to remove the second log file.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

     
  • Wednesday, December 12, 2012 2:31 PM
     
     
    Sign In to Vote
    0

    Thanks Hillary.

    We also have log shipping and replication setup on this database. Currently each of the log files size is just 802 MB, rest almost 250 GB free space. When I checked the dbcc loginfo just one vlf related to primary log file is in use. So I think we can just directly shrink the second log file and reduce it to minimum size and delete it if no vlf's are active in it.

     
  • Wednesday, December 12, 2012 5:38 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    You will want to alternate backing up the log and shrinking the log, checking the virtual log file layout using dbcc loginfo to see if the file is still in use after each step.  Once the file is no longer in use you can issue an ALTER DATABASE REMOVE FILE operation to remove the log file.

    Afterwards, check sys.database_files to verify.  You may find SQL Server is reporting the file as OFFLINE.  If so, backup the log again and it should be gone from sys.database_files.

    Brandon Williams (blog | linkedin)

     
  • Sunday, December 16, 2012 2:12 AM
     
     
    Sign In to Vote
    0

    Hi,

    see below links:

    http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

    http://rusanu.com/2012/07/27/how-to-shrink-the-sql-server-log/

    Thanks Shiven:) If Answer is Helpful, Please Vote