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 one of the most common self-inflicted performance wounds in SQL Server administration. This post explains exactly …

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 …

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 …

Running DBCC CHECKDB on every database is one of the most important tasks for a SQL Server DBA. But in busy environments it is easy to lose track of which databases have been checked recently and which have not. This script loops through all user databases, reads the last known good DBCC CHECKDB date from each one, and …

An instance with thirty user databases needs thirty full backups, and writing thirty BACKUP DATABASE statements by hand means a new database is missed the day someone creates it. This T-SQL script reads sys.databases, selects every online user database and writes one compressed, checksummed full backup per database to …

A DELETE with no WHERE clause runs at 14:28, the mistake is noticed at 14:45, and the last scheduled backup is hours old. Restoring that backup alone throws away everything committed in between. RESTORE LOG ... WITH STOPAT replays the transaction log only up to a chosen time, and the script below lists the backups in …

SQL Server Query to Identify Tables with Clustered Indexes for Rebuilding Database administrators frequently need to perform maintenance operations on clustered indexes to optimize database performance. This SQL Server query provides a quick and efficient way to identify all user tables that contain clustered indexes, …