Help!About transaction log truncation

  • According to "Sql Server 2005 Books Online", Transaction log will be truncated in two situations below:

    1.Database is in Simple Recovery Mode && each Checkpoint

    2.Back up log

    But the problem is:

    My database is in Full Recovery Mode,and i did not do any log backup.When i do a lot of trans , i can see that log file's size grows up.But the log file's unusage grows up too.It seems that transaction log truncation happened!

    I need to know why!

  • Its not the transaction log truncation when you do any transactions they first go to log file and hence its size grows when a particular transaction commits it frees the space in log file.

    If you wish to reduce the size of your LOG file back to its original default size try this :

    Backup log MYDATABASE with truncate_only

    DBCC Shrinkfile ('Log',10)

    you can change 10 to required value.

  • If you don't need to do transaction log backups, change the database recovery mode to SIMPLE. Otherwise, start taking regular transaction log backups and your transaction log won't grow wild.

    Don't resort to shrinking files to fix problems like this. Figure out what your backup and recovery requirements are and then implement an appropriate strategy.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply