Database Maintenance and DBCC

47.1 Integrity Checks

Corruption response: Don't panic. Don't detach and don't restart.

Run CHECKDB to find the scope.

Restore from backup (page restore if possible).

REPAIR_ALLOW_DATA_LOSS is the absolute last resort.

DBCC CHECKDB ('ShopDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB ('ShopDB') WITH PHYSICAL_ONLY;    -- faster, for large DBs
DBCC CHECKTABLE ('dbo.Orders');
DBCC CHECKALLOC; DBCC CHECKCATALOG; DBCC CHECKFILEGROUP; DBCC CHECKCONSTRAINTS ('dbo.Orders');
SELECT * FROM msdb.dbo.suspect_pages;

47.2 Other DBCC Commands

DBCC SHRINKFILE / SHRINKDATABASE Shrink files (avoid routinely: causes fragmentation) DBCC SQLPERF(LOGSPACE) DBCC OPENTRAN Oldest active transaction DBCC INPUTBUFFER(spid)

Last statement a session sent (or sys.dm_exec_input_buffer) DBCC SHOW_STATISTICS

DBCC FREEPROCCACHE / DROPCLEANBUFFERS Clear caches (test environments only) DBCC TRACEON / TRACEOFF / TRACESTATUS DBCC CHECKIDENT DBCC PAGE / DBCC IND

Undocumented page inspection (or sys.dm_db_page_info) DBCC USEROPTIONS Current SET options DBCC CLONEDATABASE

Schema + statistics clone for troubleshooting DBCC MEMORYSTATUS

47.3 Routine Maintenance Plan

Every 5–15 min Weekly (or on a restored copy) Weekly (threshold-based) History cleanup (backup, job, mail) Error log cycling

47.4 Patching and Upgrades

Servicing model: Cumulative Updates (CUs) and GDRs (security). Service packs ended with SQL Server 2017.

Upgrade paths: in-place, side-by-side migration, rolling upgrade via AG.

Tools: Data Migration Assistant (being replaced by Azure Migrate and the Azure SQL Migration extension), Database Experimentation Assistant, and the Query Tuning Assistant.

After an upgrade: raise the compatibility level gradually and use Query Store to catch regressions.