December 21, 2015 at 8:11 pm
WhiteLotus (12/21/2015)
I haven't changed the autogrow option yet , it is still in 10% growth position.I am thinking to change it to 512 MB . What do you think ? Is it still OK ?
512MB is much better than 10% growth. Personally, I'd still use 1000MB (or more if I knew a good history on the database) just to keep the VLFs down. Just don't use 4000MB. There used to be a bug where it would grow forever at times and I don't personally know if they ever got around to fixing that.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 21, 2015 at 10:12 pm
Sounds good ..I also notice that all databases autogrowth option are 10% . Should I change all of them ?
December 22, 2015 at 4:55 pm
WhiteLotus (12/21/2015)
Sounds good ..I also notice that all databases autogrowth option are 10% . Should I change all of them ?
If the initial size was 1MB, it takes 73 growths to get to 1GB. Prior to 2014, that means that the first 49 growths will create 4 VLFs each (the smallest will be 25KB and the largest will be 14.8MB) and then the next 4 growths will add 8 VLFs each (the smallest will be 8.2 MB and the largest will be 10.9MB) for a total of 304 VLFs. IMHO, there should be no more than 16 VLFs.
Before you do anything else, though, check the number of VLFs in the log file(s) (hopefully, you've not made the mistake of having more than 1 per DB), run the following command...
DBCC LOGINFO('YourDBNameHere');
Remember that it's the creation of VLFs that slow down restores and can otherwise have quite an impact on performance kind of like having a hard disk formatted with too small sector sizes.
My question to you would now be, wouldn't you think it a good idea to not only change the auto-growth but to also repair the damage done by that initial setting?
As a bit of a sidebar, I also change the MODEL database to have 100 for all initial size and growth settings just in case someone makes a database that doesn't know better. At least that's some minor protection for the normal 73 growths. Gives me a chance to fix things before they get totally out of shape.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2015 at 9:39 am
Jeff Moden (12/22/2015)
WhiteLotus (12/21/2015)
Sounds good ..I also notice that all databases autogrowth option are 10% . Should I change all of them ?If the initial size was 1MB, it takes 73 growths to get to 1GB. Prior to 2014, that means that the first 49 growths will create 4 VLFs each (the smallest will be 25KB and the largest will be 14.8MB) and then the next 4 growths will add 8 VLFs each (the smallest will be 8.2 MB and the largest will be 10.9MB) for a total of 304 VLFs. IMHO, there should be no more than 16 VLFs.
16 VLFs seems like an extraordinarily low limit, particularly if you need a large log file for a given db. You also definitely don't want VLFs that are too large, and limiting them that much would create some.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 23, 2015 at 4:42 pm
ScottPletcher (12/23/2015)
Jeff Moden (12/22/2015)
WhiteLotus (12/21/2015)
Sounds good ..I also notice that all databases autogrowth option are 10% . Should I change all of them ?If the initial size was 1MB, it takes 73 growths to get to 1GB. Prior to 2014, that means that the first 49 growths will create 4 VLFs each (the smallest will be 25KB and the largest will be 14.8MB) and then the next 4 growths will add 8 VLFs each (the smallest will be 8.2 MB and the largest will be 10.9MB) for a total of 304 VLFs. IMHO, there should be no more than 16 VLFs.
16 VLFs seems like an extraordinarily low limit, particularly if you need a large log file for a given db. You also definitely don't want VLFs that are too large, and limiting them that much would create some.
16 VLFs for a 1GB log file doesn't seem like too few to me. What are you referring to when you say "large log file"?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 23, 2015 at 4:56 pm
Jeff Moden (12/21/2015)
ScottPletcher (12/21/2015)
Yeah, everybody always "warns" about that, but who on earth would want to apply a 299GB log anyway??Obviously and especially in this case, it wouldn't. The log file was cleared by stopping replication leaving only that (1% or less) that hadn't yet been backed up.
Check which size your log file can reach before you backup the log.
You need the biggest size it can reach after all possible maintenance jobs, reindexing, etc.
Add 50% to that size and make it a new log file size.
Prepare the script with the new size, but do not run it yet.
Before you set up the new size - do several rounds of "log backup - truncate log file" until it's 1MB. Then immediately run the script setting up the new size.
It will minimize the fragmentation of the new file and optimize its performance.
_____________
Code for TallyGenerator
December 23, 2015 at 11:33 pm
Jeff Moden (12/23/2015)
ScottPletcher (12/23/2015)
Jeff Moden (12/22/2015)
WhiteLotus (12/21/2015)
Sounds good ..I also notice that all databases autogrowth option are 10% . Should I change all of them ?If the initial size was 1MB, it takes 73 growths to get to 1GB. Prior to 2014, that means that the first 49 growths will create 4 VLFs each (the smallest will be 25KB and the largest will be 14.8MB) and then the next 4 growths will add 8 VLFs each (the smallest will be 8.2 MB and the largest will be 10.9MB) for a total of 304 VLFs. IMHO, there should be no more than 16 VLFs.
16 VLFs seems like an extraordinarily low limit, particularly if you need a large log file for a given db. You also definitely don't want VLFs that are too large, and limiting them that much would create some.
16 VLFs for a 1GB log file doesn't seem like too few to me. What are you referring to when you say "large log file"?
It's relative, but say 8GB+. [For SAP, a dead minimum 20-30GB log file is recommended.] In my case, I've got several logs that are 8GB or much larger, such as for tempdb on large, consolidated servers, or for certain very large databases. I've never seen any recommendation to limit to only 16 VLFs.
I first learned of the VLF issue years ago from Kimberly Tripp's posts, such as here:
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
"
a transaction log of 64GB would have 16 VLFs of 4GB each. As a result, the transaction log could only clear at more than 4GB of log information AND that only when it’s completely inactive.
[Edit Jan 2015: this algorithm has changed in SQL Server 2014 – see Paul’s post here for details.]
To have a more ideally sized VLF, consider creating the transaction log in 8GB chunks (8GB, then extend it to 16GB, then extend it to 24GB and so forth) so that the number (and size) of your VLFs is more reasonable (in this case 512MB).
"
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 24, 2015 at 3:09 pm
You've confused me a bit, Scott. First you say that 16 VLFs for a 1GB logfile is too low and then you explain using even fewer VLFs per GB by allocating 8GB at a time which would give you only 2 VLFs per GB.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2015 at 12:29 pm
Jeff Moden (12/24/2015)
You've confused me a bit, Scott. First you say that 16 VLFs for a 1GB logfile is too low and then you explain using even fewer VLFs per GB by allocating 8GB at a time which would give you only 2 VLFs per GB.
I never said 16 VLFs for a *1* GB log was too low; I said for a *large* log, which I specified as minimum of 8GB.
I also don't agree with a limit of 16 VFLs max. That's too low because of the need to provide for large logs. My understanding is that even a hundred VFLs is not really a performance issue.
But 16 VLFs should be OK for a 1GB log, as that's only 67M per VLF. But 16 VLFs for, say, a 32GB log seems too low to me, as that's 2GB per VLF.
Similary, for a very large log, you'd allocate 8GB at a time to avoid not only too many VLFs but also too large VLFs. I've got logs that need to be 160GB+. Limiting that to 16 VLFs would be 10GB per VLF, and that's got to be too much.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 25, 2015 at 1:43 pm
ScottPletcher (12/25/2015)
Jeff Moden (12/24/2015)
You've confused me a bit, Scott. First you say that 16 VLFs for a 1GB logfile is too low and then you explain using even fewer VLFs per GB by allocating 8GB at a time which would give you only 2 VLFs per GB.I never said 16 VLFs for a *1* GB log was too low; I said for a *large* log, which I specified as minimum of 8GB.
I also don't agree with a limit of 16 VFLs max. That's too low because of the need to provide for large logs. My understanding is that even a hundred VFLs is not really a performance issue.
But 16 VLFs should be OK for a 1GB log, as that's only 67M per VLF. But 16 VLFs for, say, a 32GB log seems too low to me, as that's 2GB per VLF.
Similary, for a very large log, you'd allocate 8GB at a time to avoid not only too many VLFs but also too large VLFs. I've got logs that need to be 160GB+. Limiting that to 16 VLFs would be 10GB per VLF, and that's got to be too much.
Apologies... misread your post. And, yes, I absolutely agree on the 8GB thing.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 25, 2015 at 1:49 pm
Jeff Moden (12/25/2015)
ScottPletcher (12/25/2015)
Jeff Moden (12/24/2015)
You've confused me a bit, Scott. First you say that 16 VLFs for a 1GB logfile is too low and then you explain using even fewer VLFs per GB by allocating 8GB at a time which would give you only 2 VLFs per GB.I never said 16 VLFs for a *1* GB log was too low; I said for a *large* log, which I specified as minimum of 8GB.
I also don't agree with a limit of 16 VFLs max. That's too low because of the need to provide for large logs. My understanding is that even a hundred VFLs is not really a performance issue.
But 16 VLFs should be OK for a 1GB log, as that's only 67M per VLF. But 16 VLFs for, say, a 32GB log seems too low to me, as that's 2GB per VLF.
Similary, for a very large log, you'd allocate 8GB at a time to avoid not only too many VLFs but also too large VLFs. I've got logs that need to be 160GB+. Limiting that to 16 VLFs would be 10GB per VLF, and that's got to be too much.
Apologies... misread your post. And, yes, I absolutely agree on the 8GB thing.
I now think I misread your post, so I apologize too. I somehow took your "there should be no more than 16 VLFs" to be for any log, but I see you meant it for just that specific 1GB log file. And I definitely agree on that: a 1GB log file almost certainly does not need more than 16 VFLs.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply