none
.TRN file too big RRS feed

  • Question

  • Hi all- I have a question with regard to the transaction log file (.TRN) files: 

    Our database in in FULL Recovery mode.

    We have a maintenance plan that takes a) full backup daily at 2 am and deletes files older than 1 day b) transcational log backup every 15 mins and deletes files older than 2 days.

    But recently, the one of the .TRN files becomes so big (~12GB) that I receive warning that disk space is almost full. This happens once in every three days...

    There is no way that disk space could be increased. Wondering how to resolve the situation? 

    Does shrinking the .ldf file help reduce the .TRN file? Please advise.

     

    Thanks,

    Sudha

    Monday, October 17, 2011 1:40 AM

Answers

  • Yes Shrinking t-log will help you. As you mentioned every 3 days log is increased you have to check the transaction which are making log full.

    For example, if you have any Bulk Operation (e.g. Index Defrag, Bulk Insert .... ) You can change the recovery model during that time from full to Bulk-logged or Simple.

    Please note Shrink command will not help you much if it is waiting for log backup. You can query the sys.databases and check the column log_reuse_wait_desc and troubleshoot accordingly.

    I hope it will help you.

     

     


    Thanks, Jugal Shah http://sqldbpool.com/
    Monday, October 17, 2011 12:10 PM

All replies

  • Backup log will allow you to shrink it. Increase in your log possibly means you have a long transaction open, open transaction are not removed from the log file.

    You should check on those transactions if you have an idea when did they occur, and see if you can break them down to smaller pieces.

     

    MC

    Monday, October 17, 2011 4:58 AM
  • Hi all- I have a question with regard to the transaction log file (.TRN) files: 

    Our database in in FULL Recovery mode.

    We have a maintenance plan that takes a) full backup daily at 2 am and deletes files older than 1 day b) transcational log backup every 15 mins and deletes files older than 2 days.

    But recently, the one of the .TRN files becomes so big (~12GB) that I receive warning that disk space is almost full. This happens once in every three days...

    There is no way that disk space could be increased. Wondering how to resolve the situation? 

    Does shrinking the .ldf file help reduce the .TRN file? Please advise.

     

    Thanks,

    Sudha

    .trn (transaction log backup) would be huge if there are lots of logged operation performed. It backups up the amount of data since last log backup. If there are no operation performed in the database then backup size woudl be small, ir-respective of ldf size.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, October 17, 2011 5:39 AM
    Moderator
  • Yes Shrinking t-log will help you. As you mentioned every 3 days log is increased you have to check the transaction which are making log full.

    For example, if you have any Bulk Operation (e.g. Index Defrag, Bulk Insert .... ) You can change the recovery model during that time from full to Bulk-logged or Simple.

    Please note Shrink command will not help you much if it is waiting for log backup. You can query the sys.databases and check the column log_reuse_wait_desc and troubleshoot accordingly.

    I hope it will help you.

     

     


    Thanks, Jugal Shah http://sqldbpool.com/
    Monday, October 17, 2011 12:10 PM