NOTE: This is a re-post of the same topic that is soon to be published in our SSTips forum of the Shelby Community site. I want to share the information with a wider audience than just the Shelby Systems, Inc. customer base, so I am posting it here too, with only minor edits for form and content. Also I want to acknowledge the contribution of Thomas LaRock, whose blog post on autogrowth settings provided the inspiration for some of the code in the query solution I give below.If you use the free Express edition of SQL Server (like the one that is bundled with the Shelby v.5 installation software), then there is an upper limit on how large your database files can be. (SQL Server Standard Edition has an upper limit of 524 Petabytes -- effectively no limit at all -- but it is not free.) If your database reaches the limit of your SQL Server Express version, you will begin to experience errors due to the inability of the database tables to accept new data. It can be expensive and time-consuming to correct this problem once it occurs. Here is a brief chart of the Express versions and their size limits:
- 2000 Desktop - 2 GB
- 2005 Express - 4 GB
- 2008 Express - 4 GB
- 2008 R2 Express - 10 GB
- 2012 Express - 10 GB
select SQL_Server_Version = @@version, SQL_Server_Edition = serverproperty('Edition'), SQL_Server_Name = serverproperty('ServerName'), Database_Name = a.Name, Maximum_Database_Size_MB =
case when serverproperty('EngineEdition') in (1, 4) then
case when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then '2048 MB'
when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then '4096 MB'
when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then '4096 MB'
else '10240 MB' end
else 'Effectively no limit'
end, Data_Size_MB = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), Available_Growth_MB =
case when serverproperty('EngineEdition') in (1, 4) then
case when cast(serverproperty('ProductVersion') as nvarchar(128)) like '8.%' then 2048
when cast(serverproperty('ProductVersion') as nvarchar(128)) like '9.%' then 4096
when cast(serverproperty('ProductVersion') as nvarchar(128)) like '10.0%' then 4096
else 10240 end
end - convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2))from sys.sysfiles awhere a.name not like '%_log';
- SQL_Server_Version - a verbose description of the version, edition, and other properties of your SQL Server environment
- SQL_Server_Edition - the edition of SQL Server
- SQL_Server_Name - the name (including instance) of SQL Server
- Database_Name - the name of your database
- Maximum_Database_Size_MB - for Express editions, the upper limit of the size of the database, measured in Megabytes; for Standard editions, "Effectively no limit"
- Data_Size_MB - the amount of data stored in the database, measured in Megabytes. This is the critical number. If this number ever equals the value shown for the Maximum_Database_Size_MB, the Shelby v.5 software will start throwing errors whenever new values are entered because there will be no room to store them in the database.
- Available_Growth_MB - the difference between the maximum database size and the current data size; when this reaches zero, trouble will begin.
If you are a Shelby v.5 customer and you discover that you are about to max out your database size, there are some things you can do about it. Read the SSTip post in the Shelby Community (to be posted February 1, 2013) for suggestions.
