SQL Database file size script

SQL Administratoren benötigen oft detaillierte Informationen zu verschiedenen Datenbanken, wie Z.B. Used Space einer Datenbank oder einer Log Datei. 

Um mit geringem Aufwand diese Informationen zu sammeln sollte man dieses SQL Script ausführen. (Kompatibel ab SQL Server 2005)

Als Output erhält man folgende Tabelle:

DatabaseName, LogicalFileName, RecoveryMode, FileSizeMB, UsedSpaceMB, FreeSpaceMB, FreeSpacePct, PhysicalFileName, Status

SQL-Script:

DECLARE @DBInfo TABLE 
( ServerName VARCHAR(100), 
DatabaseName VARCHAR(100), 
FileSizeMB INT, 
LogicalFileName sysname, 
PhysicalFileName NVARCHAR(520), 
Status sysname, 
Updateability sysname, 
RecoveryMode sysname, 
FreeSpaceMB INT, 
UsedSpaceMB INT,
FreeSpacePct VARCHAR(7), 
FreeSpacePages INT, 
PollDate datetime) 
 
DECLARE @command VARCHAR(5000) 
 
SELECT @command = 'Use [' + '?' + '] SELECT 
@@servername as ServerName, 
' + '''' + '?' + '''' + ' AS DatabaseName, 
CAST(sysfiles.size/128.0 AS int) AS FileSize, 
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability, 
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode, 
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 
       'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(FILEPROPERTY(sysfiles.name, ' + '''' + 
       'SpaceUsed' + '''' + ' )/128.0 AS int) AS UsedSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name, 
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0)) 
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct, 
GETDATE() as PollDate FROM dbo.sysfiles' 
INSERT INTO @DBInfo 
   (ServerName, 
   DatabaseName, 
   FileSizeMB, 
   LogicalFileName, 
   PhysicalFileName, 
   Status, 
   Updateability, 
   RecoveryMode, 
   FreeSpaceMB,
   UsedSpaceMB,
   FreeSpacePct, 
   PollDate) 
EXEC sp_MSForEachDB @command 
 
SELECT 
   --ServerName, 
   DatabaseName, 
   LogicalFileName,
   RecoveryMode,
   FileSizeMB, 
   UsedSpaceMB, 
   FreeSpaceMB,
   FreeSpacePct, 
   PhysicalFileName, 
   Status
   --Updateability, 
   --PollDate 
FROM @DBInfo 
--WHERE LogicalFileName NOT LIKE '%_log'
ORDER BY 
   ServerName, 
   DatabaseName