T-SQL: How to know how much storage space is left in sql server express?

Question

How is it possible to know how much of the "DB quota" imposed by SQL Server express edition (4GB in old versions, 10GB in 2008R2) is in use?

Let's say I have a DB that is using 1GB, I want to know "9GB of free space".

Is it there a T-SQL command for this?

Answer 1

There is a command, sp_spaceused that will get you the total size, you could write some code that returns X - used to get the results, but there is not a command in TSQL that I am aware of that returns "9Gb left...."

Answer 2

You can look at the documentation from MSFT to see the maximum allowed for your edition of SQL express. After that you may look at sys databases to see how much your database occupies and then subtract it from the max value. Here is the pseudo SQL code.

Select max_space - (select used space for your db) as freespace