Datafile Spaceusage

Liefert die Datafile Auslastung aller Datenbanken einer SQL-Server Instanz
 
DECLARE @PGSize_Byte int
DECLARE @DBName nvarchar(100)
DECLARE @SQLString nvarchar(4000);
DECLARE @Tab_DBSpaces TABLE (
      Instanz varchar(256),
      DBName varchar(256),
      name varchar(256),
      type_desc varchar(256),
      filename varchar(256),
      FileSizeMB varchar(50),
      SpaceUsedMB varchar(50),
      FreeSpaceMB varchar(50),
      NextMB varchar(50),
      is_percent_growth tinyint,
      MaxSizeMB varchar(50)
)
 
SELECT @PGSize_Byte = low FROM master..spt_values WHERE number = 1 AND type = 'E'
 
DECLARE Cur_DB Cursor fast_forward for
Select name from sys.databases where state = 0 order by name
 
OPEN Cur_DB
Fetch NEXT FROM Cur_DB into @DBName
 
WHILE @@FETCH_STATUS = 0
BEGIN
      set @SQLString = N'USE [' + @DBName + ']
      select
      @@SERVERNAME
      ,''' + @DBName +''' as DBName
    , a.name
      , b.type_desc
      , filename
      , replace(convert(decimal(12,2),round(a.size/128.000,2)),''.'','','') as FileSizeMB
      , replace(convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),''.'','','') as SpaceUsedMB
      , replace(convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)),''.'','','') as FreeSpaceMB
      , replace(convert(decimal(12,2),CASE WHEN b.is_percent_growth = 0 THEN round(b.growth/128.000,2) ELSE (round(a.size/128.000,2)) * b.growth / 100.00 END),''.'','','') as NextMB
      , b.is_percent_growth
      , replace(convert(decimal(12,2),round(a.maxsize/128.000,2)),''.'','','') as MaxSizeMB
      from dbo.sysfiles a
      inner join sys.database_files b
            on a.fileid = b.file_id'
     
      INSERT INTO @Tab_DBSpaces
      execute sp_executesql @SQLString
     
      Fetch NEXT FROM Cur_DB into @DBName
END
 
CLOSE Cur_DB
DEALLOCATE Cur_DB
 
 
Select * from @Tab_DBSpaces
 
 
Ergebnis:
 

Instanz	DBName	name	type_desc	filename	FileSizeMB	SpaceUsedMB	FreeSpaceMB	NextMB	is_percent_growth	MaxSizeMB
LOCALHOST	DM_Main	DM_Main	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DM_Main.mdf	10,25	8,69	1,56	1,00	0	-               0,01
LOCALHOST	DM_Main	DM_Main_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DM_Main_log.ldf	14,69	1,94	12,75	1,47	1	2.097.152,00
LOCALHOST	DWH	Data01	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DWH_Data01.mdf	15,00	8,75	6,25	1,00	0	-               0,01
LOCALHOST	DWH	Log01_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\DWH_Log01.ldf	14,69	1,77	12,91	1,47	1	2.097.152,00
LOCALHOST	master	master	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\master.mdf	4,00	2,94	1,06	0,40	1	-               0,01
LOCALHOST	master	mastlog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\mastlog.ldf	1,25	0,55	0,70	0,13	1	-               0,01
LOCALHOST	model	modeldev	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\model.mdf	2,25	1,44	0,81	1,00	0	-               0,01
LOCALHOST	model	modellog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\modellog.ldf	0,75	0,28	0,47	0,08	1	-               0,01
LOCALHOST	msdb	MSDBData	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\MSDBData.mdf	28,94	27,31	1,63	2,89	1	-               0,01
LOCALHOST	msdb	MSDBLog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\MSDBLog.ldf	5,56	1,57	3,99	0,56	1	2.097.152,00
LOCALHOST	ReportServer	ReportServer	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServer.mdf	3,25	3,25	-	1,00	0	-               0,01
LOCALHOST	ReportServer	ReportServer_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServer_log.LDF	0,81	0,34	0,47	0,08	1	2.097.152,00
LOCALHOST	ReportServerTempDB	ReportServerTempDB	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServerTempDB.mdf	2,25	1,50	0,75	1,00	0	-               0,01
LOCALHOST	ReportServerTempDB	ReportServerTempDB_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\ReportServerTempDB_log.LDF	0,81	0,49	0,32	0,08	1	2.097.152,00
LOCALHOST	Stage	Stage	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Stage.mdf	9.837,25	286,81	9.550,44	1,00	0	-               0,01
LOCALHOST	Stage	Stage_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Stage_log.ldf	894,88	14,34	880,54	89,49	1	2.097.152,00
LOCALHOST	tempdb	tempdev	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\tempdb.mdf	8,00	2,88	5,13	0,80	1	-               0,01
LOCALHOST	tempdb	templog	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\templog.ldf	0,75	0,55	0,20	0,08	1	-               0,01
LOCALHOST	Test	Test	ROWS	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Test.mdf	170,00	169,13	0,88	1,00	0	-               0,01
LOCALHOST	Test	Test_log	LOG	C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV\MSSQL\DATA\Test_log.ldf	2,75	0,59	2,16	0,28	1	2.097.152,00