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?
Best Answer
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/