6

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.

  • You can define your files as automatically growing or place them as fix sized files. It looks as if you have defined your DB as a 10GB file from the beginning. Find the limitations of v2012 Expr here – Shnugo Oct 22 '17 at 10:34
3

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.

| improve this answer | |
  • Thank you, the above query gives clear picture. Total file size limit is 10248 MB, and now around 2300MB is Freespace. Log file freespace is 99%. Picture: link – Nisar Afridi Jan 21 '18 at 11:50
2

SQL Server will allocate more disk space as your database grows. You may start with a 1GB but it will grow to 3GB over time. Since it is inefficient to re allocate for each record, it grows by x% or xMB (you can define these). The available space is how much empty disk space you can use before the next growth.

If for some reason you belive your databse had grown into unrealistic size, you can use DBCC SHRINKDATABASE(<YourDatabaseName>)

As for the express limit, I'm not sure exactly how it is calculated. But it is possible that most of your database size is the log file, especially if you have done lots of updates and deletes and you haven't done any backup.

You can check the log file size:

DBCC SQLPERF (LOGSPACE)

If you discover that the logfile is indeed oversized and you don't need the option for point in time recovery, you can change the recovery model to simple and that will reduce it's growth significantly

Recovery models: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

| improve this answer | |
1

The 'space available parameter from DB properties' you asked about is the unallocated space in the data file (mdf) and the transaction log file (ldf). The mdf file size is not always accurate as it contains unallocated data pages and (generally) does not shrink when data pages are no longer required.

A quick way to accurately find the used space is to run the SQL command:

exec sp_helpdb 'myDatabase';

This shows two result sets. In the second result set the first line shows the allocated size of the data pages in the 'size' column which is your most accurate estimate.

Edit: The 'maxsize' column shows the maxmum size the file is allowed to grow to, or can be 'unlimited' to allow the file to grow to use the entire disk. It's useful to set 'maxsize' to an actual number, then if you have multiple databases on one disk you can set the sizes to prevent one database from using up all the disk space.

However: the 10GB limit for SQL Server Express takes priority, so even if you have maxsize = Unlimited (or maxsize = 20GB or whatever) you would find the actual database size would not grow beyond 10GB.

| improve this answer | |
  • I ran this command and under primary it shows max size as unlimited. So what does this mean, db can grow unlimited with express edition? Picture: drive.google.com/open?id=1vnGXYLnE8h6JIvpJZNuIGC2nLXDwIQn5 – Nisar Afridi Jan 17 '18 at 11:08
  • Basically the SQL Server Express 10GB limit takes priority over the maxsize, so even if you set maxsize to 'Unlimited' or 20GB or whatever, it would still not grow beyond 10GB. I updated the answer to make it a bit clearer. – Ubercoder Jan 19 '18 at 16:07
  • Understood. Right now under database properties space available shows around 2500MB. but myDB.mdf file size is more than 10.0 GB (10,745,806,848 bytes). Convert 10GB to bytes, that is less than above number which is confusing. For me now i am looking into 2300Mb and before it reach max i will do the migration to enterprise. – Nisar Afridi Jan 21 '18 at 6:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.