How can I verify that my SQL Server edition is Express? [closed]

Posted on

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.

Leave a Reply

Your email address will not be published.