Storage errors do not always surface as immediate failures. When SQL Server hits an 823 or 824 error while reading a database page, it records that page in msdb.dbo.suspect_pages — a system table in msdb that keeps one row per suspect page until someone deletes it. Querying this table is the fastest first pass in any …

Heaps and indexes that were never compressed keep occupying full-size pages, and nothing in a default database flags them. sys.dm_db_index_physical_stats joined to sys.partitions lists the uncompressed heaps and indexes above a size threshold, and the script below turns that list into ready-to-review ALTER INDEX ... …

Statistics are the optimizer's picture of the data, and that picture ages every time rows change. When a table grows faster than its statistics are refreshed, plans keep being built for the row counts it held weeks ago. Refreshing the statistics is often the fix, and the work is knowing which ones to refresh and with …

An incident rarely announces which DBCC statement it needs. A log file stops truncating, a query slows after a plan changes, an identity column runs ahead of its table, and each one has a Database Console Command that answers it, sitting next to a few that can make the same situation worse. The DBCC statements are …

Running DBCC CHECKDB on a schedule is only the start, because its options change what it checks, how long it takes and what it can do to your data. This post covers the check options, a script that runs the command against every online user database, and the three repair levels, including why REPAIR_ALLOW_DATA_LOSS is …

A one-time data load doubled a data file overnight, the load is done, the rows are deleted, and the file is now mostly empty space. The reflex — reach for DBCC SHRINKDATABASE and give the disk back — is a common way to hurt performance without meaning to. This post explains what shrinking does to your indexes, when it …

Open the SQL Agent job history on an instance that has been running for a decade and you will still find DBCC DBREINDEX in a nightly maintenance step, quietly doing its job. It works — which is exactly why nobody has touched it. But it has been deprecated for the better part of twenty years, it locks tables it does not …

Duplicate and overlapping indexes waste storage, slow down every write, and split optimizer statistics across redundant copies. This T-SQL script compares each non-heap index in the current database on its first sixteen key columns and surfaces any pair of indexes that overlap, giving the DBA a short list of cleanup …

Excessive virtual log files (VLFs) inside the transaction log slow down database recovery, backup, and restore operations — often by minutes on large logs — and the problem compounds silently every time autogrowth fires with a small increment. This script queries DBCC LOGINFO across every online database on the …

In busy SQL Server environments it is easy to lose track of which databases have had a recent DBCC CHECKDB and which have not. This script loops through all user databases, reads the last known good DBCC CHECKDB date from each one, and presents the results sorted from oldest to newest so you can spot gaps fast. Purpose …