Database Maintenance — DBCC, Statistics and Automated Jobs

Database maintenance keeps SQL Server running reliably over months and years. Without maintenance, index fragmentation grows (queries get slower), statistics become stale (query optimizer makes bad plans), and database corruption goes undetected until a restore attempt fails. A weekly maintenance plan covering integrity checks, index maintenance, and statistics updates prevents gradual performance degradation and catches corruption early when it is still recoverable.

DBCC and Maintenance #

-- ── DBCC CHECKDB — integrity verification ────────────────────────────────
-- Run weekly (or after hardware incidents)
-- In production: NO_INFOMSGS to reduce output; PHYSICAL_ONLY for faster check
DBCC CHECKDB (BlogApp) WITH NO_INFOMSGS, PHYSICAL_ONLY;
-- NO_INFOMSGS: suppress informational messages
-- PHYSICAL_ONLY: only check physical page structure (faster, misses some logical errors)
-- Full check (without PHYSICAL_ONLY): weekly or monthly — slower but thorough

-- ── If corruption detected — check severity and repair ───────────────────
-- Error 8976+ = allocation errors (most serious)
-- Error 8900+ = consistency errors

-- Last resort repair (loses some data — only after exhausting restore options):
-- ALTER DATABASE BlogApp SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
-- DBCC CHECKDB (BlogApp, REPAIR_ALLOW_DATA_LOSS);
-- ALTER DATABASE BlogApp SET MULTI_USER;

-- ── Statistics update ─────────────────────────────────────────────────────
-- After major data changes (large imports, purges):
UPDATE STATISTICS dbo.Posts   WITH FULLSCAN;
UPDATE STATISTICS dbo.Users   WITH FULLSCAN;
UPDATE STATISTICS dbo.Comments WITH FULLSCAN;

-- Update all statistics in the database:
EXEC sp_updatestats;   -- updates only stale statistics (sampled, faster)

-- ── Shrink log file after backup ─────────────────────────────────────────
-- Use ONLY after a log backup has truncated the log — do not auto-shrink
DBCC SHRINKFILE (BlogApp_Log, 1024);  -- shrink to 1024MB target
-- Avoid routine shrinking — it causes fragmentation and is counterproductive
-- Only shrink after an unusual one-time log growth event

-- ── SQL Server Agent — automated maintenance schedule ────────────────────
-- Ola Hallengren's DatabaseIntegrityCheck job (recommended):
-- EXEC dbo.DatabaseIntegrityCheck
--     @Databases = 'BlogApp',
--     @CheckCommands = 'CHECKDB',
--     @PhysicalOnly = 'Y',   -- fast check weekly
--     @LogToTable = 'Y';     -- log results to CommandLog table

-- Weekly full check (Sunday) + daily physical check (Mon-Sat)
-- Monthly: run without PHYSICAL_ONLY for complete logical check

-- ── Database growth monitoring ────────────────────────────────────────────
SELECT
    name                          AS FileName,
    type_desc                     AS FileType,
    size * 8 / 1024               AS SizeMB,
    FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS UsedMB,
    (size - FILEPROPERTY(name, 'SpaceUsed')) * 8 / 1024 AS FreeMB
FROM sys.database_files;

-- ── Error log — check for critical errors ────────────────────────────────
EXEC sp_readerrorlog 0, 1, 'Error';    -- current log, SQL Server type, filter 'Error'
EXEC sp_readerrorlog 0, 1, 'severity 17'; -- severity 17+ = serious errors

Common Mistakes #

Mistake 1 — Never running DBCC CHECKDB (corruption discovered during emergency restore) #

❌ Wrong — no integrity checks; corruption accumulates silently; discovered when trying to restore from backup that is also corrupt.

✅ Correct — weekly DBCC CHECKDB with PHYSICAL_ONLY; monthly full check; corruption detected and repaired while still manageable.

Mistake 2 — Routine data file shrinking (fragmentation + re-growth cycle) #

❌ Wrong — weekly DBCC SHRINKFILE; causes severe fragmentation; immediate index rebuild re-grows the file.

✅ Correct — never shrink data files routinely; size data files appropriately at creation with FILEGROWTH set to reasonable increment.