August 13, 2014 at 5:46 am
I have been recording data about the sizes of our database files, free space MB, free space % and VLFs.
This morning the recording job failed due to an overflow on the LogVLFs.
Turns out the VLF count went through the roof (Aka SmallInt upper limit).
Just before recording started the log file was shrunk to 8192 MB then grown by 8192 MB till 256 GB.
The log had not grown since records started. (2014-06-24)
LogSizeMBLogUsedPercLogFreeMBLogFreePercLogVLFsSampleDate
277494.8107.660256238.20092.340974822014-08-13 11:05:00
277494.8107.067257884.18592.933974822014-08-13 10:05:00
277494.8106.778258685.04693.222974822014-08-13 09:24:00
253952.23052.259121238.25247.7412742014-08-13 04:05:00
253952.23037.250159354.28862.7502742014-08-13 03:05:00
253952.2302.594247365.94897.4062742014-08-13 02:05:00
253952.2300.957251520.66899.0432742014-08-13 01:05:00
I know Kimberly Tripp[/url] does not recommend log growth using multiples of 4GB increments, but the bug explained by Paul Randal[/url] has nothing to do with crazy VLFs growing.
We did pump a lot of data into the database yesterday evening, so log growth was expected.
What caused this massive increase of VLFs?
Will it happen again if I shrink then regrow? (8000 MB increments)
Cheers
August 13, 2014 at 6:58 am
at 4 or 8 GB increments your going to experience excessive waits while the log grows.
What size does the log need to be?
Find this out and then leave it there
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2014 at 8:12 am
Thanks for responding.
253952.230 is the size we wanted. (To avoid log growths)
Has been great for a long time.
But the log needed to grow this morning.
253952 / 8192 = 31 growths
277494 / 8192 = 33,873779296875 growths ??
How could we possibly get so many VLFs in 3 growths?
According to Ms Tripp ".....chunks larger than 1GB = 16 VLFs"
3 x 16 = 48
So I would expect us to have 322 VLFs, not 97482
I'll have to shrink and regrow to trim the VLFs but need to know if this is avoidable in future.
Cheers
August 13, 2014 at 1:03 pm
What is the Autogrowth increment on the log file?
Have you double checked?
August 13, 2014 at 1:11 pm
DennisPost (8/13/2014)
I know Kimberly Tripp[/url] does not recommend log growth using multiples of 4GB increments, but the bug explained by Paul Randal[/url] has nothing to do with crazy VLFs growing.
We did pump a lot of data into the database yesterday evening, so log growth was expected.
What caused this massive increase of VLFs?
Will it happen again if I shrink then regrow? (8000 MB increments)
Cheers
I pregrow the files to an appropriate size (sounds like 256GB may need to be considered). But I avoid the multiples of 4GB and will go with 4000MB or 8000MB instead. And then set the autogrowth increment to something around 4000MB.
Pregrowing the file avoids most of the unexpected log growth waits while in production hours.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2014 at 1:10 am
arnipetursson (8/13/2014)
What is the Autogrowth increment on the log file?Have you double checked?
Triple checked. Definitely 8192 MB.
Just changed it to 8000 MB.
August 14, 2014 at 1:47 am
I pregrow the files to an appropriate size (sounds like 256GB may need to be considered). But I avoid the multiples of 4GB and will go with 4000MB or 8000MB instead. And then set the autogrowth increment to something around 4000MB.
Pregrowing the file avoids most of the unexpected log growth waits while in production hours.
It was pregrown.
I will shrink and regrow it to 256000 MB (32 * 8000 MB).
But should an exceptional moment occur again and grow the log, what guarantee do I have that this won't happen again?
August 14, 2014 at 7:40 am
DennisPost (8/14/2014)
I pregrow the files to an appropriate size (sounds like 256GB may need to be considered). But I avoid the multiples of 4GB and will go with 4000MB or 8000MB instead. And then set the autogrowth increment to something around 4000MB.
Pregrowing the file avoids most of the unexpected log growth waits while in production hours.
It was pregrown.
I will shrink and regrow it to 256000 MB (32 * 8000 MB).
But should an exceptional moment occur again and grow the log, what guarantee do I have that this won't happen again?
Changing to 8000 instead of 8GB will most likely help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2014 at 2:32 am
Changing to 8000 instead of 8GB will most likely help.
I'll will do that. But why? What is this based on?
August 15, 2014 at 7:42 am
DennisPost (8/15/2014)
Changing to 8000 instead of 8GB will most likely help.
I'll will do that. But why? What is this based on?
I have seen the same sort of thing. Albeit rarely in a previous environment. We never saw it after setting autogrowth to something not a multiple of 4GB.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 19, 2014 at 9:48 am
All good again.
Just a DBCC SHRINKFILE removed the clunky VLFs.
(Ran after full backup and a few log backups. Then diff backup when VLFs returned to normal to ensure the backup chain was intact)
Log regrown to 256000 MB with 8000 MB increments.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply