Nexus - AI Task Orchestrator

14-database-maintenance-tasks
Procedimiento: Tareas de Mantenimiento de Base de Datos

Base de Datos
1 plugin(s)
Titulo
Descripcion
Contenido Markdown
11091 caracteres
# Procedimiento: Tareas de Mantenimiento de Base de Datos

## Metadata
- **ID**: PROC-14
- **Frecuencia**: Semanal (domingo noche preferido)
- **Duraci├│n estimada**: 30-60 min
- **Requiere**: Acceso SQL Server (db_owner) o PostgreSQL (superuser o rol con VACUUM)
- **Dependencias**: PROC-13 (opcional, para saber qu├® ├¡ndices optimizar)
- **Bloquea**: Operaciones de escritura durante rebuild (SQL Server)
- **Agentes**: database-optimizer

---

## Objetivo

Ejecutar tareas de mantenimiento rutinarias:
1. Actualizar estad├¡sticas
2. Reconstruir/reorganizar ├¡ndices fragmentados
3. Limpiar espacio (VACUUM en PostgreSQL)
4. Verificar integridad

---

## Detecci├│n de Stack

```yaml
stack:
  database: "sqlserver"  # sqlserver | postgresql
```

---

## Checklist Ejecutable

### 1. Pre-verificaciones

- [ ] Confirmar ventana de mantenimiento disponible
- [ ] Verificar espacio en disco suficiente (m├¡nimo 20% libre)
- [ ] Notificar a equipo si es producci├│n

---

## Para SQL Server

### 2. Verificar Fragmentaci├│n de ├ìndices

```sql
-- ├ìndices con fragmentaci├│n > 10%
SELECT
    OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    CASE
        WHEN ips.avg_fragmentation_in_percent < 10 THEN 'OK'
        WHEN ips.avg_fragmentation_in_percent < 30 THEN 'REORGANIZE'
        ELSE 'REBUILD'
    END AS action_needed
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
    AND ips.page_count > 1000  -- Solo ├¡ndices con > 1000 p├íginas
    AND i.name IS NOT NULL
ORDER BY ips.avg_fragmentation_in_percent DESC;
```

- [ ] Identificar ├¡ndices que requieren REORGANIZE (10-30%)
- [ ] Identificar ├¡ndices que requieren REBUILD (>30%)

### 3. Reorganizar ├ìndices (Fragmentaci├│n 10-30%)

```sql
-- Generar scripts de REORGANIZE
SELECT
    'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(ips.object_id) + '].[' + OBJECT_NAME(ips.object_id) + '] REORGANIZE;' AS reorganize_statement
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent BETWEEN 10 AND 30
    AND ips.page_count > 1000
    AND i.name IS NOT NULL;
```

- [ ] Ejecutar REORGANIZE (operaci├│n online, no bloquea)

### 4. Reconstruir ├ìndices (Fragmentaci├│n >30%)

```sql
-- Generar scripts de REBUILD
-- NOTA: REBUILD es offline por defecto en ediciones Standard
SELECT
    'ALTER INDEX [' + i.name + '] ON [' + OBJECT_SCHEMA_NAME(ips.object_id) + '].[' + OBJECT_NAME(ips.object_id) + '] REBUILD WITH (ONLINE = OFF);' AS rebuild_statement
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30
    AND ips.page_count > 1000
    AND i.name IS NOT NULL;
```

- [ ] Ejecutar REBUILD en ventana de mantenimiento
- [ ] Si es Enterprise Edition, usar `ONLINE = ON`

### 5. Actualizar Estad├¡sticas

```sql
-- Actualizar todas las estad├¡sticas de la base de datos
EXEC sp_updatestats;

-- O para tablas espec├¡ficas con estad├¡sticas desactualizadas:
SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    'UPDATE STATISTICS [' + OBJECT_SCHEMA_NAME(s.object_id) + '].[' + OBJECT_NAME(s.object_id) + '] [' + s.name + '];' AS update_statement
FROM sys.stats s
WHERE STATS_DATE(s.object_id, s.stats_id) < DATEADD(DAY, -7, GETDATE())
    AND OBJECT_SCHEMA_NAME(s.object_id) NOT IN ('sys', 'INFORMATION_SCHEMA')
ORDER BY STATS_DATE(s.object_id, s.stats_id);
```

- [ ] Ejecutar actualizaci├│n de estad├¡sticas
- [ ] Verificar que no hay estad├¡sticas > 7 d├¡as de antig├╝edad

### 6. Verificar Integridad (DBCC CHECKDB)

```sql
-- Verificar integridad de la base de datos
-- NOTA: Puede ser lento en bases grandes
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
```

- [ ] Ejecutar CHECKDB (puede tomar tiempo)
- [ ] Verificar que no hay errores de consistencia

### 7. Limpiar Logs de Transacciones (si aplica)

```sql
-- Verificar tama├▒o de log
SELECT
    name AS log_name,
    size * 8.0 / 1024 AS size_mb,
    FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024 AS used_mb
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Si el log est├í muy grande y el recovery model lo permite:
-- BACKUP LOG [DatabaseName] TO DISK = 'NUL';  -- Truncar log (solo en SIMPLE)
-- DBCC SHRINKFILE (log_file_name, target_size_mb);
```

