35 — DBCC Commands Reference

-- Minimal / fastest — checks physical page structure only
DBCC CHECKDB (N'MyDatabase') WITH PHYSICAL_ONLY;

-- Full integrity check (default)
DBCC CHECKDB (N'MyDatabase');

-- Full check with no informational messages
DBCC CHECKDB (N'MyDatabase') WITH NO_INFOMSGS;

-- Full check with extended logical checks
DBCC CHECKDB (N'MyDatabase') WITH EXTENDED_LOGICAL_CHECKS;

-- Estimate how long a check will take (2014+)
DBCC CHECKDB (N'MyDatabase') WITH ESTIMATEONLY;

-- Repair without data loss (can only fix minor allocation errors)
DBCC CHECKDB (N'MyDatabase', REPAIR_REBUILD);

-- Repair with possible data loss (removes corrupt pages)
DBCC CHECKDB (N'MyDatabase', REPAIR_ALLOW_DATA_LOSS);

-- Put in single-user mode before repair
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB (N'MyDatabase', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE MyDatabase SET MULTI_USER;

-- 1. Restore database from backup to secondary server (NORECOVERY optional)
RESTORE DATABASE MyDatabase_Test
  FROM DISK = N'\\backup\MyDatabase_full.bak'
  WITH MOVE 'MyDatabase_Data' TO 'D:\MSSQL\MyDatabase_Test.mdf',
       MOVE 'MyDatabase_Log'  TO 'D:\MSSQL\MyDatabase_Test.ldf',
       RECOVERY, REPLACE;

-- 2. Run full CHECKDB against the restored copy
DBCC CHECKDB (N'MyDatabase_Test') WITH NO_INFOMSGS;

-- 3. Drop the test database
DROP DATABASE MyDatabase_Test;

-- Create snapshot
CREATE DATABASE MyDatabase_snap ON
  (NAME = MyDatabase_Data,
   FILENAME = 'D:\Snapshots\MyDatabase_snap.ss')
AS SNAPSHOT OF MyDatabase;

-- Run CHECKDB against snapshot (reads from original data pages on demand)
DBCC CHECKDB (N'MyDatabase_snap') WITH NO_INFOMSGS;

-- Drop snapshot
DROP DATABASE MyDatabase_snap;

Msg 8978, Level 16, State 1
Table error: Object ID 123456. Page (1:12345) is missing a reference
from previous page (1:12344). Possible chain linkage problem.

-- Check CHECKDB progress via sys.dm_exec_requests
SELECT
    session_id,
    command,
    percent_complete,
    estimated_completion_time / 1000.0 / 60 AS est_minutes_remaining,
    wait_type,
    wait_time / 1000.0 AS wait_seconds
FROM sys.dm_exec_requests
WHERE command LIKE '%CHECKDB%'
   OR command LIKE '%CHECKTABLE%';

-- Find when CHECKDB last ran successfully per database
SELECT
    name,
    DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime') AS LastGoodCheckDbTime
FROM sys.databases
WHERE state_desc = 'ONLINE'
ORDER BY DATABASEPROPERTYEX(name, 'LastGoodCheckDbTime');

-- Check a table
DBCC CHECKTABLE (N'Sales.Orders');

-- Physical only (much faster)
DBCC CHECKTABLE (N'Sales.Orders') WITH PHYSICAL_ONLY;

-- No informational messages
DBCC CHECKTABLE (N'Sales.Orders') WITH NO_INFOMSGS;

-- Check at a specific DBCC_SNAPSHOT point (snapshot isolation)
-- Avoids blocking — reads committed data without SCH-S locks
DBCC CHECKTABLE (N'Sales.Orders') WITH TABLOCK;  -- forces table lock

DBCC CHECKALLOC (N'MyDatabase') WITH NO_INFOMSGS;

DBCC CHECKCATALOG (N'MyDatabase') WITH NO_INFOMSGS;

-- Check a named filegroup
DBCC CHECKFILEGROUP (N'SECONDARY') WITH NO_INFOMSGS;

-- Check primary filegroup
DBCC CHECKFILEGROUP (1) WITH NO_INFOMSGS;

-- Get filegroup IDs
SELECT data_space_id, name FROM sys.filegroups;

-- Clear ALL plans from ALL databases (affects entire instance)
DBCC FREEPROCCACHE;

-- Clear plans for a specific plan handle
DECLARE @plan_handle VARBINARY(64);
SELECT @plan_handle = plan_handle
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE text LIKE '%SearchForThisQuery%';

DBCC FREEPROCCACHE (@plan_handle);

-- Clear plans for a specific SQL handle (removes all plans for a query)
DBCC FREEPROCCACHE (@sql_handle);

-- For a specific database only — use Resource Governor pool approach
-- (no built-in "free cache for one database" command)

-- Find the plan handle for a specific cached query
SELECT
    qs.plan_handle,
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.text LIKE N'%OrdersByCustomer%'
ORDER BY qs.total_worker_time DESC;

-- Clear the buffer pool (flushes dirty pages first via CHECKPOINT)
CHECKPOINT;          -- flush dirty pages to disk
DBCC DROPCLEANBUFFERS;  -- then remove all clean pages

-- Standard benchmark pattern: cold cache test
CHECKPOINT;
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

-- Run your query here
SET STATISTICS IO, TIME ON;
SELECT ... FROM ...;
SET STATISTICS IO, TIME OFF;

-- Shrink a specific file (by file name)
DBCC SHRINKFILE (N'MyDatabase_Log', 256);  -- shrink to 256 MB target

-- Shrink a file by file ID
SELECT file_id, name, size/128 AS size_mb FROM sys.database_files;
DBCC SHRINKFILE (2, 512);  -- file_id 2, target 512 MB

-- Truncate only (remove empty space at end of file — less destructive)
DBCC SHRINKFILE (N'MyDatabase_Data', TRUNCATEONLY);

-- Shrink entire database (shrinks all files — almost always wrong)
DBCC SHRINKDATABASE (N'MyDatabase', 10);  -- 10% free space target

-- Set auto-grow to a sensible size to avoid many small growths
ALTER DATABASE MyDatabase
MODIFY FILE (NAME = N'MyDatabase_Data', FILEGROWTH = 512MB);

-- Pre-size files correctly initially
ALTER DATABASE MyDatabase
MODIFY FILE (NAME = N'MyDatabase_Data', SIZE = 20480MB);

-- 1. Verify log is not in use (check VLF status)
DBCC LOGINFO;  -- look for status=2 VLFs (active)

-- 2. Back up the log to free VLFs
BACKUP LOG MyDatabase TO DISK = N'NUL';  -- NUL = discard (dev only)

-- 3. Check log_reuse_wait_desc to understand why log can't shrink
SELECT name, log_reuse_wait_desc FROM sys.databases WHERE name = 'MyDatabase';

-- 4. Shrink (if log_reuse_wait_desc = NOTHING or LOG_BACKUP)
DBCC SHRINKFILE (N'MyDatabase_Log', 256);

-- Correct entire database
DBCC UPDATEUSAGE (N'MyDatabase') WITH NO_INFOMSGS;

-- Correct a specific table
DBCC UPDATEUSAGE (N'MyDatabase', N'Sales.Orders') WITH NO_INFOMSGS;

-- Correct a specific index on a table
DBCC UPDATEUSAGE (N'MyDatabase', N'Sales.Orders', 1) WITH NO_INFOMSGS;
-- (third argument is index_id from sys.indexes)

-- See what session 72 is doing
DBCC INPUTBUFFER (72);

-- Modern equivalent (more columns, no DBCC)
SELECT
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time / 1000.0 AS wait_sec,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS current_statement,
    t.text AS full_batch
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = 72;

-- Check current database
DBCC OPENTRAN;

-- Check a specific database
DBCC OPENTRAN (N'MyDatabase');

-- More detail with DMVs (preferred)
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, GETDATE()) AS age_minutes,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_tran_active_transactions at
JOIN sys.dm_tran_session_transactions st ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
ORDER BY at.transaction_begin_time ASC;

