Published November 11th, 2003 by Jim O'Halloran
MSDE Database Limits
Spent quite a while this afternoon fighting an…
Error: -2147217900 - Could not allocate space for object ‘TABLE_NAME’ in database ‘DATABASE_NAME’ because the ‘FILEGROUP_NAME’ filegroup is full.
… when doing an “INSERT INTO … SELECT …” query on an MSDE 1.0 (SQL Server v7.0 engine) database. The database was pretty big, and the insert query was going to generate a metric shirt load of rows to be inserted.
This was a bit strange at first because the filegroup in question was set to automatically grow when it ran out of space. The disk holding the database was not full and should have had plenty of space. The filegroup had not grown to fill the disk either.
Anyway, after much headscratching it appears that MSDE (all versions) has a 2Gb database size limit. If you hit the 2Gb limit, Microsoft advises upgrading to the fully fledged SQL Server product.
Applications may grow beyond the MSDE 2000 2-GB data limit or the application’s user base may grow beyond the workload limitation in SQL Server Desktop Engine. If this occurs, Microsoft recommends migrating the MSDE 2000 solution to a SQL Server based solution.
For what its worth, even the fully fledged SQL Server may impose a database size limit. When installed with Microsoft Small Business Server (SBS) 4.5 the limit is set at 10Gb. This doesn’t seem to apply to SBS 2000 (scroll down to the “Optimisations” heading) though.
The Small Business Server 2000 component server applications are not limited in any way compared with the stand-alone versions other than the fact that the applications can be installed only on a Small Business Server 2000–based computer.
Although SBS 2000 does carry the same “must be a primary domain controller”, “no more than 50 users”, and “no domain trusts” limitations as SBS 4.5.
Jeremy Zawodny Says
Heh.
And people make fun of MySQL…
Nov 12th, 2003 at 11:58 am
Jim O'Halloran Says
In its defence, MSDE is a good solution… But limits like a 2Gb database size limit which are there simply to “sell an upgrade” really irritate me.
In my situation, the customer I’m writing this for does have the full SQL Server product, and the app will run under SQL Server. I don’t want to run NT4 Server on my laptop (yuk!), so I can’t run the full SQL Server, which now means I can’t develop the application easily on my primary development machine.
Unfortunately, for various reasons MySQL isn’t a good fit for our customers either.
Jim.
Nov 12th, 2003 at 12:10 pm
Steve Says
MSDE has a 2GB data file size limitation - but it only checks the file size when trying to GROW the data file.
You can restore a database created on Std Edition or Enterprise Edition that is ALREADY bigger than 2GB, and MSDE is OK with that. As long as you don’t run out of space in the data file (and MSDE doesn’t have to enlarge the file) you’ll be OK.
The license for MSDE only allows a maximum of 2GB per database, so don’t do this.
Jul 22nd, 2004 at 3:06 pm
Charo Says
We solve a BIG problem with an application that only run with MSDE database engine.
Thank you, Steve!!!!
Sep 8th, 2005 at 11:54 pm
prashant kothari Says
I am using MSDE (SQL Server v8.0 Engine) from a laptop. Can you provide a some details about the limitations on the Database in terms of size, max users etc. It would be really nice if you can send me some links/URLs that provide any information about this DB specifications/limitations.
Sep 22nd, 2005 at 5:24 pm
Mark Says
MySQL is very good at what it does but is in a totally different ball park to MSSQL / MSDE. If you’re doing basic SQL then MySQL wont let you down but as soon as you move onto Views, Functions, Stored Procs etc in MSSQL you wont go back to MySQL unless you have to. The other annoying thing about MySQL is it’s case-sensitivity for table and column names (yuk).
Microsoft have just released SQL2005 Express, which is a 4GB version like the old MSDE. If you blow this then you’re company should be making enough money to fork out a few thousand for full SQL. If you’re from the free software brigade then get MySQL and get used to writing SQL long hand.
I have found several cases where people have written mickey mouse applications for people in MSDE. The applications have been heavily used or have been in use for a few years and eventually the 2GB limit is reached. The answer was normally to spend £3000 on a SQL licence. Obviously people complain at this. But I think this is the fault of the original developer for not looking at the scalability or warning them that one day they may reach a limit and have to pay and not MS…
Jan 6th, 2006 at 3:00 am