February 26, 2016 at 8:54 am
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.
February 26, 2016 at 10:56 am
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
February 26, 2016 at 12:19 pm
[/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]
February 26, 2016 at 12:22 pm
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();
February 26, 2016 at 1:28 pm
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
February 27, 2016 at 9:12 am
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