1

I have a Microsoft SQL Server 2017 Express Edition those have a size limit of 10240MB (10Gigs) and I am not sure how I can check how much space I have left in this license. I have tried the following:

SELECT DB_NAME(database_id) AS database_name, 
    type_desc, 
    name AS FileName, 
    size/128.0 AS CurrentSizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'DemoDB' AND type IN (0,1);

This outputs the following:

database_name type_desc FileName CurrentSizeMB
DemoDB ROWS DemoDB 10229.687500
DemoDB LOG DemoDB_log 8217.000000

Is it the upper value CurrentSizeMB 10229.687500 or do I need to use another query?

1
  • 1
    size, per the documentation, is the "Current file size, in 8-KB pages.", so size/128.0 would be the current size of the data file in question in MiB. – Larnu Feb 23 at 14:42
0

You can utilize the below to see how much free space is left in the data file(s) as well as how much you've already used. The limit for data files in Express edition is 10 GBs currently(https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15)

The code is based off of: https://am2.co/2016/04/shrink-database-4-easy-steps/

USE DemoDB
GO

SELECT
DB_NAME(),
dbf.name AS LogicalName,
dbf.type_desc AS FileType,
fg.name AS FileGroupName,
dbf.physical_name AS PhysicalFileLocation,
CAST(  (dbf.size / 128.0)  / 1024.0 AS DEC(10,2)) AS FileSizeGB,
CAST(  (FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024 AS DEC(10,2))  AS UsedSpaceGB,
CAST(   ((dbf.size / 128.0)  / 1024.0) - ((FILEPROPERTY(dbf.name, 'SPACEUSED')  / 128.0)  /1024) AS DEC(10,2)) AS FreeSpaceGB 

FROM
sys.database_files dbf
LEFT JOIN sys.filegroups fg
ON dbf.data_space_id = fg.data_space_id


ORDER BY
FileType DESC,
FileGroupName,
LogicalName
1
  • I did some googeling and found, that sys.master_files (the table I used) is not an accurate representation of the used space, as it only holds the free space of the last startup of the server. sys.database_files (in your query) on the other hand shows real time data. – I dunno Feb 26 at 7:08

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.