Post reply

Resizing the log file

  • March 6, 2012 at 10:35 am

    #265546

    DB - SQL SERVER 2008 R2

    The size of the log file is 24G and the database is internally fragmented. The total number of the VLFs is more than 300. I have the t-log back up running in every 15 minutes.

    I log the size of the log file in a table in every 4 hours and I do not see anywhere it's growing more than 40MB. I am going to resize the log file (shrikfile) to 1GB.

    Is it okay t resize to 1G?

    Thanks

  • March 6, 2012 at 12:44 pm

    #1455519

    its obviously grown that large for a reason i would have thought, sure its not a weekly reindex job or something pushing the size up. Be mindful about shrinking it as it may well require space, monitor it for a while and check the usage trend then make a decision

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 7, 2012 at 6:54 am

    #1455961

    Guras (3/6/2012)

    DB - SQL SERVER 2008 R2

    The size of the log file is 24G and the database is internally fragmented. The total number of the VLFs is more than 300. I have the t-log back up running in every 15 minutes.

    I log the size of the log file in a table in every 4 hours and I do not see anywhere it's growing more than 40MB. I am going to resize the log file (shrikfile) to 1GB.

    Is it okay t resize to 1G?

    Thanks

    When you say you never see it growing more than 40MB what exactly do you mean? Do you mean you never see space used growing by more than 40MB or you see the log file grow by 40MB? If the second then you need a 24GB log file, if the first then you can probably shrink it. I'd follow the advice Kimberly Tripp gives in these 2 blog posts:

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • March 7, 2012 at 7:02 am

    #1455966

    Jack Corbett (3/7/2012)

    Guras (3/6/2012)

    DB - SQL SERVER 2008 R2

    The size of the log file is 24G and the database is internally fragmented. The total number of the VLFs is more than 300. I have the t-log back up running in every 15 minutes.

    I log the size of the log file in a table in every 4 hours and I do not see anywhere it's growing more than 40MB. I am going to resize the log file (shrikfile) to 1GB.

    Is it okay t resize to 1G?

    Thanks

    When you say you never see it growing more than 40MB what exactly do you mean? Do you mean you never see space used growing by more than 40MB or you see the log file grow by 40MB? If the second then you need a 24GB log file, if the first then you can probably shrink it. I'd follow the advice Kimberly Tripp gives in these 2 blog posts:

    I meant the size of the log file used space growing more than 40 MB and I take T-log back up in every 15 minutes.

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply