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.

Disruption

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;

Steal

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.

Disruption

Answer 3

To check the database size query:

sys.databases

Just query this, perhaps with C# or if you use SSMS (sql server management studio) shell, you can schedule a job that emails you or whatever you want.

Example: SQL Server 2008: How to query all databases sizes?

Edit: NOT sure if error is thrown, it should log to event log or a sql log...

Side note: Developer version is only $50 and holds same as Datacenter which hold 524 PB http://technet.microsoft.com/en-us/library/cc645993%28v=sql.105%29.aspx

Langobardic

Thanks for help. During development, our team use express version to build the project. – Disruption

Answer 4

To Check the Size of the Database Two Ways:

/* new school way - data plus log and run in the local db that you want to see here you can see the log and the mdf file. */ SELECT size*8.0/1024.0 as size_in_gb, * FROM sys.database_files GO

/* old school way, run for all db size*/ sp_helpdb

FYI - the MDF and NDF files are the only ones that attribute to the file size exceeding 10GB.

Tartan

I think you are calculating size_in_mb (so MB not GB). – Sporocyst

Answer 5

I am using the following method to calculate database current size crucial for comparing with sql size limitations:

    public static int GetDbSizeInMB([NotNull] string connectionString) {
        using (SqlConnection sqlConnection = new SqlConnection(connectionString)) {
            sqlConnection.Open();
            using (var sqlCommand = new SqlCommand()) {
                sqlCommand.CommandType = CommandType.Text;
                sqlCommand.CommandText = @"
                    SELECT SUM(CAST(FILEPROPERTY([name],'SpaceUsed') AS int)/128.0) AS [UsedSpaceInMB]
                    FROM sys.database_files
                    WHERE type_desc like 'ROWS' or type_desc like 'FULLTEXT'
                    ";
                sqlCommand.Connection = sqlConnection;
                return Convert.ToInt32(sqlCommand.ExecuteScalar());
            }
        }

)

Emilemile