- [ ] Verificar tama├▒o de log de transacciones
- [ ] Hacer backup de log si es necesario

---

## Para PostgreSQL

### 2. Ejecutar VACUUM ANALYZE

```sql
-- VACUUM ANALYZE en toda la base de datos
-- Recupera espacio y actualiza estad├¡sticas
VACUUM (VERBOSE, ANALYZE);

-- O para tablas espec├¡ficas con muchas tuplas muertas:
SELECT
    schemaname,
    relname AS table_name,
    n_dead_tup,
    n_live_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
```

- [ ] Ejecutar VACUUM ANALYZE
- [ ] Verificar tablas con alto porcentaje de tuplas muertas

### 3. VACUUM FULL (Solo si es Necesario)

```sql
-- ADVERTENCIA: VACUUM FULL bloquea la tabla completamente
-- Solo usar si hay mucho espacio desperdiciado (bloat)

-- Detectar bloat en tablas
SELECT
    schemaname || '.' || relname AS table_name,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || relname)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname || '.' || relname)) AS table_size,
    ROUND(100.0 * pg_relation_size(schemaname || '.' || relname) /
          NULLIF(pg_total_relation_size(schemaname || '.' || relname), 0), 2) AS table_pct
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname || '.' || relname) DESC
LIMIT 20;

-- Si se decide hacer VACUUM FULL:
-- VACUUM FULL VERBOSE table_name;
```

- [ ] Evaluar si VACUUM FULL es necesario
- [ ] Si se ejecuta, hacerlo en ventana de mantenimiento

### 4. Reindexar ├ìndices Hinchados

```sql
-- Detectar ├¡ndices que podr├¡an beneficiarse de REINDEX
SELECT
    schemaname,
    indexrelname AS index_name,
    relname AS table_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS times_used
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- REINDEX de forma concurrente (no bloquea):
-- REINDEX INDEX CONCURRENTLY index_name;
-- REINDEX TABLE CONCURRENTLY table_name;
```

- [ ] Identificar ├¡ndices grandes con poco uso
- [ ] Ejecutar REINDEX CONCURRENTLY si es necesario

### 5. Actualizar Estad├¡sticas del Planner

```sql
-- Forzar recolecci├│n de estad├¡sticas
ANALYZE VERBOSE;

-- Verificar configuraci├│n de autovacuum
SHOW autovacuum;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_scale_factor;
```

- [ ] Ejecutar ANALYZE
- [ ] Verificar que autovacuum est├í habilitado

### 6. Verificar Conexiones Hu├®rfanas

```sql
-- Conexiones idle por m├ís de 1 hora
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    NOW() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'idle'
    AND query_start < NOW() - INTERVAL '1 hour'
ORDER BY query_start;

-- Para terminar conexiones problem├íticas:
-- SELECT pg_terminate_backend(pid);
```

- [ ] Revisar conexiones idle antiguas
- [ ] Terminar si son hu├®rfanas

---

## Output Esperado

```
====== PROC-14 COMPLETADO [TIMESTAMP] ======
Motor: SQL Server / PostgreSQL
Base de datos: [nombre]

FRAGMENTACI├ôN (SQL Server):
- ├ìndices reorganizados: X
- ├ìndices reconstruidos: Y
- Fragmentaci├│n promedio antes: Z%, despu├®s: W%

VACUUM (PostgreSQL):
- Tablas procesadas: X
- Tuplas muertas recuperadas: Y
- Espacio liberado: Z MB

ESTAD├ìSTICAS:
- Actualizadas: X tablas

INTEGRIDAD:
- CHECKDB/Integridad: OK / ERRORES

Pr├│ximo mantenimiento recomendado: [fecha]
```

---

## Criterios de ├ëxito

- [ ] Fragmentaci├│n de ├¡ndices < 10% en promedio
- [ ] Estad├¡sticas actualizadas (< 24 horas)
- [ ] Sin errores de integridad
- [ ] Espacio de log/bloat controlado

---

## Alertas y Escalaci├│n

| Severidad | Condici├│n | Acci├│n |
|-----------|-----------|--------|
| CRITICAL | CHECKDB reporta errores | Escalar inmediatamente - posible corrupci├│n |
| CRITICAL | Espacio en disco < 10% | Liberar espacio urgente |
| WARNING | Fragmentaci├│n > 50% generalizada | Programar mantenimiento extenso |
| WARNING | Estad├¡sticas > 30 d├¡as | Ejecutar update urgente |
| INFO | Todo OK | Documentar y programar siguiente |

---

## Automatizaci├│n

Para ejecutar de forma no-interactiva, usar modo por defecto:
- SQL Server: REORGANIZE + UPDATE STATISTICS (no REBUILD)
- PostgreSQL: VACUUM ANALYZE (no VACUUM FULL)

Operaciones que requieren confirmaci├│n manual:
- REBUILD de ├¡ndices
- VACUUM FULL
- Terminaci├│n de conexiones

---


---

## 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-14 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-14"]
    }
  ],
  "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:**
- tasks_executed, tasks_failed, maintenance_score

**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 | Motor | ├ìndices Opt. | Stats Update | Integridad | Notas |
|-------|----------|-------|--------------|--------------|------------|-------|
| | | | | | | |