I am developing a web site, it uses SQL Server 2008 R2 Express for its database. And in testing, there is a lot of data and images stored into this database.
According to wiki, the SQL Server Express edition has a 10 GB size limit. When I insert data and reach the limit, what exception will be thrown? Or, how do I detect the approaching limit problem by codes ?
I use EF 5 with code-first approach to insert large data set.
In tests I have seen that:
won't work as expected, it showed 12GB after deleting lots of records. And the other answers regarding query sys.databases were not clear enough to me.
Searching around I found a very good explanation regarding SQL Server 2012 Express Edition 10GB Size Limit on Ramons weblog
SELECT [name] AS [Filename], [size]/128.0 AS [Filesize], CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [UsedSpaceInMB], [size]/128.0 - CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [AvailableSpaceInMB], [physical_name] AS [Path] FROM sys.database_files
"... space includes the transaction log and it also includes all unused space within these files. .... SQL Server Express will start complaining when it cannot reserve any more space for the datafile."
CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [UsedSpaceInMB]
seems to be the best option.
In combination with EF my request to the DB looks like
string sqlSelect = "SELECT CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [UsedSpaceInMB] FROM sys.database_files"; var dbResult = dbInstance.Database.SqlQuery<Decimal>(sqlSelect).FirstOrDefault(); double spaceUsedInGb = Convert.ToDouble(dbResult)/1024;