locked
How to LDF file in control.. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    hi ,

    One of the Database in our environment LDF file is increase every day 5 GB.. I had to do some steps--because the  Database is full recovery mode ..I was taken the Log trn backup.. 2-3 time  the after that Shrink the file ..I want the proper solutions for this issues. Please help.

    ajitkumar

    Monday, January 27, 2020 9:53 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote
    Run frequently log backup jobs, that releases the VLF = Virtual Log Files so that they can be reused.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, January 27, 2020 10:10 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Schedule Log backup frequently or else if business don't point in time recovery you can change the recovery model to simple.   

    Please mark me as answer if my post helps you .

    Br 

    ChetanV

    Monday, January 27, 2020 10:33 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hello Friend ,

    The .ldf file grows according to the number of transactions (insert - update - delete) that are executed in the environments.
    If the file is growing beyond what you want or need, change the frequency of your tlog backup jobs.

    Friend, now here's a tip.
    Try to make Tlog backups to the point that your .ldf files are stable in size. Do not make Shrinks if possible. Shrink burdens your database and leads to negative impacts if done frequently.

    If this answer helped you, mark it as helpful so that someone with a question or similar problem can find an answer or help more easily. * Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Monday, January 27, 2020 7:40 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ajitkumar,

    If your business does not support loss of data or requires having point in time recovery, you need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits. If you are taking an hourly T-Log backup, your T-Log would grow until one hour but after this the T-Log backup would truncate all the ‘committed’ transactions once you take it. Doing this would lead the size of the T-Log not to go down much, but it would rather be marked as empty for the next hour’s T-Log to populate. 

    If you do not require to have point in time recovery. You change your recovery model to Simple Recovery Model. This way, you will not have extra ordinary growth of your log file.

    Please refer to SQL SERVER – How to Stop Growing Log File Too Big to get more information.

    If you want to reduce the physical size of a physical log file, you must shrink the log file. This is useful when you know that a transaction log file contains unused space. 

    Please refer to Manage the size of the transaction log file.


    Best regards, 
    Cathy

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Tuesday, January 28, 2020 6:51 AM