Can I run a T-SQL query to determine the maximum data size for an instance of SQL Server Express?

Question

The original version of SQL Server Express had a fixed database size of 4 GB. Since 2008 R2, it has increased to 10 GB.

Our source code executed SELECT SERVERPROPERTY ('edition') , then looked at the Express line to identify the express version. Then we accepted the limit of 4 GB. Obviously, this was broken when 2008 R2 came out. I want to avoid using Express or Express R2 to get a limit of 4 GB or 10 GB. This will fail if / when Microsoft releases the “R3” version with some arbitrary new limit.

Is there a way to programmatically determine (i.e. by running a T-SQL query) the current size limit?

Answer 1

If you restore a backup larger than 4 GB in SQL Server 2005 Express Edition, you will receive this error:

CREATE DATABASE or ALTER DATABASE failed because the total total size of the database will exceed your license limit of 4096 MB per database.

If you have an existing database in SQL Server 2005 Express Edition and suddendly exceeds 4 GB, you will get this error:

Failed to allocate a new page for the database 'YourDatabaseName due to insufficient disk space in the file group' YourFilegroup. Create the necessary space by dropping objects in the file group, adding additional files to the file group, or setting autorun for existing files in the file group.

So the answer is NO. The database size limit parameter is defined in sqlservr.exe. If you cannot recompile the source code, you cannot get it through TSQL.

Inspired by the suggestions of @Kevin Ross and @Damien_The_Unbeliever:

 SELECT CASE WHEN SERVERPROPERTY ('EngineEdition') = 4 -- 4 = Express Edition THEN CASE WHEN (SELECT max_size FROM sys.database_files WHERE file_id =1)=-1 THEN CASE WHEN CONVERT(REAL,CONVERT(VARCHAR(5),SUBSTRING(CONVERT(VARCHAR(5),SERVERPROPERTY('productversion')), 1, CHARINDEX('.', CONVERT(VARCHAR(5),SERVERPROPERTY('productversion')))-1))) < 10.5 -- Not SQL Server R2 THEN 'You have got ' + CONVERT(VARCHAR(38), (SELECT 4096-size/128 FROM sys.database_files WHERE file_id =1)) +' Mb ' +'(' + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT 4096 - size/128 FROM sys.database_files WHERE file_id =1)) / 4096)) + '%) available.' + ' You may increase your database size up to 4Gb.' ELSE -- You have got SQL Server R2 'You have got ' + CONVERT(VARCHAR(38), (SELECT 10240-size/128 FROM sys.database_files WHERE file_id =1)) +' Mb' +'(' + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT 10240 - size/128 FROM sys.database_files WHERE file_id =1)) / 10240)) + '%) available.' + ' You may increase your database size up to 10Gb.' END ELSE CASE WHEN CONVERT(REAL,CONVERT(VARCHAR(5),SUBSTRING(CONVERT(VARCHAR(5),SERVERPROPERTY('productversion')), 1, CHARINDEX('.', CONVERT(VARCHAR(5),SERVERPROPERTY('productversion')))-1))) < 10.5 -- Not SQL Server R2 THEN 'You have got ' + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128) +' Mb left out of ' +' Mb (' + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1)) / CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1)))) + '%).' + ' You may increase your database size up to 4Gb.' ELSE -- You have got SQL Server R2 'You have got ' + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128) +' Mb left out of ' + CONVERT(VARCHAR(38), (SELECT max_size FROM sys.database_files WHERE file_id =1)/128) +' Mb (' + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1)) / CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1)))) + '%).' + ' You may increase your database size up to 10Gb.' END END ELSE -- Congratulations! You have got something better than Express Edition! CASE WHEN (SELECT max_size FROM sys.database_files WHERE file_id =1)=-1 THEN 'Main file will grow until the disk is full.' ELSE 'You have got ' + CONVERT(VARCHAR(38), (SELECT max_size - size FROM sys.database_files WHERE file_id =1)/128) +' Mb left out of ' + CONVERT(VARCHAR(38), (SELECT max_size FROM sys.database_files WHERE file_id =1)/128) +' Mb (' + CONVERT(VARCHAR(38), CONVERT(int, 100*CONVERT(float, (SELECT max_size - size FROM sys.database_files WHERE file_id =1)) / CONVERT(float,(SELECT max_size FROM sys.database_files WHERE file_id =1)))) + '%)' END END AS Database_Info 

Obviously, I did not have the opportunity to test it on SQL Server 2005-2008 R2.

Answer 2

I have nothing but 2008R2 Express to test this, but is something like this working?

 SELECT case when SERVERPROPERTY ('EngineEdition')=4 THEN CASE WHEN convert(real,convert(varchar(5),SERVERPROPERTY('productversion'))) <10.5 THEN '4Gb Limit' ELSE '10Gb Limit' end else 'no limit' end as Size_Limit 

This first checks if it is an express editor, if it is not, then it returns “No Limit” if it is expressed, it checks the version number if it is less than 10.5 (pre 2008R2), then it returns 4Gb, it is more or equal to 10.5 then it displays 10Gb