Este script SQL permite exibir informações detalhadas sobre o tamanho dos arquivos de dados e arquivos de log de todas as bases de dados em um servidor SQL Server. Ele é extremamente útil para monitoramento e gestão de espaço em disco utilizado por cada base de dados. A seguir, você encontrará uma explicação completa sobre o funcionamento do script, seguido de um exemplo prático.
📊 Objetivo e Funcionamento
O script tem como objetivo apresentar o tamanho total de dois tipos de arquivos dentro do SQL Server:
- Arquivos de Dados (tipo 0)
- Arquivos de Log (tipo 1)
A consulta sys.master_files é usada para obter informações detalhadas sobre os tamanhos dos arquivos. O script calcula os tamanhos dos arquivos e retorna os valores em megabytes (MB).
Como Funciona:
- A consulta DBSIZE calcula o tamanho dos arquivos de dados e log por base de dados.
- Em seguida, a consulta principal usa sys.databases para listar todas as bases de dados no servidor.
- O script retorna:
- Nome da Base de Dados
- Tamanho Total do Arquivo de Dados (MB)
- Tamanho Total do Arquivo de Log (MB)
A consulta retorna os resultados classificados por tamanho de arquivo de dados em ordem decrescente.
🔍 Exemplo de Script:
WITH DBSIZE AS
(
SELECT
database_id,
type,
SUM(size * 8.0/1024) AS size
FROM
sys.master_files
GROUP BY
database_id, type
)
SELECT
d.name AS DB,
SUM(CASE WHEN ds.type = 0 THEN ds.size ELSE 0 END) AS 'DataFileSize(MB)',
SUM(CASE WHEN ds.type = 1 THEN ds.size ELSE 0 END) AS 'LogFileSize(MB)'
FROM
sys.databases d
LEFT JOIN DBSIZE ds
ON d.database_id = ds.database_id
GROUP BY
d.name
ORDER BY 2 DESC;
🧑💻 Explicação do Script:
-
WITH DBSIZE: Cria uma CTE (Common Table Expression) chamada DBSIZE que agrupa os arquivos de dados e log por database_id e tipo. A consulta retorna o tamanho dos arquivos em MB.
SUM(size * 8.0 / 1024)converte o tamanho do arquivo de páginas (tamanho em KB) para MB.
-
SELECT Principal:
- d.name AS DB: Exibe o nome da base de dados.
- SUM(CASE WHEN ds.type = 0 THEN ds.size ELSE 0 END) AS 'DataFileSize(MB)': Soma o tamanho de todos os arquivos de dados para cada base de dados.
- SUM(CASE WHEN ds.type = 1 THEN ds.size ELSE 0 END) AS 'LogFileSize(MB)': Soma o tamanho de todos os arquivos de log para cada base de dados.
-
ORDER BY 2 DESC: Ordena o resultado pelo tamanho do arquivo de dados de forma decrescente.
⚙️ Uso Prático e Benefícios:
Esse tipo de script é fundamental para administradores de banco de dados que precisam:
- Monitorar o uso de espaço em disco em tempo real.
- Verificar o crescimento de arquivos de log, ajudando a gerenciar o crescimento e evitar que o log de transações ocupe espaço excessivo.
- Auditar bases de dados e avaliar rapidamente os requisitos de armazenamento.
📈 Considerações Finais:
O script pode ser adaptado para atender a diferentes necessidades, como filtrar bases de dados específicas ou calcular o espaço utilizado por tipos de arquivos diferentes. Por exemplo, você pode modificar a consulta para incluir arquivos de tipos diferentes ou até mesmo integrar com outras métricas de desempenho para um relatório mais robusto.
Este tipo de relatório é uma das várias boas práticas para manter o desempenho e a saúde do seu banco de dados, garantindo que você esteja sempre ciente do espaço utilizado e da necessidade de manutenção.
🚀 Conclusão
Utilizar scripts como o acima é uma excelente maneira de monitorar a saúde do banco de dados no SQL Server, sendo uma ferramenta crucial para a gestão de recursos e a prevenção de problemas relacionados ao espaço em disco.