locked
How can I reduce VLF's from log file? RRS feed

  • Question

  • I want to reduce the VLF's from my log file. As currently 9711 VLF's are existing in the database.How can I reduce it?

    while ideally it should 100 VLF's.

    DB Size=230 GB


    Thanks

    • Moved by ErikEJMVP Tuesday, April 1, 2014 7:02 AM wrog forum
    Tuesday, April 1, 2014 5:16 AM

Answers

All replies

  • You need to take LOG BACKUP regularly for databases with FULL recovery model to manage the LOG growth.

    Refer the below link:

    http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/

    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:22 AM
    Tuesday, April 1, 2014 5:26 AM
  • You need to configure T logs for the database and later shrink the logfile

    May refer below articles

    http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/

    http://sqlaspire.blogspot.in/2013/03/reducing-vlf-count-for-sql-server.html


    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, April 1, 2014 6:04 AM
  • I want to reduce the VLF's from my log file. As currently 9711 VLF's are existing in the database.How can I reduce it?

    while ideally it should 100 VLF's.

    DB Size=230 GB


    Thanks

    Hello,

    VLF can be controlled by taking log backup but IMO what will control so many VLF is Autogrowth setting for the database.Please check your autogrowth setting it should be in MB and also check you have not specified smaller value for autogrowth.

    You can take log backup and shrink log files to reduce VLF but if Autogrowth setting is not proper it will again increase the VLF count.Just assume you have kept 50 MB autogrowth now your datafile needs to grow 200 MB so approximately 4 VLF would be required.Now if you would have set it to 200 MB just one VLF would be required.Hope this makes little sense.This was just to explain you.More about autogrowth can be read from below link

    https://www.simple-talk.com/sql/database-administration/sql-server-database-growth-and-autogrowth-settings/

    Below article by Kimberly will also be helpful

    http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621MVP Tuesday, April 1, 2014 8:37 AM
    • Proposed as answer by Sofiya Li Wednesday, April 2, 2014 8:01 AM
    • Marked as answer by Sofiya Li Tuesday, April 8, 2014 2:22 AM
    Tuesday, April 1, 2014 8:36 AM
  • Shrink it to a very very small size, then expand it using only one or a few increments (grow operations) to the desired size.

    Tibor Karaszi, SQL Server MVP | web | blog

    Tuesday, April 1, 2014 10:40 AM