March 21, 2012 at 2:52 pm
We have a vendor product monitoring Web access running on a SQL Server 2008 R2 64-bit Standard Edition. The transaction log for the database has file growth set to 100 MB and restricted file growth set to 2,097,152 MB. However, the transaction log keeps filling up all the disk space and logging messages that the transaction log is full. The last time the transaction log got to 28 GB, even though the disk usage report shows that only a few hundred MB are being used. Am I correct that the restricted file growth option sets the maximum file size for the transaction log? If so, why would SQL Server be ignoring this limit?:exclamation:
March 21, 2012 at 2:57 pm
Brian Brown-204626 (3/21/2012)
Am I correct that the restricted file growth option sets the maximum file size for the transaction log? If so, why would SQL Server be ignoring this limit?:exclamation:
Yup. That file is restricted to a maximum size of 2,097,152 MB. That's 2 TB or 2048 GB. If the file is currently 28GB, then it's well under the maximum limit, so it's not being ignored.
Also worth noting, the 2TB limit is the maximum size that a log file can reach ever, just like data files have a max size of 16 TB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2012 at 3:01 pm
Sorry, I mis-read the numbers. I was thinking that it was 2 GB, which it would be if the size was in KB, not MB. My bad.
July 10, 2012 at 10:45 am
GilaMonster (3/21/2012)
Brian Brown-204626 (3/21/2012)
Am I correct that the restricted file growth option sets the maximum file size for the transaction log? If so, why would SQL Server be ignoring this limit?:exclamation:Yup. That file is restricted to a maximum size of 2,097,152 MB. That's 2 TB or 2048 GB. If the file is currently 28GB, then it's well under the maximum limit, so it's not being ignored.
Also worth noting, the 2TB limit is the maximum size that a log file can reach ever, just like data files have a max size of 16 TB.
For the restricted growth & all, one of my test boxes is holding a single LDF on sql 2005 sp4 (x64) which is currently @ 2.57 TB.
Believe the 2 TB limit is for MBR partitions & no holds bar for GPT.
July 10, 2012 at 1:57 pm
yup (7/10/2012)
Believe the 2 TB limit is for MBR partitions & no holds bar for GPT.
Nothing whatsoever to do with partitions or partition tables on disk.
The max size of a single LDF file is 2 terabytes. Now, you can have multiple log files, each of that size.
It's got to do with the offsets for the VLFs within the files and the data type used to store those offsets.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 10, 2012 at 7:05 pm
GilaMonster (7/10/2012)
yup (7/10/2012)
Believe the 2 TB limit is for MBR partitions & no holds bar for GPT.Nothing whatsoever to do with partitions or partition tables on disk.
The max size of a single LDF file is 2 terabytes. Now, you can have multiple log files, each of that size.
It's got to do with the offsets for the VLFs within the files and the data type used to store those offsets.
Not sure if I can attach images here but for now here is the table
select name, groupid, size/128 from DbLogSz..sysfiles
name groupidSizeOnDisk_MB
DbLogSz14
DbLogSz_log02734080
Its a x64 SQL 9.0.5057
July 11, 2012 at 1:22 am
Yes, you can attach screenshots.
DBCC UpdateUsage (shouldn't affect log files, but), and check what the size is in the OS please.
The 2TB is documented in BoL and the MSDN pages, so if you have a log larger, please file a connect item and tell MS that they are wrong about the limits.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2012 at 3:04 am
As per the documentation the TLOG files are capped at 2 TB (typically you wount be using one this big) the MS document is http://msdn.microsoft.com/en-us/library/ms143432. All the papers say that the LDF growth is limited to 2 TB, on my test machine I've a log file at 2.7 TB.
Presuming the 2 TB limit is for MBR partitions & no holds bar for GPT guess the documentation prepared with MBR in mind here with GPT its an LDF 2.72 TB in size.
All can share your comments here at https://connect.microsoft.com/SQLServer/feedback/details/753184/sql-server-transactional-log-file-ldf-having-a-2-tb-limit
July 11, 2012 at 3:14 am
yup (7/11/2012)
Presuming the 2 TB limit is for MBR partitions & no holds bar for GPT guess the documentation prepared with MBR in mind here with GPT its an LDF 2.72 TB in size.
No, it has nothing whatsoever do do the MBR/GPT (which limits the total size of a partition that can be created). If it did, data files would also be limited to 2TB. They're limited to 16TB each.
It has to do with the data type used for the offsets for the VLFs as they are recorded in the log header
Screenshot please, of the file in the file system with the file size visible.
The fact that the data file is 4MB and the log listed as 2+ TB makes me wonder if it's an error in the metadata.
Also, you'll need to attach some proof to the Connect item (the screenshot of the file in explorer should do), or it will likely be discounted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2012 at 3:44 am
MBR limits partitions to 2TB, where as GPT doesn't, it's a lot larger than that.
The main restriction with MBR is the fact it will only support 4 partitions max, where as GPT supports up to 128. I've never tried to create a 2+ TB log on a GPT partition, (shouldn't think i ever will) lol.
As Gail pointed out, the fact you have a 4MB data file and a 2TB log doesn't quite make sense
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 5:32 am
Here is an image attached.
Also if you can get some one to share details from some where s/he is using an MDF / NDF on MBR above 2 TB. Most of the people are not aware of basic & dynamic I'm afraid if any one would 've tested GPT.
Now why is the data file at 4 MB, 🙂 because I left it there like that and started with a 512 kb ldf holding 2 vlf.
July 11, 2012 at 7:07 am
yup (7/11/2012)
Also if you can get some one to share details from some where s/he is using an MDF / NDF on MBR above 2 TB. Most of the people are not aware of basic & dynamic I'm afraid if any one would 've tested GPT.
The size limitations have zero, nothing at all to do with the MBR/GPT partitioning aspects, nothing to do with dynamic or basic disks.
The 16 TB limitation on the data file is because the PageNo is an int, so the max pages in a file is 2.1 billion (roughly), a page is 8kB and hence the max size of a file is (roughly) MAXINT * 8 kb = 17 179 869 176 kb = 16 384 GB = 16 TB
The size limitation on a log file is because the log header keeps track of the offsets of the VLFs within the log file in kb and uses an INT for that. Hence the max offset of a VLF is 2,147,483,647 kb = 2 048 GB.
It is theoretically conceivable that the log could go over 2TB by the size of one VLF, not sure offhand what the max size of a VLF is. Not sure if there is one or if it's 1/16 of the size of the last growth. Still, to have a 700GB VLF, that would mean that the last autogrow would have been 10TB, which is obviously not possible.
Please post a screenshot of an Explorer window showing that log file with its size (as recorded by explorer). Or a command window with the output from DIR. As I said, my gut feel here is some metadata errors, it's not uncommon for there to be slight errors in file size metadata.
If you give me a couple hours to plug in and fire up my server, I'll create you a DB with a 3 TB data file. Or would you prefer 4.5? (I only have 5TB of storage space available in the server)
It's not on MBR of course, since you cannot have a partition larger than 2TB on MBR.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2012 at 7:15 am
yup (7/11/2012)
Here is an image attached.Also if you can get some one to share details from some where s/he is using an MDF / NDF on MBR above 2 TB. Most of the people are not aware of basic & dynamic I'm afraid if any one would 've tested GPT.
Now why is the data file at 4 MB, 🙂 because I left it there like that and started with a 512 kb ldf holding 2 vlf.
Post a screenshot of the file properties from Windows Explorer that shows its physical size on disk. I can hack up the metadata in SQL and make the output of those queries show invalid information with relative ease, and it is not entirely uncommon to have incorrect metadata show up from time to time.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
July 11, 2012 at 10:51 am
You wount believe me.... all screen shots attached.
Let me know if you want me to take a backup of this DB & upload.
July 11, 2012 at 5:08 pm
Hi, were you able to check on this ?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply