Nexus - AI Task Orchestrator

15-database-growth-analysis
Procedimiento: An├ílisis de Crecimiento de Base de Datos

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
12055 caracteres
# Procedimiento: An├ílisis de Crecimiento de Base de Datos

## Metadata
- **ID**: PROC-15
- **Frecuencia**: Semanal
- **Duraci├│n estimada**: 15-30 min
- **Requiere**: Acceso SQL Server (lectura DMV) o PostgreSQL (pg_stat)
- **Dependencias**: Ninguna
- **Bloquea**: Ninguno (solo lectura)
- **Agentes**: database-optimizer

---

## Objetivo

Analizar el crecimiento de la base de datos para:
1. Identificar tablas con crecimiento anormal
2. Proyectar necesidades de espacio futuras
3. Detectar posibles problemas de dise├▒o (tablas log sin purga, etc.)
4. Alertar antes de quedarse sin espacio

---

## Umbrales de Alerta

| M├®trica | WARNING | CRITICAL |
|---------|---------|----------|
| Espacio usado en disco | >70% | >85% |
| Crecimiento semanal | >10% | >25% |
| Tabla individual | >5GB y creciendo >20%/semana | >10GB y creciendo |
| D├¡as hasta lleno (proyecci├│n) | <60 d├¡as | <30 d├¡as |

---

## Checklist Ejecutable

### 1. Pre-verificaciones

- [ ] Conectar a la base de datos
- [ ] Verificar permisos de lectura en vistas de sistema

---

## Para SQL Server

### 2. Espacio Total de la Base de Datos

```sql
-- Tama├▒o actual de la base de datos
SELECT
    DB_NAME() AS database_name,
    SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8.0 / 1024 AS data_size_mb,
    SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8.0 / 1024 AS log_size_mb,
    SUM(size) * 8.0 / 1024 AS total_size_mb,
    SUM(FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS used_mb,
    SUM(size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 AS free_mb,
    CAST(100.0 * SUM(FILEPROPERTY(name, 'SpaceUsed')) / SUM(size) AS DECIMAL(5,2)) AS used_pct
FROM sys.database_files;

-- Espacio en disco del servidor
EXEC xp_fixeddrives;
```

- [ ] Documentar tama├▒o actual
- [ ] Verificar espacio libre en disco

### 3. Top 20 Tablas por Tama├▒o

```sql
-- Tablas m├ís grandes con detalle
SELECT TOP 20
    OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
    t.name AS table_name,
    p.rows AS row_count,
    SUM(a.total_pages) * 8 / 1024.0 AS total_mb,
    SUM(a.used_pages) * 8 / 1024.0 AS used_mb,
    SUM(a.data_pages) * 8 / 1024.0 AS data_mb,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024.0 AS unused_mb
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.object_id, t.name, p.rows
ORDER BY SUM(a.total_pages) DESC;
```

- [ ] Identificar tablas m├ís grandes
- [ ] Verificar si alguna es > 5GB

### 4. Tablas con Mayor Crecimiento Reciente

```sql
-- NOTA: Para tracking de crecimiento hist├│rico, se necesita tabla de snapshots
-- Crear si no existe:
/*
CREATE TABLE dbo.TableSizeHistory (
    snapshot_date DATE,
    schema_name NVARCHAR(128),
    table_name NVARCHAR(128),
    row_count BIGINT,
    size_mb DECIMAL(18,2),
    PRIMARY KEY (snapshot_date, schema_name, table_name)
);
*/

-- Insertar snapshot actual
INSERT INTO dbo.TableSizeHistory (snapshot_date, schema_name, table_name, row_count, size_mb)
SELECT
    CAST(GETDATE() AS DATE),
    OBJECT_SCHEMA_NAME(t.object_id),
    t.name,
    p.rows,
    SUM(a.total_pages) * 8 / 1024.0
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id IN (0, 1)
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0
GROUP BY t.object_id, t.name, p.rows;

-- Comparar con hace 7 d├¡as
SELECT
    h1.schema_name,
    h1.table_name,
    h2.size_mb AS size_7_days_ago,
    h1.size_mb AS size_today,
    h1.size_mb - ISNULL(h2.size_mb, 0) AS growth_mb,
    CASE WHEN h2.size_mb > 0
        THEN CAST(100.0 * (h1.size_mb - h2.size_mb) / h2.size_mb AS DECIMAL(10,2))
        ELSE NULL
    END AS growth_pct
FROM dbo.TableSizeHistory h1
LEFT JOIN dbo.TableSizeHistory h2
    ON h1.schema_name = h2.schema_name
    AND h1.table_name = h2.table_name
    AND h2.snapshot_date = DATEADD(DAY, -7, h1.snapshot_date)
WHERE h1.snapshot_date = CAST(GETDATE() AS DATE)
ORDER BY h1.size_mb - ISNULL(h2.size_mb, 0) DESC;
```

- [ ] Guardar snapshot de hoy
- [ ] Comparar con snapshot anterior
- [ ] Identificar tablas con crecimiento > 10%

### 5. Proyecci├│n de Crecimiento

```sql
-- Proyecci├│n simple basada en ├║ltimos 30 d├¡as
WITH GrowthData AS (
    SELECT
        schema_name,
        table_name,
        MIN(size_mb) AS size_30_days_ago,
        MAX(size_mb) AS size_today,
        DATEDIFF(DAY, MIN(snapshot_date), MAX(snapshot_date)) AS days
    FROM dbo.TableSizeHistory
    WHERE snapshot_date >= DATEADD(DAY, -30, GETDATE())
    GROUP BY schema_name, table_name
    HAVING COUNT(*) >= 2
)
SELECT
    schema_name,
    table_name,
    size_today,
    size_30_days_ago,
    size_today - size_30_days_ago AS growth_30_days,
    CASE WHEN days > 0
        THEN (size_today - size_30_days_ago) / days * 30
        ELSE 0
    END AS projected_monthly_growth,
    CASE WHEN size_today - size_30_days_ago > 0
        THEN CAST((SELECT SUM(size - FILEPROPERTY(name, 'SpaceUsed')) * 8.0 / 1024 FROM sys.database_files)
             / ((size_today - size_30_days_ago) / days) AS INT)
        ELSE 999
    END AS days_until_full
FROM GrowthData
ORDER BY size_today - size_30_days_ago DESC;
```

- [ ] Calcular proyecci├│n mensual
- [ ] Alertar si d├¡as hasta lleno < 60

---

## Para PostgreSQL

### 2. Espacio Total de la Base de Datos

```sql
-- Tama├▒o de la base de datos
SELECT
    pg_database.datname AS database_name,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS total_size,
    pg_database_size(pg_database.datname) / 1024 / 1024 AS size_mb
FROM pg_database
WHERE datname = current_database();

-- Espacio por tablespace
SELECT
    spcname AS tablespace,
    pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace;
```

- [ ] Documentar tama├▒o actual

### 3. Top 20 Tablas por Tama├▒o

```sql
-- Tablas m├ís grandes
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup AS row_count,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size,
    pg_total_relation_size(schemaname || '.' || relname) / 1024 / 1024 AS size_mb,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size,
    pg_size_pretty(pg_indexes_size(schemaname || '.' || relname::regclass)) AS indexes_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC
LIMIT 20;
```

- [ ] Identificar tablas m├ís grandes

### 4. Tracking de Crecimiento

```sql
-- Crear tabla de historial si no existe
/*
CREATE TABLE IF NOT EXISTS public.table_size_history (
    snapshot_date DATE,
    schema_name TEXT,
    table_name TEXT,
    row_count BIGINT,
    size_mb NUMERIC(18,2),
    PRIMARY KEY (snapshot_date, schema_name, table_name)
);
*/

-- Insertar snapshot actual
INSERT INTO public.table_size_history (snapshot_date, schema_name, table_name, row_count, size_mb)
SELECT
    CURRENT_DATE,
    schemaname,
    relname,
    n_live_tup,
    pg_total_relation_size(schemaname || '.' || relname) / 1024.0 / 1024.0
FROM pg_stat_user_tables
ON CONFLICT (snapshot_date, schema_name, table_name)
DO UPDATE SET row_count = EXCLUDED.row_count, size_mb = EXCLUDED.size_mb;

-- Comparar crecimiento
SELECT
    h1.schema_name,
    h1.table_name,
    h2.size_mb AS size_7_days_ago,
    h1.size_mb AS size_today,
    h1.size_mb - COALESCE(h2.size_mb, 0) AS growth_mb,
    CASE WHEN h2.size_mb > 0
        THEN ROUND(100.0 * (h1.size_mb - h2.size_mb) / h2.size_mb, 2)
        ELSE NULL
    END AS growth_pct
FROM public.table_size_history h1
LEFT JOIN public.table_size_history h2
    ON h1.schema_name = h2.schema_name
    AND h1.table_name = h2.table_name
    AND h2.snapshot_date = CURRENT_DATE - INTERVAL '7 days'
WHERE h1.snapshot_date = CURRENT_DATE
ORDER BY h1.size_mb - COALESCE(h2.size_mb, 0) DESC;
```

