SQL Server Performance Forum – Threads Archive
transaction log full with mirroring (SP1+Hotfix)
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.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.
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
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.
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.
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.
]]>