Monitoramento de Crescimento de Tabelas em Todos os Bancos do SQL Server

Introdução

Em ambientes corporativos é comum o crescimento constante dos bancos de dados. Monitorar o tamanho das tabelas permite antever problemas de performance e de armazenamento, além de auxiliar na tomada de decisões para otimização de dados.

Este script realiza um monitoramento automatizado, registrando o tamanho de todas as tabelas de todos os bancos de dados (exceto os de sistema) em uma tabela centralizada.

Objetivo

Criar um histórico com informações de tamanho de todas as tabelas dos bancos online, gravando os dados em uma tabela localizada no banco DBManager.

Etapas do Script

1. Criação do banco de monitoramento

O script verifica se o banco DBManager existe e o cria, caso não exista:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DBManager')
BEGIN
    CREATE DATABASE DBManager;
END

2. Criação da tabela de histórico

Se a tabela table_size_history ainda não existir, ela será criada para armazenar os dados de todas as tabelas monitoradas:

CREATE TABLE table_size_history (
    database_name NVARCHAR(128),
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
    row_count BIGINT,
    table_size_mb DECIMAL(18,2),
    index_size_mb DECIMAL(18,2),
    total_size_mb DECIMAL(18,2)
);

3. Execução de cursor sobre todos os bancos online

Um cursor percorre todos os bancos online (exceto os de sistema) e monta dinamicamente um comando SQL para consultar o tamanho de todas as tabelas do banco atual.

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE state = 0
AND name NOT IN ('master', 'tempdb', 'model', 'msdb')
ORDER BY name;

O SQL dinâmico dentro do cursor extrai:

• Nome do banco, esquema e tabela

• Quantidade de linhas

• Tamanho de dados (MB)

• Tamanho de índice (MB)

• Tamanho total (MB)

4. Tratamento de erros

Erros na execução são tratados individualmente por banco:

BEGIN TRY
    EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
    PRINT 'Erro ao processar o banco ' + @dbname + ': ' + ERROR_MESSAGE();
END CATCH

5. Consulta final

Após a coleta, os dados podem ser consultados com:

SELECT * FROM table_size_history
ORDER BY database_name, schema_name, table_name;

Conclusão

Este script é uma solução prática para DBAs e analistas que precisam de um controle rápido e eficiente do crescimento de tabelas em um ambiente com vários bancos SQL Server.

Para automatizar a execução, recomenda-se criar um SQL Server Agent Job que execute o script diariamente

Script completo

/****** Object: Script de Historico de Crescimento de Tabela em Todos os bancos V1 Date: 29/04/2025 ******

EMPRESA: CLOUDDB

SITE: https://www.clouddb.com.br/

***************************************************************************************/

--Verifica/Cria a tabela de armazenamento no banco DBManager

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'DBManager')

BEGIN

CREATE DATABASE DBManager;

END

GO

USE [DBManager]

GO

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'table_size_history' AND schema_id = SCHEMA_ID('dbo'))

BEGIN

-- Cria uma tabela temporária para armazenar os resultados

CREATE TABLE table_size_history (

database_name NVARCHAR(128),

schema_name NVARCHAR(128),

table_name NVARCHAR(128),

row_count BIGINT,

table_size_mb DECIMAL(18,2),

index_size_mb DECIMAL(18,2),

total_size_mb DECIMAL(18,2)

);

END

-- Gera e executa o SQL dinâmico para cada banco de dados

DECLARE @sql NVARCHAR(MAX);

DECLARE @dbname NVARCHAR(128);

DECLARE db_cursor CURSOR FOR

SELECT name FROM sys.databases

WHERE state = 0 -- apenas bancos online

AND name NOT IN ('master', 'tempdb', 'model', 'msdb') -- exclui bancos do sistema

ORDER BY name;

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = N'

USE [' + @dbname + N'];

INSERT INTO [DBManager].[dbo].[table_size_history]

SELECT

DB_NAME() AS database_name,

s.name AS schema_name,

t.name AS table_name,

p.rows AS row_count,

SUM(a.data_pages) * 8.0 / 1024 AS table_size_mb,

SUM(a.used_pages - a.data_pages) * 8.0 / 1024 AS index_size_mb,

SUM(a.total_pages) * 8.0 / 1024 AS total_size_mb

FROM sys.tables t

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

INNER JOIN sys.indexes i ON t.object_id = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

WHERE t.is_ms_shipped = 0

GROUP BY s.name, t.name, p.rows;';

BEGIN TRY

EXEC sp_executesql @sql;

END TRY

BEGIN CATCH

PRINT 'Erro ao processar o banco ' + @dbname + ': ' + ERROR_MESSAGE();

END CATCH

FETCH NEXT FROM db_cursor INTO @dbname;

END

CLOSE db_cursor;

DEALLOCATE db_cursor;

-- Exibe os resultados

SELECT * FROM table_size_history

ORDER BY database_name, schema_name, table_name;