-- Log file space usage for all databases
DBCC SQLPERF (LOGSPACE);
-- Returns: Database Name, Log Size (MB), Log Space Used (%), Status

-- Reset wait statistics (use only in baseline capture scenarios)
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

-- Reset latch statistics
DBCC SQLPERF ('sys.dm_os_latch_stats', CLEAR);

-- Show all three result sets
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID');

-- Show specific parts
DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID')
    WITH STAT_HEADER;         -- metadata only

DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID')
    WITH DENSITY_VECTOR;      -- column prefix densities

DBCC SHOW_STATISTICS ('Sales.Orders', 'IX_Orders_CustomerID')
    WITH HISTOGRAM;           -- 200-step selectivity histogram

-- Modern replacement
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, 'LIMITED'  -- or 'SAMPLED' / 'DETAILED'
) ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 5
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Enable trace flag for current session only
DBCC TRACEON (4199);

-- Enable trace flag globally (all sessions)
DBCC TRACEON (4199, -1);

-- Enable multiple trace flags globally
DBCC TRACEON (1117, 1118, -1);

-- Disable a trace flag globally
DBCC TRACEOFF (4199, -1);

-- Check status of specific trace flags
DBCC TRACESTATUS (4199, 1117, 1118);

-- Check status of all enabled trace flags
DBCC TRACESTATUS (-1);

