SELECT name AS FileName,
FILE_ID,
physical_name,
type_desc,
size / 128 AS CurrentSize_MB,
growth / 128 AS Growth_MB,
max_size / 128 [maxsize_MB],
size / 128-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128 AS FreeSpace_MB,
ROUND(CAST(((size / 128.0-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)*100)/(size / 128.0) AS DECIMAL(10, 2)), 2) AS [%Free],
'DBCC SHRINKFILE ('''+name+''' , '+CAST(size / 128-((size / 128-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128)-128) AS VARCHAR(10))+')' AS FileName
FROM sys.database_files
ORDER BY 1;
FILE_ID,
physical_name,
type_desc,
size / 128 AS CurrentSize_MB,
growth / 128 AS Growth_MB,
max_size / 128 [maxsize_MB],
size / 128-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128 AS FreeSpace_MB,
ROUND(CAST(((size / 128.0-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0)*100)/(size / 128.0) AS DECIMAL(10, 2)), 2) AS [%Free],
'DBCC SHRINKFILE ('''+name+''' , '+CAST(size / 128-((size / 128-CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128)-128) AS VARCHAR(10))+')' AS FileName
FROM sys.database_files
ORDER BY 1;
Comments
Post a Comment