If you try to insert data into a database and it is full, it will throw an exception. Obviously this isn't ideal, so you want to be monitoring the database size. The best way to do this is to query the system views to find out how large your database is.
This query:
SELECT *
FROM sys.database_files
Will return the details about the current database you connected to. There is a column called
size
which has the current allocated size of your database in kilobytes. Remember that SQL Server will allocate
x amount of database space so you will have free space within the file, even though it has hit the max size.
I wrote this query to help show how much space is available in a database file. You need to use the function
FILEPROPERTY
and pass the value "SpaceUsed". With this query, I also convert all of the sizes into gigabytes.
SELECT
name
,[filename]
,CONVERT(DECIMAL(12, 2), ROUND((size / 128.000) / 1024, 2)) AS [File Size in GB]
,CONVERT(DECIMAL(12, 2), ROUND((FILEPROPERTY(name, 'SpaceUsed') / 128.000)
/ 1024, 2)) AS [Space Used in GB]
,CONVERT(DECIMAL(12, 2), ROUND(((size - FILEPROPERTY(name, 'SpaceUsed'))
/ 128.000) / 1024, 2)) AS [Free Space in GB]
,CONVERT(DECIMAL(4, 1), ROUND(CASE WHEN CONVERT(DECIMAL(12, 2), ROUND((size
/ 128.000)
/ 1024, 2)) < 1
THEN 100.00
ELSE CONVERT(DECIMAL(12, 2), ROUND(((size
- FILEPROPERTY(name,
'SpaceUsed'))
/ 128.000)
/ 1024, 2))
/ CONVERT(DECIMAL(12, 2), ROUND((size
/ 128.000)
/ 1024, 2))
* 100
END, 1)) AS [Percent Free]
FROM
sys.sysfiles