August 2, 2016 at 12:28 pm
I'm just curious if anyone knows the reason why database files, by default, are set to unlimited growth if they are a data file, but limited growth if they are a log file? In our current environment, disk space is not a big concern, and we also run our backups as necessary to keep the log file sizes from auto-growing.
Is there a reason that you would want to force your log file to stop growing? I understand that it could fill up a drive (if you don't have the appropriate monitoring and maintenance in place), but the same argument can be made for a data file, correct?
Thanks!
August 2, 2016 at 5:09 pm
No idea. Hadn't noticed as I usually reset things in production.
My guess is that people tend to have multiple data files when databases grow, so you want to allow growth to the limit of all files on all disks. Rarely do I see multiple log files.
August 3, 2016 at 1:58 pm
I believe that SQL itself has an internal limit on how large of a log file it can use, so the max log size is set to that. No doubt something to do with the pointers stored in, and used for processing, the log file.
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".
August 4, 2016 at 4:02 am
ScottPletcher (8/3/2016)
I believe that SQL itself has an internal limit on how large of a log file it can use, so the max log size is set to that. No doubt something to do with the pointers stored in, and used for processing, the log file.
Correct. SQL Server has a limit on the file size of the TLog for any database of 2TB.
Given that, and that it also has a limit on the file size of any DB data file (16TB), why does it not also set that maximum? It's these inconsistencies that get me.
See SQL Server maximum capacity specifications.
Thomas Rushton
blog: https://thelonedba.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply