[Script] Obtendo tamanho das bases de dados detalhadamente

Olá pessoal,

Mais uma vez aqui compartilhando scripts com vocês. Dessa vez o script que vou passar é para vocês pegarem o tamanho de suas bases de dados, porem com algumas outras informações como: Tamanho total, espaço utilizado, espaço disponivel e disco de localização dos arquivos (mdf e ldf). Vale lembrar que esse script apenas pega o tamanho do arquivo .mdf e .ndf, caso você desejar pegar a informação também para o arquivo .ldf, você precisa retirar a clausula “WHERE   df.type = 0”. Abaixo segue o script, espero que todos gostem.

CREATE TABLE #tblDatabaseSizeInformation
    (
      NAME SYSNAME ,
      SpaceAvailable DECIMAL(10, 2) ,
      SpaceUsed DECIMAL(10, 2) ,
      DatabaseSize DECIMAL(10, 2)
    )

EXEC master.sys.sp_MSForEachDB ' use ? 
INSERT INTO #tblDatabaseSizeInformation
        ( NAME ,
          SpaceAvailable ,
          SpaceUsed ,
          DatabaseSize
        ) SELECT  db.NAME, 
SUM(CAST(( ( mf.size * 8. ) / 1024. ) AS DECIMAL(10, 2)))
- SUM(CAST(( ( FILEPROPERTY(mf.name, ''SpaceUsed'') * 8. ) / 1024 ) AS DECIMAL(10,
                                                   2))) AS SpaceAvailable ,
SUM(CAST(( ( FILEPROPERTY(mf.name, ''SpaceUsed'') * 8. ) / 1024. ) AS DECIMAL(10,
                                                   2))) AS SpaceUsed ,
SUM(CAST(( ( mf.size * 8. ) / 1024. ) AS DECIMAL(10, 2))) AS DatabaseSize
FROM    sys.database_files AS df
JOIN sys.master_files AS mf ON df.name = mf.name COLLATE Latin1_General_CS_AS
JOIN sys.databases as db on mf.database_id = db.database_id
WHERE   df.type = 0
GROUP BY db.name'

Nessa primeira parte o script apenas realiza a varredura das bases de dados capturando as informações de espaço. O script abaixo le os dados e gera a saida necessaria.

select distinct tblDbInf.NAME, tblDbInf.DatabaseSize,
tblDbInf.SpaceUsed,tblDbInf.SpaceAvailable,
'(' + (select top(1) left(ms.physical_name,2) 
from sys.master_files JOIN sys.databases as db
 on tblDbInf.NAME = db.name join sys.master_files as ms
 on db.database_id = ms.database_id
 where ms.file_id = 1) +')' + ' mdf location / ' + '( '+  
( select top(1) left(ms.physical_name,2) 
from sys.master_files JOIN sys.databases as db
on tblDbInf.NAME = db.name
 join sys.master_files as ms
 on db.database_id = ms.database_id where ms.file_id = 2)+ ')' 
+ ' ldf location' as FileLocation
from #tblDatabaseSizeInformation as tblDbInf
JOIN sys.databases as db
 on tblDbInf.NAME = db.name
 join sys.master_files as ms
 on db.database_id = ms.database_id

O script gera então a seguinte saida.

image

O resultado pode ser muito útil se colocado em uma planilha no excel como esse exemplo logo abaixo.

image

Espero que tenham gostado e por hoje é isso.

Abraços,
Marcos Freccia

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em abril 22, 2013, em Dicas, Scripts, SQL Server, T-SQL, VirtualPass e marcado como , , , . Adicione o link aos favoritos. 4 Comentários.

  1. Rapaz, gostei do script, vai para minha galeria de scripts matadores

  2. Marcos,
    nessa parte do script você faz referência á tabela sys.master_files duas vezes e no ON do join, você liga a tabela temporária sem citá-la antes da sys.databases, é isso mesmo?
    from sys.master_files JOIN sys.databases as db
    on tblDbInf.NAME = db.name join sys.master_files as ms

    Falo isso porque pra mim deu produto cartesiano.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: