Script to get data file size, used space and free space

5 Replies

While troubleshooting the disk space issue, you can use below script to check the data file size, used space and free size.

select 
		DBName,
		name,
		[filename],
		size as 'Size(MB)',
		usedspace as 'UsedSpace(MB)',
		(size - usedspace) as 'AvailableFreeSpace(MB)'
from		
(	
SELECT
db_name(s.database_id) as DBName,
s.name AS [Name],
s.physical_name AS [FileName],
(s.size * CONVERT(float,8))/1024 AS [Size],
(CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, 'SpaceUsed') AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
) DBFileSizeInfo

5 thoughts on “Script to get data file size, used space and free space”

  2. Greg

    …The select should have capital letters for the variables to match the names in brackets.
    select
    DBName,
    Name,
    [Filename],
    Size as ‘Size(MB)’,
    Usedspace as ‘UsedSpace(MB)’,
    (Size – Usedspace) as ‘AvailableFreeSpace(MB)’
    from
    (
    SELECT
    db_name(s.database_id) as DBName,
    s.name AS [Name],
    s.physical_name AS [FileName],
    (s.size * CONVERT(float,8))/1024 AS [Size],
    (CAST(CASE s.type WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(s.name, ‘SpaceUsed’) AS float)* CONVERT(float,8) END AS float))/1024 AS [UsedSpace],
    s.file_id AS [ID]
    FROM
    sys.filegroups AS g
    INNER JOIN sys.master_files AS s ON ((s.type = 2 or s.type = 0) and s.database_id = db_id() and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
    ) DBFileSizeInfo

    Reply ↓
  3. Aijaz Ali

    Good one Jugal! I still need to use this query for my SQL 2000 databases. This is also handy if for some reason you are not able to see the Disk Usage reports

    Reply ↓
  4. Marco

    Looks like that calculates the used size only for the database in the current db context? ALl others are NULL.

    Reply ↓

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Google photo

You are commenting using your Google account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s