SQL Server Express 2012 – Database Size Limit Explained

sql-server-express

I checked the forum there are other similar topics but my scenario is different.
for myDatabase under database properties -> General tab it shows:

Size : 25285.31 MB
Space Available: 3989.13 MB

And on disk 

myDB.mdf = 10.0 GB (10,745,806,848 bytes)
myDB_log.ldf = 14.6 GB (15,767,764,992 bytes)

As per my understanding only 10GB is allowed in Expres edition but here it seems more. What is the space available parameter from DB properties.

Best Answer

SQL Server Express only imposes file size limits on data files, log files can grow to any size. The Size shown on the database properties window is the data and log file combined sizes.

Somewhat confusingly though, the Free Space value shown on this window only relates to the data file(s). If you'd like to see this in more detail, I'd recommend querying the system tables rather than relying on the SSMS GUI (which isn't always going to give you the information you want/need). The following script will show some more detailed information about data file sizes/growth settings:

SELECT 
    [TYPE] = DF.TYPE_DESC
    ,[FILE_Name] = DF.name
    --,[FILEGROUP_NAME] = FG.name
    ,[File_Location] = DF.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((DF.SIZE/128.0 - CAST(FILEPROPERTY(DF.NAME, 'SPACEUSED') AS INT)/128.0)/(DF.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' 
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END 
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' 
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END 
        + CASE is_percent_growth WHEN 1 THEN ' [WARNING: Autogrowth by percent]' ELSE '' END
FROM sys.database_files DF
LEFT JOIN sys.filegroups FG
ON DF.data_space_id = FG.data_space_id 
order by DF.TYPE desc, DF.NAME;

As an aside: Since your data file is at 10GB already, future growth operations will fail (and whatever transaction triggered the growth will generate a Primary Filegroup Full error). Since the file is set to 10GB exactly, I suspect that it has been grown out to this amount deliberately to take advantage of the full amount of space offered by SQL Express.

Related Question