locked
How to auto reduce the size of LOG file. RRS feed

  • Question

  • I want to reduce my log file size but don't want to specified the reducible size, it automatically take the size as desired.

    Nitin Rawat

    Tuesday, August 16, 2016 5:44 AM

Answers

  • I want to reduce my log file size but don't want to specified the reducible size, it automatically take the size as desired.

    Nitin Rawat

    If I can understand your question you don't want manual intervention and Log file should manually reduce it self as and when required. This is bad idea, I must say very bad idea in long run. The reason is you know log file has to grow when transactions are performed as information about transaction is written on log file. Now when log file grows autogrowth(AG) event is triggered and until this event completes all processes "freeze" plus instant file initialization is not there for log files so when autogrowth happens it takes some time to AG to complete and this can be deciding factor on busy system. Now as per you you want to frequently shrink you log file so this AG event would be triggered frequently and this frequently "freezing" SQL Server process and making things slow.

    I suggest you provide necessary disk space on which your log file resides. Make sure auto growth is not in percentage and put appropriate value for it, shrinking of both data and log file is bad.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, August 16, 2016 6:41 AM

All replies