SQL Server Performance

Abnormal log growth in primary database

Discussion in 'SQL Server 2005 Database Mirroring' started by amara, Apr 19, 2008.

  1. amara Member

    Hi,
    We have one of the databases on SQL Server 2005 Enterprise edition. Its around 500 GB and is configured for mirroring as well as log shipping.Mirroring is configured through SQL Server database properties where as log shipping is happening through a 3rd party tool by redgate called SQL Backup.There are jobs to restore the logs on the logshipping server.
    We have a challenge in this setup. The log file on the primary server is growing enormously high. We are not able to shrink the log file.We cannot truncate it as well, because log shipping is also in effect.
    When ever log grows high, we are breaking the mirroring and logshipping, truncating the log and shrinking, then restore the full backup, differentials and log backups on mirrored and logshipping servers and then reconfiguring them. Client is unhappy that we are breaking the mirror quiet frequently.
    Please help me out to find a solution to maintain the size of the log file on the primary database.
    amara, Apr 19, 2008
    #2

  2. satya Moderator

    You need to take into the processes such as REINDEX & inserts on the database that will have contribute the log sizes, also you need to perform the log shipping to 15 minutes interval in order to keepup the logical size.
    IN this csae you have to stop Mirroring & Log shipping to shrink the log size, then setup an optimum value to the log and in this case for 500gb database 10gb should be a starter here.
    satya, Apr 20, 2008
    #3

  3. amara Member

    Satya,
    Even now, logshipping is happening every 10 minutes...Could you please elaborate the solution?
    amara, May 8, 2008
    #4

  4. dba.mani@gmail.com New Member

    We have the same kind of environment with 1.5TB database. We have database mirroring, Logshippnig on primaryDatabase snapshot on the mirrored instance. It gives us no problems at all. All you have to do is be careful during the logged operations. Usually we do a index rebuild for big tables (more than 1billion) twice a month, and during that time, the db mirroring mode is changed to asychronous and restricted user mode. Log backups happen every 1 hr and logshipping restores every 1hr. FYI.. Principal and Mirror on EMC backbone with gigabit NICs.
    thks,
    Mani
    MCDBA, MCTS
    dba.mani@gmail.com, May 8, 2008
    #5

  5. satya Moderator

    What is the schedule of that REdgate's backup and log shipping job schedule?
    satya, May 9, 2008
    #6

  6. amara Member

    :Log backups thru redgate happens every 10 minutes..Log shipping job will restore the backups once in 2 hours...
    amara, May 15, 2008
    #7

  7. Ola Hallengren New Member

    >The log file on the primary server is growing enormously high
    How large is the log file growing?
    >Please help me out to find a solution to maintain the size of the log file on the primary database.
    What problems are the large log file causing you other than the disk space usage?
    Ola Hallengren
    http://ola.hallengren.com
    Ola Hallengren, May 15, 2008
    #8

  8. amara Member

    Space is the main constraint...
    amara, May 15, 2008
    #9

  9. Ola Hallengren New Member

    I think that when you have a database of that size then you'll need a lot of space for the log file. In my view you should be able to rebuild your largest index without running out of log space. I have a database of about the same size and my log file is about one third of the size of the data file. So I think that you need a larger disk system.
    What you could do to miminize the amount of log records that is generated and transfered to the log shipping and mirror servers, is to use sys.dm_db_index_physical_stats to rebuild / reorganize only the indexes with a high fragmentation.
    I have a stored procedure that could help you with this.
    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
    Ola Hallengren
    http://ola.hallengren.com
    Ola Hallengren, May 16, 2008
    #10

Share This Page