- [ ] Guardar snapshot
- [ ] Analizar crecimiento semanal

### 5. Tablas Tipo Log (Potencial Problema)

```sql
-- Detectar tablas que parecen logs (crecen mucho, no se purgan)
SELECT
    schemaname,
    relname,
    n_live_tup AS rows,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS size,
    seq_scan + idx_scan AS total_reads,
    n_tup_ins AS inserts,
    n_tup_del AS deletes,
    CASE WHEN n_tup_del > 0
        THEN ROUND(n_tup_ins::numeric / n_tup_del, 2)
        ELSE n_tup_ins
    END AS insert_delete_ratio
FROM pg_stat_user_tables
WHERE relname LIKE '%log%'
   OR relname LIKE '%audit%'
   OR relname LIKE '%history%'
   OR relname LIKE '%event%'
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC;
```

- [ ] Identificar tablas de log sin pol├¡tica de retenci├│n
- [ ] Verificar si necesitan purga

---

## Output Esperado

```
====== PROC-15 COMPLETADO [TIMESTAMP] ======
Motor: SQL Server / PostgreSQL
Base de datos: [nombre]

ESPACIO ACTUAL:
- Tama├▒o total: X GB
- Usado: Y GB (Z%)
- Libre: W GB

TOP 5 TABLAS:
1. [tabla]: XX GB (YY%)
2. [tabla]: XX GB (YY%)
...

CRECIMIENTO SEMANAL:
- Crecimiento total: +X GB (+Y%)
- Tabla con mayor crecimiento: [nombre] +Z GB

PROYECCI├ôN:
- Crecimiento mensual estimado: X GB
- D├¡as hasta espacio cr├¡tico: Y d├¡as

ALERTAS:
- [lista de alertas si las hay]

RECOMENDACIONES:
- [acciones sugeridas]
```

---

## Criterios de ├ëxito

- [ ] Espacio usado < 70%
- [ ] No hay tablas con crecimiento anormal (>25%/semana)
- [ ] Proyecci├│n > 60 d├¡as hasta lleno
- [ ] Snapshot guardado para tracking

---

## Alertas y Escalaci├│n

| Severidad | Condici├│n | Acci├│n |
|-----------|-----------|--------|
| CRITICAL | Espacio > 85% | WhatsApp inmediato + acci├│n urgente |
| CRITICAL | <30 d├¡as hasta lleno | Planificar expansi├│n urgente |
| WARNING | Espacio > 70% | Crear tarea Holded |
| WARNING | Tabla log > 10GB | Revisar pol├¡tica retenci├│n |
| INFO | Todo OK | Documentar tendencias |

---

## Automatizaci├│n

En ejecuci├│n no-interactiva:
1. Guardar snapshot autom├íticamente
2. Calcular m├®tricas
3. Generar alertas seg├║n umbrales
4. Enviar resumen por WhatsApp si hay WARNING o CRITICAL

---


---

## Output Estructurado (Nexus)

Al finalizar, el agente DEBE generar un bloque JSON con el siguiente formato para que Nexus pueda procesarlo automaticamente:

```json
{
  "result": "success",
  "summary": "Ejecucion de PROC-15 completada. [Descripcion breve de resultados]",
  "metrics": {
    "issues_found": 0,
    "issues_resolved": 0,
    "queries_analyzed": 50, "slow_queries": 3,
    "custom": {
      "procedure_specific_metric": "value"
    }
  },
  "backlog_items": [
    {
      "title": "Titulo del item de seguimiento",
      "description": "Descripcion detallada si se requiere accion futura",
      "priority": "medium",
      "type": "improvement",
      "tags": ["proc-15"]
    }
  ],
  "next_steps": [
    "Accion recomendada 1",
    "Accion recomendada 2"
  ],
  "warnings": [
    "Advertencias encontradas durante la ejecucion"
  ]
}
```

**Campos requeridos:**
- `result`: `"success"` | `"partial"` | `"failed"`
- `summary`: Resumen ejecutivo en 1-3 lineas

**Metricas especificas de este procedure:**
- growth_rate_percent, tables_analyzed, largest_tables

**Criterios de resultado:**
- `success`: Procedimiento completado sin errores criticos
- `partial`: Completado con algunos problemas menores o items pendientes
- `failed`: Error critico o no se pudo completar

## Historial de Ejecuciones

| Fecha | Proyecto | Tama├▒o Total | Usado % | Crecimiento | Proyecci├│n | Alertas |
|-------|----------|--------------|---------|-------------|------------|---------|
| | | | | | | |