2 Gig Limit

[foxup]

Hi,

I have questions about the 2 GIG .DBF size limit. Has anybody passed or obtained that ? I mean what happens exactly @ 2 GIG? Has anybody passed it? Is it "exactly" 2 GIG (aka 2,097,152 KB exactly). The reason I ask is that I have a DBF that is 2,091,808 KB. Can anybody descrive the experince?


Thanks,
FOXUP

 

[Dan Olsson]

No it is 2x1024x1024x1024=2 147 483 648 and I have sometimes been going over that limit by appending records, which immidiately renders the dbf useless. BTW the 2GB limit also applies to CDX- and FPT-files

 

[Mike Lewis]

Yes, I've seen it many times, and I'm sure others have as well.

As Dr. D implies, a gigabyte is 1024 megabytes, and a megabyte is 1024 kilobytes, and kilobyte is 1024 bytes. Put another way, a gigabyte is 2 the power of 30 bytes.

And it doesn't only apply to DBFs, CDXs and FTPs. It applies to any file that can be opened in VFP. That even includes text files. And it makes no difference how you try to open those files. So using low-level file access won't help.

Just about the only way to read those files in VFP would be to use some external component. For example, you could use the FileSystemObject, which is part of the Windows Script Host:

loFS = CREATEOBJECT("Scripting.FileSystemObject")
loFile= loFS.OpenTextFile("c:\MyFile.txt", 1)
DO WHILE NOT loFile.AtEndOfStream
  lcLine = loFile.ReadLine()
  * code here to process the data in lcLine
ENDDO 	
loFile.Close()

Source: Working with large text files in Visual FoxPro

Last thought: If you often come up against the 2 GB limit in your DBFs, then it might be time to think about moving your data to a back-end server, such as MySQL or SQL Server.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[foxup]

OK, so many KB's is a GIG Exactly as I think I have my math wrong. Here is my math:

1 Gigabytes is equal to 1,048,576 Kilobytes
1 GB = 1,048,576 KB
2 GB = aka 2,097,152 KB exactly (am I wrong?)

 

[foxup]

Again, I want to empahsize "KB" not Bytes.

 

[TamarGranor]

Since switching to a SQL back-end is likely to be a big job, here are some thoughts on how to shrink your file.

First, if you delete records, PACK the table. Depending how many are deleted, that may buy you quite a bit of time.

Second, look at your table structure. Has the table gotten this big primarily because of the number of records or because of the number of fields per record (or, of course, some combination)?

Is your data normalized, or are you repeating some data in multiple records?

Assuming PACK doesn't help and the data is normalized, you have two basic choices: partition horizontally or partition vertically.

Partitioning horizontally means moving some records out. You might do that by archiving some data, or by dividing the data up by some field like region.

Partitioning vertically means moving some fields out. Just create a parallel table to hold those fields, plus the PK for each record. That is, create a one-to-one correspondence between the two tables.

Obviously, anything other than PACK is going to require some recoding. If archiving some data is an option, it's probably the least path of resistance because you can likely take your time making that data available.

Tamar

 

[Dan Freeman]

Can anybody descrive the experince?

A lot depends on when, exactly, you hit the limit.

APPEND FROM may look like it succeeds but everything afterward will just crash. USE will report "Not a table" (error 15).

It isn't pretty and there isn't much you can do to recover short of truncating that file somehow, but since it's too big for any of the file-handling tools "somehow" is a big mystery box.

The ugly truth is that if you have a data set that MUST reside in a single table and must be that large, a DBF is the wrong storage mechanism. That's been true for 30 years. This isn't a new thing.

 

[Mike Lewis]

1 Gigabytes is equal to 1,048,576 Kilobytes
1 GB = 1,048,576 KB
2 GB = aka 2,097,152 KB exactly (am I wrong?)

No, you are not wrong. There is nothing wrong with your arithmetic. But is that the issue? I thought you were asking what happens when the limit is reached, and how to deal with it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Just adding one thought: As you are so close to the limit, why don't you try yourself in a copy. Just need to append blank and see what happens. No matter what the behaviour is, you won't be able to store more and you also won't be able to at least use the data up to 2GB, the table will not stay healthy. So you have to prevent hitting the limit.

Bye, Olaf.

 

[Olaf Doschke]

CD (getenv("TEMP"))
Create Table twogblimit.dbf free (c1 C(254), c2 C(254), c3 C(254), c4 c(254), c5 C(7))
? Recsize()
Append Blank

Do While .T.
   * double the records by append the dbf dat to itself in each iteration
   Append From Dbf("twogblimit")
   Doevents Force
EndDo

This results in the error "File ...dbf is too large".
Reccount at that stage then is 2097151, which means the dbf size Header()+Reccount()*Recsize()+1 (the 1 is for the EOF byte) is 567 Bytes below the 2GB limit.
Win Explorer shows 2,147,483,081 bytes, which exactly matches that (2^31-567=2147483081)

So VFP indeed stops before corrupting the DBF. I wouldn't rely on that, though.

The damage is done, even if the DBF is ok, the last data is not saved. Causing all kind of follow up errors.
Tamar has given some pointers on how to address the DBF size and avoid the limit, at least gain some time.

You might SELECT MAX(LEN(ALLTRIM(somecharfield)) FROM yourtable to determine how long the longest trimmed value stored really is and optimize such columns width.

If FPT size is small you may change some char fields to memo, which in general won't involve much changes in code (aside of grind binding needing an editbox instead of textbox), you can make use of 4GB in dbf+fpt file this way.

If your fpt file is hitting the limit you may play with SET BLOCKSIZE when creating a new table to copy over data into, BLOCKSIZE 0 to allocate blocks in the granularity of single bytes, meaning each memo value is stored in a block exactly the length of the value, not padded to the next multiple of 64bytes. But it also means any change of a memo value to a larger size causes memo bloat, the old block is marked unused and the value is stored in a new block with the larger size. In the first moment it'll save space, though. In average 32 byte per memo field, if your memos currently are having the default block sizes of 64 byte, in average the half of each block is unused. Your statistics may vary, if many memos are empty or short.

Bye, Olaf.

 

[foxup]

OK, thank you all for your input and experiences. I made a 2nd copy of the DBF file and tried some quick 'fake' appends to see where it would 'bust'. I've got about 40,000 recs left before it busts so that buys me another 20 days or so. I'll figure out something in the meantime. Again, thanks all for your experiences and help


Thanks,
FOXUP!

 

[craigber]

The issue really isn't that a file gets bigger than 2 GB, but rather how VFP allocates memory to work with the file. For years people asked for the 2 GB limit to be removed and the answer was that it would break all existing applications because it would change how memory was allocated, primarily for keys.

Craig Berntson
.Net MVP, Author, Tech Presenter

http://blogs.msmvps.com/craigber

 

[dbMark]

VFP is a 32-bit application. So the largest value it can handle throughout most if not the entire system is 31-bits since one bit is typically reserved for signing. So... 2^31 = 2,147,483,648. Reducing by 1 to remove the zero value, you get 2,147,483,647 as the typical file maximum size.

Other maximums:
Maximum # of characters per character string or memory variable: 16,777,184
Digits of precision in numeric computations: numbers up to 9007199254740992 (2^53) in exact computations.
(scientific notation resulting in about 15 decimal/Base10 digits of precision)

For many years users have clamored for larger values to be allowed in VFP, essentially moving it into the 64-bit world but MS always responded it would take a total rewrite and they didn't have any business interest to invest in that path (for a "buy once, compile many" model since they preferred to develop their other database systems that provided more revenue with subscriptions, seat licenses, etc).