Problem with Transaction Logs RRS feed

  • Question

  • Hi,


    I have a big problem with transaction log.  I need to reduce the transaction log file becouse, the have 25 GB, but when i run the statement for BACKUP LOG with TUNCATE_ONLY, dont work, becouse i have database mirroring installed.


    Please is very urgent.

    Friday, August 4, 2006 5:02 PM


  • That would be the disaster recovery and availability forum since this one is for strictly Database Mirroring questions.
    Wednesday, August 23, 2006 3:12 PM

All replies

  • What is sthe state of mirroring? Is it DISCONNECTED or SUSPENDED? In that case, the transaction log will continue to grow and you can't shrink / truncate.

    Moreover, TRUNCATE_ONLY is not recommended. It will be removed in a future version of SQL Server. See BOL.

    Monday, August 7, 2006 4:07 PM
  • Hi Sanjay,

    The state of mirroring is syncronized.  But in the state (mirroring active), how i cant reduce the log file?

    Monday, August 7, 2006 4:52 PM
  • You have to backup the transaction log.  You can not issue a BACKUP LOG...WITH TRUNCATE_ONLY as this command is not compatible with Database Mirroring and as such is not allowed.

    I am concerned about your environment.  Just because you have Database Mirroring enabled does not mean that you shouldn't be doing regular backups.  Those backups should include transaction log backups.  Then all you would to do is to shrink the tran log file.  If you aren't doing transaction log backups, then you need to implement them.

    Monday, August 7, 2006 8:11 PM
  • Hi Michael,

    My environment is this,

    I have a one Database with 3GB of Data.  This Database is for management the purches orders of the goverment, so in this case, if the database is down, i need another in standby.  But, the transaction log gowth to much in houres (25GB).  When i do the shrinkfile only down a 1GB.


    Tuesday, August 8, 2006 3:29 PM
  • Your transaction log is divided into boundaries called virtual log files.  Inside the tran log, there is a pointer that keeps track of where it is currently writing.  When the log pointer reaches a VLF boundary, it can wrap around to the beginning of the file and start reusing space.  When you run a shrink, it starts at the end of the file and removes space until it reaches a page in the log that is still in use, then it stops.  So, basically, keep running your transaction log backups and then once a day issue a shrink to the tran log.  The log backups will eventually clean out the transactions at the head of the file while the log pointer is at the head of the file and the shrink will then remove all of the space that was allocated.
    Tuesday, August 8, 2006 3:58 PM
  • Hi Guillermo,

    If your mirroirng state is synchronized, you will keep the transaction log under control by periodically backing up the log.

    How frequently do you backup the log?

    Looks like you generate 25 GB of log per day. If you would like to keep the transaction log size under check, you would probably like to back the log more frequently.


    Tuesday, August 8, 2006 8:50 PM
  • Thanks for your reply.  I understand what say.  But what if....If the transactions never complete then does the log just continue to grow until the server runs out of space or until the database is deleted? 

    We run an application that updates the database after moving files onto the server from a deep archive.  These server services performing the work have memory leaks that eventually crash an ATM ELAN driver.  The files are copied but the database never updates with the new online path.  I think but I am not sure that these are uncommited transactions. When a full database backup is performed the size of the log never decreases.  I'm not sure but maybe if I "shrink" the log file it would delete empty vitual logs.  I'm am by no means a DBA.  The server must be rebooted to bring back the functionality of the ATM NIC.

    The Data Base is only 300mb and the log is 560mb.  Only a few hundred transactions a day.  When I ran a "Optimization" job via "Database Maintenance Plan"  the log grew.




    Monday, August 21, 2006 3:17 PM
  • Split post from original and moved to appropriate forum.
    Monday, August 21, 2006 11:26 PM
  • what forum may that be?

    I ran the shrinkfile command in simple recovery mode

    the log went from 561 to 5 MB




    Tuesday, August 22, 2006 2:49 PM
  • That would be the disaster recovery and availability forum since this one is for strictly Database Mirroring questions.
    Wednesday, August 23, 2006 3:12 PM
  •  heywade wrote:

    what forum may that be?

    I ran the shrinkfile command in simple recovery mode

    the log went from 561 to 5 MB



    Hi all,

    I got similar DB enviorment like heywade did. I also got similar problem as he described.

    The size of log is still very very large. It seems to affect the DB performance. 

    So, I also tried to shrink the log and db after a full backup but no luck. Even I stopped the mirror but still didn't work. I dont know why ...

    The only thing I can do is

    1) Stop mirroring

    2) Full backup

    3) Detach the DB

    4) Delete the large log (usually I rename it first for safety)

    5) Attach the DB (of course, not with the large old one, create a new instead.)

    6) re-do the mirroring setup steps.

    How terrible!

    BTW, I wonder how can heywade run shrinkfile comand in 'simple recovery mode' since 'simple recovery mode' is not allowed when mirroring.

    Any advice, please? Thank you very much.


    Monday, September 25, 2006 8:15 PM
  • Oh I also got this big problem, and I amazing that no MS SQL supporter answer this question clear. Perhaps this has been explained scattered in Ms SQL help.

    There is 2 way:

    1. Take the SQL server model to: simple and schedule 1 daily database backup

    2. If the model is: full recovery: must schedule two job after check on: auto shrink database option

    a./ Backup transaction log file

    b./ Backup database file

    And after that you have just enjoy the working of SQL auto shrink database, It will also auto shrink your transaction log file. The important things is you must take the job of backup your transaction log file.

    Friday, October 6, 2006 5:09 PM