Check all database size and Free Space in SQL Server

To Check all database size and there free space with  respect to each file you can use below query.


exec sp_msforeachdb
'use [?];
  select DB_NAME() AS DbName, [name], file_id, physical_name,
[size]/128 AS ''Total Size in MB'',
[size]/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS ''Available Space In MB'',
CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS ''Used Space In MB'',
(100-((([size]/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128)/([size]/128))*100)) AS ''Percentage Used''
FROM sys.database_files '



The only issue is with query is if you have database size in few MB’s if will throw divide by zero



No comments:

Post a Comment