PostgreSQL vs SQL Server

Identifies user tables suffering from dead tuple bloat and tracks active connection state distribution to catch idle-in-transaction connection leaks.

# 1. Inspect top tables suffering from dead tuple bloat & autovacuum starvation
psql -h pg-primary -U dbre_admin -d appdb -c "
SELECT 
  relname, 
  n_dead_tup, 
  n_live_tup, 
  ROUND(100.0 * n_dead_tup / NULLIF(n_dead_tup + n_live_tup, 0), 2) AS dead_pct, 
  last_autovacuum, 
  last_autoanalyze 
FROM pg_stat_user_tables 
WHERE n_dead_tup > 2000 
ORDER BY n_dead_tup DESC 
LIMIT 8;"

# 2. Check active backend connection distribution and idle-in-transaction states
psql -h pg-primary -U dbre_admin -d appdb -c "
SELECT 
  state, 
  COUNT(*), 
  MAX(EXTRACT(EPOCH FROM (now() - state_change))) AS max_duration_sec 
FROM pg_stat_activity 
GROUP BY state 
ORDER BY count DESC;"

Audits transaction log utilization, identifies log reuse hold reasons, and monitors Always On Availability Group synchronization queues.

# 1. Audit SQL Server transaction log space utilization & reuse wait reasons
sqlcmd -S mssql-primary -U dbre_monitor -P "$MSSQL_PASS" -Q "
SELECT 
  name AS database_name, 
  recovery_model_desc, 
  log_reuse_wait_desc 
FROM sys.databases 
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb');
DBCC SQLPERF(LOGSPACE);"

# 2. Inspect Always On Availability Group replica synchronization states
sqlcmd -S mssql-primary -U dbre_monitor -P "$MSSQL_PASS" -Q "
SELECT 
  ar.replica_server_name, 
  drcs.database_name, 
  drs.synchronization_state_desc, 
  drs.synchronization_health_desc, 
  drs.log_send_queue_size, 
  drs.redo_queue_size 
FROM sys.dm_hadr_database_replica_states drs 
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id 
JOIN sys.dm_hadr_database_replica_cluster_states drcs ON drs.group_database_id = drcs.group_database_id;"