MSSQL databases greater than 4GB

Post by **Ron2K** » 03 Jan 2007, 10:00

Hi,

I'm developing an application that reads log files and writes the data to database (MS SQL Server 2005). There's around 10,000 VERY large log files, so the database is going to be huge. I've noticed during testing that as soon as the database hits 4GB in size, SQL Server refuses to grow the database any more which results in data not being written to the database. Now, that 4GB is probably only a third of the data, so that's a problem.

My guess (which may or may not be correct) is that I'll have to use secondary data files, but I can't come up with a way to automatically create a secondary data file and make it the default file on the fly. If anyone has any solutions (or something better than what I'm thinking), I'd much appreciate it.

rustypup · Post by **rustypup** » 03 Jan 2007, 10:06

compression and store/retrieve as BLOB?...

4GB is a miniscule limit for any db... shamefully small - is there a reason you're not using postgre / mysql / etc..?

Post by **Ron2K** » 03 Jan 2007, 10:26

Unfortunately I'm working on Microsoft servers so I'm locked into their pathetic solution.

bootsie · Post by **bootsie** » 03 Jan 2007, 10:30

i think that your problem may be else wher as 4GB limit is not an issue. MS SQL dbs can handle much much more than that.

rustypup · Post by **rustypup** » 03 Jan 2007, 10:45

bootsie wrote:MS SQL dbs can handle much much more than that.

i'm almost convinced of this... ours only started blinking at 3tb+... 4gb sounds like the db activity log may be the issue, (i've seen that one once or twice... )

admittedly, later upgrades to mssql server removes any cap... at a price, of course

<edit>
hang about... is this the "express" edition?
</edit>

Post by **Ron2K** » 03 Jan 2007, 10:47

Could also be an NTFS limitation - but either way, I'm going have to work around this one...

The reason why I'm saying that is that I build a logging mechanism into the app to view any queries that failed to execute. That file also hit 4GB because of all the queries that were now not being executed and I got an "invalid file handle" exception.

rustypup · Post by **rustypup** » 03 Jan 2007, 10:56

hmm...

the bulk of the space consumed by a log file is ' '... so:
1) compression ->BLOB should recover a huge chunk of space... or
2) the compressed flat-file approach, (will probably require a certain level of encryption), which begs the "why use a database?" question... unless you use an alternate/built-in database purely for logging?...

Post by **Ron2K** » 03 Jan 2007, 14:04

Problem solved.

We were all wrong. I've just attempted to manually increase the file size (why the hell didn't I think of that before?) and got this error:

Code: Select all

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096MB per database. (Microsoft SQL Server, Error: 1827)

I'm writing to a SQL database on my local machine using the version of SQL Server that comes with Visual Studio 2005, which now appears to be handicapped. *sigh*

So, it's nothing to do with me or my code, it's Microsoft. Who wants to join me in cursing them?

rustypup · Post by **rustypup** » 03 Jan 2007, 14:27

hence my question about which version... i dimly recall that the express flavour was capped at 4gb... and yes, much cursing was involved...

seriously... mysql/postgre etc and let m$ hang...