Log initial size

  • Hi,

    I have set my db log intitial size as 512 MB and autogrowth as 512 MB. The db is in full recovery model and tlog runs every 15 min. Noticed, the very next day log size increased to almost 3 Gb. And my VLF's are almost 50 with only one row as status 2.

    I know shring the log reduce intitial size. But shrink is not good practise.

    Is there anyway to maintain fixed initial size. May I know the reason for Initial log size growth? If i ignore cant except how long initial log size grows.

    Thanks in advance.

  • gorripativ (2/26/2016)


    Hi,

    I have set my db log intitial size as 512 MB and autogrowth as 512 MB. The db is in full recovery model and tlog runs every 15 min. Noticed, the very next day log size increased to almost 3 Gb. And my VLF's are almost 50 with only one row as status 2.

    I know shring the log reduce intitial size. But shrink is not good practise.

    Is there anyway to maintain fixed initial size. May I know the reason for Initial log size growth? If i ignore cant except how long initial log size grows.

    Thanks in advance.

    1) in full recovery mode your tlog will grow indefinitely unless you do a tlog backup. Full backups will NOT flush committed records from the tlog. This is a VERY common misconception and misconfiguration in the wild.

    2) You should size your tlog so that it does NOT need to grow at all during any normally-expected evolutions between your tlog backup timing.

    3) To size appropriately, search for Kimberly Tripp's blog posts on sizing tlog and efficient tlog configuration.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • [/quote] Hi,

    In the question clearly I have mentioned we have tlog backups for every 15 min. Even if I take tlog backups for every 15 min why my initial size is growing is my concern?

    1) in full recovery mode your tlog will grow indefinitely unless you do a tlog backup. Full backups will NOT flush committed records from the tlog. This is a VERY common misconception and misconfiguration in the wild.

    2) You should size your tlog so that it does NOT need to grow at all during any normally-expected evolutions between your tlog backup timing.

    3) To size appropriately, search for Kimberly Tripp's blog posts on sizing tlog and efficient tlog configuration.[/quote]

  • gorripativ (2/26/2016)


    Even if I take tlog backups for every 15 min why my initial size is growing is my concern?

    What is returned when you run this query in the database where this happens:

    SELECT log_reuse_wait_desc

    FROM sys.databases

    WHERE database_id = DB_ID();


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • If your log has stabilized at 3GB - and was grown out in 512MB chunks - I don't see any problems here that need to be addressed. You have a reasonably sized transaction log with a reasonably sized growth setting.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • gorripativ (2/26/2016)


    Hi,

    I have set my db log intitial size as 512 MB and autogrowth as 512 MB. The db is in full recovery model and tlog runs every 15 min. Noticed, the very next day log size increased to almost 3 Gb. And my VLF's are almost 50 with only one row as status 2.

    I know shring the log reduce intitial size. But shrink is not good practise.

    Is there anyway to maintain fixed initial size. May I know the reason for Initial log size growth? If i ignore cant except how long initial log size grows.

    Thanks in advance.

    512MB was clearly not large enough. if it's stabilised at 3GB then when you can get exclusive access to the database shrink the log to 10MB, then grow it to 3GB with sensible autogrowth from there if required

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

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

Viewing 6 posts - 1 through 6 (of 6 total)

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