Increase the size of sql compact 3.5 .sdf file

Question

I'm using Sql Compact3.5 as my DB with C# .NET what is the maximum size of sdf that I can give? Is there any way to programatically increase the maximum size of the sdf file? If so how?

Daciadacie

Answer 1

The maximum size of the database is by default 128 MB. Override this by using the following connection string.

Data Source=MyData.sdf;Max Database Size=256;Persist Security Info=False;

(from: http://www.connectionstrings.com/sql-server-2005-ce) and (from: http://msdn.microsoft.com/...)

The absolute maximum size of the database is 4gb.

Falito

Answer 2

Actually, Max Database Size should be pointed as from 16 to 4091 in Microsoft SQL CE 4.0. Default value is 257 (Mb). I tried to use 4096 (exactly 4Gb) and error occured. 4091 is valid value, and now my connection string looks like:

Data Source=file.sdf; Max Database Size=4091

If you use Microsoft SQL Compact Edition along with Entity Framework (I do), the connection string for EF looks like this:

string template = "metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlServerCe.4.0;"
                + "provider connection string=\";Data Source={0}; Max Database Size=4091 \";";
string s1 = string.Format(template, SdfFilePath);

where Model is the name of your edmx file.

Nubianubian

Answer 3

Just for the benefit of people searching...

If your SQL CE database is published and in use, you can still increase the size by changing the Max Database Size in the connection string (as above). You are not stuck with the value you initially choose.

http://blogs.msdn.com/b/sqlservercompact/archive/2007/06/13/the-story-of-max-database-size-connection-string-parameter.aspx

Paresthesia

Thanks for the link. Also note that only the first connection can reserve the shared memory. The second connection can not do anything about it, which in my specific scenario is ok. +1 – Meuse

Answer 4

As Benny Jobigan said, you can specify a maximum size via the connection string.

However, the size specified must be between 16 and 4091. If you specify a size outside if this, the following exception is thrown (from the C# SQL Server Compact 3.5 library):

System.ArgumentException: The value of ssce:max database size token is outside of range of allowed values (16,4091).

Sweltering

Thank you for this comment. I completely scanned past the listed min/max values in the error message (Just assumed it was an error code) This was the first thing I found via google to point out the number is 4091. – Hent

This should be the accepted answer. The current accepted answer does not give the actual maximum value you can set. – Applegate

Answer 5

Right click on database name in Server Explorer and select "Modify Connection". Click on Advanced Button. You can change database default size.

Herder