Labels

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''a
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:
Write comments

Please do not enter spam links

Services

More Services