T-Sql

Ever wanted to know how to view available space in your database files? This script will give you the answer.

Uncomment out the last line to just view the log file.

sql

SELECT * FROM users WHERE age > 30;

USE HDNet
GO

SELECT 
    @@Servername AS instance_name,
    sd.name AS db_name, 
    sd.recovery_model_desc AS recovery_model,
    sdf.physical_name AS filename,
    sdf.name AS logical_name,
    (FILEPROPERTY(sdf.name, 'IsPrimaryFile')) AS primary_file,
    (FILEPROPERTY(sdf.name, 'IsLogFile')) AS is_log_file,
    CONVERT(DECIMAL(10,2), size/128.0/1024)AS size_GB,
    CONVERT(DECIMAL(10,2), FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024) AS space_used,
    CONVERT(DECIMAL(10,2), size/128.0/1024-(FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024)) AS space_availabile,
    CONVERT(DECIMAL(10,2), (FILEPROPERTY(sdf.name, 'SpaceUsed')/(size/128.0))/128.0*100) AS pct_used,
    sd.log_reuse_wait_desc
FROM sys.database_files AS sdf
CROSS APPLY
(
    SELECT * FROM sys.databases AS sdf
    WHERE name = DB_NAME()
) AS sd








--WHERE (FILEPROPERTY(sdf.name, 'IsLogFile')) = 1

space here

sql

SELECT * FROM users WHERE age > 30;

USE HDNet
GO

SELECT 
    @@Servername AS instance_name,
    sd.name AS db_name, 
    sd.recovery_model_desc AS recovery_model,
    sdf.physical_name AS filename,
    sdf.name AS logical_name,
    (FILEPROPERTY(sdf.name, 'IsPrimaryFile')) AS primary_file,
    (FILEPROPERTY(sdf.name, 'IsLogFile')) AS is_log_file,
    CONVERT(DECIMAL(10,2), size/128.0/1024)AS size_GB,
    CONVERT(DECIMAL(10,2), FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024) AS space_used,
    CONVERT(DECIMAL(10,2), size/128.0/1024-(FILEPROPERTY(sdf.name, 'SpaceUsed')/128.0/1024)) AS space_availabile,
    CONVERT(DECIMAL(10,2), (FILEPROPERTY(sdf.name, 'SpaceUsed')/(size/128.0))/128.0*100) AS pct_used,
    sd.log_reuse_wait_desc
FROM sys.database_files AS sdf
CROSS APPLY
(
    SELECT * FROM sys.databases AS sdf
    WHERE name = DB_NAME()
) AS sd










--WHERE (FILEPROPERTY(sdf.name, 'IsLogFile')) = 1

T-Sql

Free Space in SQL Files

test