DB Notes

DBA Toolkit: SQL Server Health Check Queries This guide provides a suite of T‑SQL queries that give you a quick snapshot of your SQL Server’s health. The toolkit covers: Database Health Status Top 5 Resource‑Intensive Queries (by CPU time, in seconds) Top 5 Largest Objects (Tables) by Size Backup Status – including full, differential (incremental), and transaction log backups Database Size Summary Memory (RAM) Usage 1. Database Health Status This query returns basic state and configuration info for each database: SELECT name, state_desc, -- e.g. ONLINE, OFFLINE, RECOVERING, SUSPECT, etc. recovery_model_desc, -- SIMPLE, FULL, or BULK_LOGGED user_access_desc, -- MULTI_USER, SINGLE_USER, RESTRICTED_USER is_read_only, is_auto_close_on, is_auto_shrink_on FROM sys.databases; 2. Top 5 Resource‑Intensive Querie...

            It could be tricky to detect when your database slows down for no reason. At first, you should check system resources (CPU - RAM usage, network etc.) and if find some bottlenecks, then go deeper. ADDM report should be useful for detecting most problem causes.           But it is also probable that some queries may be using server resources excessively. How to detect them? Well, there is one simple query to check that: select s.sql_text,sl.sid,sl.target||'-'||sl.opname Target,sl.totalwork,sl.sofar,sl.time_remaining Seconds_remaining,sl.elapsed_seconds,sl.sql_id,sl.username from v$session_longops sl,v$sql s,v$session se where s1.sid=se.sid and se.sql_id=s.sql_id and totalwork!=0 and sofar<>totalwork;