To get a list of the databases files, and the current disk size in MB, and the Free Space in MB in each fil, you can use this commands
|
1 2 3 4 5 6 7 |
USE [DATABASE_NAME] SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files; |
If you need a overview of all yours database this TSQL can be used
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE #FileSize (dbName NVARCHAR(128), FileName NVARCHAR(128), type_desc NVARCHAR(128), CurrentSizeMB DECIMAL(10,2), FreeSpaceMB DECIMAL(10,2) ); INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'USE [?]; SELECT DB_NAME() AS DbName, name AS FileName, type_desc, size/128.0 AS CurrentSizeMB, size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);'; SELECT * FROM #FileSize WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb'); DROP TABLE #FileSize; |