locked
How to reduce the virtual log(if Database has too many Virtual logfiles) RRS feed

  • Question

  • Hey all,

    In Database mirrorng  how to reduce the number of file ?..
    Did if Pricipal database has too many vlogs..will it create any issue.

    How to reduce the vlogs from database..

    Please some body help on this.

    Thanks in advance

    Friday, September 21, 2012 9:49 AM

Answers

  • Hi Mywish_SQL,

    Ideally, transaction log files should be created at their desired size at installation rather than being allowed to grow slowly over time.

    SQL Server should not have an excessive number of Virtual Log Files (VLFs) inside the Transaction Log.  Having a large number of small VLFs can slow down the recovery process that a database goes through on startup or after restoring a backup.

    One can determine the number of VLFs in a particular database by seeing how many records are returned as a result of this DBCC command executed from within the context of the database: DBCC LOGINFO.

    If you have very large numbers of VLFs you will still need to correct the situation.

    To correct the situation and prevent its recurrence:
     1. Backup the Transaction Log of the database.
     2. Shrink the Transaction Log as much as possible using DBCC SHRINKFILE.
         Ideally do this in a maintenance window
         Multiple SHRINKFILE executions might be called for to substantially shrink it.
     3. Expand the Transaction Log back to its target initial size.
     4. Set the Transaction Log growth size to be in units of 1 GB.
     5. Apply the latest CUs for SQL Server.

    For more information, please refer to:
    http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx.
    http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Ramesh Babu Vavilla Monday, September 24, 2012 2:37 AM
    • Marked as answer by Maggie Luo Thursday, September 27, 2012 4:24 PM
    Saturday, September 22, 2012 5:16 PM

All replies

  • Vlog as name inplies are virtually inside your t-log file.

    You can shrink the t-log file to reduce the no. of vlfs.

    Shrinking a t-log file may create a performance issue.

    Why you are concerning about vlfs? SQL server manage this itself internallly on autogrow, manuall grow or shrink.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answerand Vote as Helpfulon posts that help you. This can be beneficial to other community members reading the thread.


    Friday, September 21, 2012 9:37 PM
  • In order to minimize VLF you need to allocate enough space on the creation. I mean  if you need a 2GB log then just create that as one step create log file with 2GB size , IIRW SQL Server will allocate 16 VLF.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Saturday, September 22, 2012 6:50 AM
  • Hi Mywish_SQL,

    Ideally, transaction log files should be created at their desired size at installation rather than being allowed to grow slowly over time.

    SQL Server should not have an excessive number of Virtual Log Files (VLFs) inside the Transaction Log.  Having a large number of small VLFs can slow down the recovery process that a database goes through on startup or after restoring a backup.

    One can determine the number of VLFs in a particular database by seeing how many records are returned as a result of this DBCC command executed from within the context of the database: DBCC LOGINFO.

    If you have very large numbers of VLFs you will still need to correct the situation.

    To correct the situation and prevent its recurrence:
     1. Backup the Transaction Log of the database.
     2. Shrink the Transaction Log as much as possible using DBCC SHRINKFILE.
         Ideally do this in a maintenance window
         Multiple SHRINKFILE executions might be called for to substantially shrink it.
     3. Expand the Transaction Log back to its target initial size.
     4. Set the Transaction Log growth size to be in units of 1 GB.
     5. Apply the latest CUs for SQL Server.

    For more information, please refer to:
    http://blogs.msdn.com/b/saponsqlserver/archive/2012/02/22/too-many-virtual-log-files-vlfs-can-cause-slow-database-recovery.aspx.
    http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Ramesh Babu Vavilla Monday, September 24, 2012 2:37 AM
    • Marked as answer by Maggie Luo Thursday, September 27, 2012 4:24 PM
    Saturday, September 22, 2012 5:16 PM