SQL Server Diagnostic Report

System Diagnostic Report Analysis

1. Overall Health Summary

• Database States: All databases are online with a mix of recovery models (SIMPLE and FULL).
• CPU Usage: SQL Server process CPU utilization is consistently at 0%. High system idle indicates minimal database activity.
• Memory Usage: Committed memory (8035 MB) is below available physical memory (16383 MB), indicating no immediate memory pressure.
• TempDB Usage: Free space available (approx. 95 MB), which is critical; consider increasing TempDB data files if high usage is exp ected.
• Waits: Significant waits recorded for several types, notably HADR_FILESTREAM_IOMGR_IOCOMPLETION and SP_SERVER_DIAGNOSTICS_SLEE P, indicating potential IO-related blocking or inefficiencies.
• Backups: Critical issue—most databases lack recent backups (LastBackupFinishDate is None). This poses a significant risk for data recovery.

2. Performance Issues and Bottlenecks

• CPU: Over the last hour, the SQL Server CPU showed 0% usage; thus, no CPU bottleneck is present.
• Memory: While there is no immediate memory pressure, the TempDB is critically low on free space, which can lead to performance de gradation.
• IO: Long wait times primarily associated with HADR_FILESTREAM_IOMGR_IOCOMPLETION and DIRTY_PAGE_POLL, indicating potential IO issues.
• The significant wait times on DIRTY_PAGE_POLL suggest that the system is experiencing delays in writing dirty pages to disk, pot entially from disk latency or high write operations.
• Blocking: No information on current blocking or deadlocks provided but consider looking into wait stats related to blocking once high activity resumes.
• Backups: Critical databases have no backups, indicating severe operational risk for potential data loss.

3. Immediate and Long-term Recommendations

Immediate Actions:

• Backup all databases urgently to mitigate the risk of data loss. Set up a scheduled backup process immediately.
• Monitor TempDB usage closely; consider increasing the number of data files to improve performance and prevent contention in the s hort term.

Long-term Strategies:

• Review and optimize queries that contribute to high wait times. Implement missing index suggestions to improve performance in fre quently accessed tables.
• Implement resource governance policies to control TempDB space and prevent exhaustion.
• Set up maintenance plans for regular database backups, integrity checks, and index maintenance.

4. TODO List for Each Database

• backup-ribeira-pombal-07-06-2023: sql BACKUP DATABASE [backup-ribeira-pombal-07-06-2023] TO DISK = N'path_to_backup\backup-ribeira-pombal-07-06-2023.bak';
• bancario_valenca_25112022: ```sql BACKUP DATABASE [bancario_valenca_25112022] TO DISK = N'path_to_backup\bancario_valenca_25112022.bak';

Generated 2026-05-30 02:49:32 | Page 2  - **base_texto_main**:sql BACKUP DATABASE [base_texto_main] TO DISK = N'path_to_backup\base_texto_main.bak'; - **carta_servico**:sql BACKUP DATABASE [carta_servico] TO DISK = N'path_to_backup\carta_servico.bak'; - **Install missing indexes** for optimal performance once backups are secured; for example:sql CREATE INDEX [IX_IO_CONTADOR_cod_cliente] ON [transparencia].[dbo].[IO_CONTADOR] ([cod_cliente]) INCLUDE ([des_data]); CREATE INDEX [IX_tb_sig_salario2_cod_importacao_imp] ON [siga].[dbo].[tb_sig_salario2] ([cod_importacao_imp]); ``` This report outlines a clear path to stabilize the SQL Server environment, ensuring data protection and improving performance efficienc y. Immediate action on backups is critical to maintaining availability and compliance.

Generated 2026-05-30 02:49:32 | Page 3