Script – Informações do Tamanho das Bases

Olá, O codigo a seguir tem a finalidade de capturar o tamanho atual das bases (levando em conta apenas os arquivos de dado). Uma ótima utilidade para o script é persistir essas informações em um repositório pra depois fazer outras análises (Capacity Plan, por exemplo), por isso adicionalmente, existe um campo de data na saída da variável. Segue script:

 -- Cria variável de tabela

DECLARE @DBSize AS TABLE
(
      DBName varchar(100),
      DBStatus varchar(20),
      Recovery_Model varchar(100),
      Database_Path varchar(500),
      File_Size float,
      Space_Used float,
      Free_Space float,
      File_Size_String Varchar(100) NULL,
      Space_Used_String Varchar(100) NULL,
      Free_Space_String Varchar(100) NULL,
	  Total_Space_MB Varchar(100) NULL
)

INSERT INTO @DBSize(Dbname, DBStatus, Recovery_Model, Database_Path, file_Size, Space_Used, Free_Space)
EXEC SP_MSFOREACHDB
'USE [?];
	SELECT
		DB_NAME() AS DbName,
		CONVERT(varchar(20),DatabasePropertyEx(''?'',''Status'')) ,
		CONVERT(varchar(20),DatabasePropertyEx(''?'',''Recovery'')),
		physical_name,
		size*8.00 AS File_Size,
		FILEPROPERTY(name, ''SpaceUsed'')*8.00 as Space_Used,
		size*8.00 - FILEPROPERTY(name, ''SpaceUsed'')*8.00 AS Free_Space
	FROM sys.database_files  where type=0'

--> Atualiza campos nulos com sufixo de espaço

UPDATE @DBSize SET
      File_Size_String =
            CASE
                  WHEN File_Size/1024.00 > 1000 THEN Convert(Varchar(50),Convert(decimal(20,2),(File_Size/1024/1024.00))) + ' GB'
                  ELSE Convert(Varchar(50),Convert(decimal(20,2),(File_Size/1024.00))) + ' MB'
            END,
      Space_Used_String =
            CASE
                  WHEN Space_Used/1024.00 > 1000 THEN Convert(Varchar(50),Convert(decimal(20,2),(Space_Used/1024/1024.00))) + ' GB'
                  ELSE Convert(Varchar(50),Convert(decimal(20,2),(Space_Used/1024.00))) + ' MB'
            END,
      Free_Space_String =
            CASE
                  WHEN Free_Space/1024.00 > 1000 THEN Convert(Varchar(50),Convert(decimal(20,2),(Free_Space/1024/1024.00))) + ' GB'
                  ELSE Convert(Varchar(50),Convert(decimal(20,2),(Free_Space/1024.00))) + ' MB'
            END,
	  Total_Space_MB =  Convert(decimal(20,2),(File_Size/1024.00))

--> Obs: Campo de Data puramente opcional

SELECT
	DBName as [Database] ,
	DBStatus,
	Recovery_Model,
	File_Size_String,
	Space_Used_String,
	Free_Space_String,
	Total_Space_MB,
	Database_Path,
	GETDATE() AS Data
FROM @DBSize
ORDER BY File_Size DESC, Space_Used DESC, Free_Space DESC

Exemplo de saída

: