SQL Server Performance

transaction log full with mirroring (SP1+Hotfix)

Discussion in 'SQL Server 2005 Database Mirroring' started by carpi75, Dec 18, 2006.

  1. carpi75 New Member

    Hi,

    I have setup a database mirroring (High protection mode, without witness). After few days, my transaction log is filling up; there is no truncate of the log. Do you have any idea about the reason of this ?

    Regards;

    Stephane.
  2. satya Moderator

    Have you created any job for transaction log backup?
    Check ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/53e98134-e274-4dfd-8b72-0cc0fd5c800e.htm fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. carpi75 New Member

    No, just a simple backup of the entire database is done every day. Is it necessary to add a "BACKUP LOG" on my backup script to avoid this problem ?

    Regards,

    Stephane

  4. MohammedU New Member

    Database mirroring requires that the database use the full recovery model. You can#%92t use the simple or bulk-logged recovery models.

    So you have to create the log backup job and schedule it to run every 30/60 minutes.. otherwise your log will not be truncated...
    This will cause your disk to run outof space and you can't run any trasactions in the db....



    Mohammed U.
  5. satya Moderator

    Yes in order to workout you need to have log shipping enabled to take care of Tlog file size.

    quote:
    Database mirroring maintains a hot standby database (known as the mirror database) that can quickly assume client connections in the event of a principal database outage. Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database). Database mirroring applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.

Share This Page