Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Number of Virtual Log files

    we are using sql server 2008 r2 standard with sp2
    I have a 12 GB database in production server, log file was set to 150MB with increment of 10 percent, in last 4 years database size has gone from 2gb to 12 gb


    i ran following command

    DBCC LOGINFO

    and found i have 150 plus rows(which means 150 plus virtual log files)

    i increased the size of log file to 25 percent of data file which comes to be approx 3gb and also set auto growth to 20 percent

    addtional info: we have a log shipping environment in production, i am taking log backups every 15 mins.....


    still number of virtual log files is same , why is that , how to bring them between number of 25-50 as thats the recomended
    Last edited by amitwadhawan123; 11-08-12 at 12:19.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The only way to eliminate virtual log files is to shrink the log file (see DBCC SHRINKFILE), then set the log file to the required size. Since you are in FULL recovery mode, this may take several attempts, as the log file will not shrink below the current high water mark in the file (last active virtual log file).

  3. #3
    Join Date
    Aug 2009
    Posts
    73
    Update::
    now the t-log backup goes for logshipping it reduced the log file size to 12 MB even though i set to 3gb, how much percentage increase should be done?? how to handle this during batch processes which occur once a month and affect 1 million rows on an avg?

    now number of Vlog files is 42 ....??
    Last edited by amitwadhawan123; 11-08-12 at 12:24.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Virtual log files are only allocated when the log file is created, or when it is expanded. They are not created dynamically based on the load.

  5. #5
    Join Date
    Aug 2009
    Posts
    73
    sir i Agree, So when batch update process runs in our environment , then log file size increases ,but does that mean

    "number of virtual log files will be less if i have set auto increment to say 50% of current log size (rather than 10%)"

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There will be fewer virtual log files, if you create the log file at the appropriate size (3GB, it looks). When a physical log file grows, new virtual log files are appended on the end. None of the old ones are reallocated. In short, auto growth is bad.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •