Question :
I have a SQL Server (version 11.0.5058). I tried to restore a database from a bak file and I got an error saying:
CREATE DATABASE or ALTER DATABASE failed because the resulting
cumulative database size would exceed your licensed limit of 10240 MB
per database.
I was told that I do not have an Express instance, and thus I do not have a database limit of 10GB. I was not able to find information to verify this. Although under Help->About the word Express does not occur. How can I verify the database limit information?
What can be the problem?
Is this something in my configurations I can change?
Answer :
You have to check the SERVERPROPERTY ('Edition') AS ServerEdition
to find if your installed instance is an Express edition or standard or enterprise.
You can use below T-SQL :
SELECT
SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
SERVERPROPERTY ('ServerName') AS SQLServerName,
SERVERPROPERTY ('Edition') AS ServerEdition,
CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + '
+ CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
+ CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion
Another variation that tells you the database size limit based on the version (note that -1 means unlimited)
SELECT
SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
SERVERPROPERTY ('ServerName') AS SQLServerName,
SERVERPROPERTY ('Edition') AS ServerEdition,
CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + '
+ CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build '
+ CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion
, case when cast(SERVERPROPERTY ('Edition') as varchar(255)) like '%express%'
then
case
when (
CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) >=10 -- major
and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) in (0,50) -- minor
) then 10 -- this is sql server 2008R2 and up
when (
CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) <= 10
and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) < 50
) then 4 -- this is sql server 2008 and less
end
else -1
end as EditionLimit
Starting sql server 2008R2, Express edition database limit was increased from 4GB to 10GB.