-- Enable trace flag 3604 to direct output to client (required)
DBCC TRACEON (3604);

-- Read a page: (database_id, file_id, page_id, print_option)
-- print_option: 0=header, 1=header+data, 2=header+data+row offsets, 3=header+each row
DBCC PAGE (MyDatabase, 1, 1234, 3);

-- Turn off after use
DBCC TRACEOFF (3604);

-- DBCC IND: List all pages for an object
-- (database, object_name_or_id, index_id: -1=all)
DBCC TRACEON (3604);
DBCC IND (MyDatabase, 'Sales.Orders', -1);

-- DBCC EXTENTINFO: Extent-level allocation info
DBCC EXTENTINFO (MyDatabase, 'Sales.Orders', -1);
DBCC TRACEOFF (3604);

-- Documented alternative (2012+)
SELECT *
FROM sys.dm_db_database_page_allocations(
    DB_ID('MyDatabase'),
    OBJECT_ID('Sales.Orders'),
    NULL,   -- index_id (NULL = all)
    NULL,   -- partition_id (NULL = all)
    'DETAILED'
);

DBCC MEMORYSTATUS;

-- Top memory consumers by clerk
SELECT
    type AS clerk_type,
    SUM(pages_kb) / 1024.0 AS memory_mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY memory_mb DESC;

DBCC USEROPTIONS;

-- Verify your connection's SET options match application connections
-- Application connections via ODBC/JDBC typically have ARITHABORT = OFF
-- SSMS has ARITHABORT = ON
-- This means SSMS executes with a different plan than the app

-- Check current identity value (does not change anything)
DBCC CHECKIDENT ('Sales.Orders', NORESEED);

-- Reseed to a specific value
DBCC CHECKIDENT ('Sales.Orders', RESEED, 10000);

-- Let SQL Server correct identity if it's wrong (auto-correct)
-- Sets seed to max(identity_col) in the table
DBCC CHECKIDENT ('Sales.Orders', RESEED);

-- Detect: current seed < max existing value
SELECT IDENT_CURRENT('Sales.Orders') AS current_seed,
       MAX(OrderID) AS max_existing
FROM Sales.Orders;

-- Fix:
DBCC CHECKIDENT ('Sales.Orders', RESEED);

-- Create a clone (SQL Server 2014 SP2+)
DBCC CLONEDATABASE (N'MyDatabase', N'MyDatabase_Clone');

-- The clone contains:
--   - All schema objects (tables, indexes, views, procs, functions)
--   - All statistics (histograms, density vectors)
--   - No data rows
--   - No logins mapped (orphaned users)

ALTER DATABASE MyDatabase_Clone SET READ_WRITE WITH NO_WAIT;
ALTER DATABASE MyDatabase_Clone SET MULTI_USER WITH NO_WAIT;

-- Count VLFs per database
CREATE TABLE #vlfinfo (
    RecoveryUnitID INT, FileID INT, FileSize BIGINT,
    StartOffset BIGINT, FSeqNo BIGINT, [Status] INT,
    Parity INT, CreateLSN NUMERIC(25,0)
);
INSERT #vlfinfo EXEC ('DBCC LOGINFO WITH NO_INFOMSGS');
SELECT COUNT(*) AS vlf_count FROM #vlfinfo;
DROP TABLE #vlfinfo;