How to check if my SQL Server Express database exceeds the 10 GB size limit?

Question

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.

Answer 1

In tests I have seen that:

sp_spaceused

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 [EDIT2018 updated link]

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."

So checking

CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0 AS [UsedSpaceInMB]

seems to be the best option.

In combination with EF in c# 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;

Answer 2

Execute this SQL command, and it will reveal the disk-space usage of current database.

sp_spaceused

It also can be used to query the space usage of specific table. This link provides useful information about this problem.