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
If i have 3 differents database in a same server…??? I tried to run this scrpt per instances?
…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
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
Looks like that calculates the used size only for the database in the current db context? ALl others are NULL.
Excellent served my purpose