4

In my C# program, I am reading in about 350GB worth of zipped CSV files and storing the data in a SQLite v3 database. I'm using System.Data.SQLite from NuGet.

My database is about 147GB at the moment, and I am getting an error when trying to run the next INSERT query:

(778) os_win.c:41557: (665) winWrite2(D:\System.db) - The requested operation could not be completed due to a file system limitation.
Error: near line 1: disk I/O error."

The drive is 1.81TiB and has 1.37TiB free. The volume is NTFS. The DB is 146650432KiB. ChkDsk reports everything is OK, and the drive is otherwise working perfectly.

This happens for any INSERT from my program as well as from the DB Browser application.

(The database would probably shrink substantially if I ran a VACUUM operation, since I have run a lot of INSERT statements)

I estimate there are approximately 3.5 billion rows in 12 tables. From what I've read, there should be no issues with a SQLite database of this size.

Any ideas why this might be happening and how to work around it? Ideally, it would be great if I didn't have to start the whole import process from scratch as it's taken a few days to get to this point, which is only about 20% complete.

CL.
  • 173,858
  • 17
  • 217
  • 259
Ozzah
  • 10,631
  • 16
  • 77
  • 116
  • 1
    Is this a 32-bit system or a 64-bit system? Same question for the actual process? – Ron Beyer Mar 25 '18 at 22:38
  • 64-bit system. The application is compiled targetted to "Any CPU", and the process is 32-bit. I've re-built as 64-bit and let's see how it goes. – Ozzah Mar 25 '18 at 22:41
  • It still happens when the process is 64-bit. – Ozzah Mar 25 '18 at 22:44
  • I've found once I get about over 8GB in a single table SQLite doesn't have the necessary performance I need. I would look for a different db. Also https://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file – Kevin Mar 25 '18 at 22:44
  • @Kevin That's great and I will keep that in mind, but it doesn't really answer the question. – Ozzah Mar 26 '18 at 00:40
  • Then I would star looking at Event logs and other things. It's possible your disk is full, you are having contention issues or a myriad of other things. Time to start troubleshooting. – Kevin Mar 26 '18 at 00:48
  • Neither NTFS nor SQLite has a limit at 147 GB. – CL. Mar 26 '18 at 21:27
  • Try `.log stderr` in the [command-line shell](http://www.sqlite.org/cli.html), and then appending (dummy) data from there. – CL. Mar 26 '18 at 21:28
  • @CL. "(778) os_win.c:41557: (665) winWrite2(D:\System.db) - The requested operation could not be completed due to a file system limitation. Error: near line 1: disk I/O error." The drive is 1.81TiB and has 1.37TiB free. The volume is NTFS. The DB is 146650432KiB. ChkDsk reports everything is OK, and the drive is otherwise working perfectly. – Ozzah Mar 26 '18 at 23:47

1 Answers1

4

The Windows error code 665 means exactly what the message says: the file system has reached some limit.

The limit is not on the file size itself, but on the number of internal structures needed to manage a fragmented file. (The exact same problem happens with Microsoft's own SQL Server.)

Microsoft has a hot fix that would allow you reformat the drive with a file system that reserves more space for these structures. But an easier way to fix this problem now would be to run a defragmenter, or to just make a new copy of the file (if you have enough free space).

If possible, try to avoid fragmenting the file, i.e., don't write other data to the same file system while filling the database. Alternatively, to avoid smaller fragments, extend the size of the database file by a large amount by temporarily inserting a large row:

CREATE TABLE t(x);
INSERT INTO t VALUES(zeroblob(500*1024*1024));  -- 500 MB
DROP TABLE t;
CL.
  • 173,858
  • 17
  • 217
  • 259