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 out of space inside a data or log file halts SQL Server transactions immediately — and discovering that a volume is full only after an outage is too late. This script queries sys.master_files and sys.dm_os_volume_stats across every online database on the instance to report each file's allocated size, space …

Every SQL Server installation ships with max server memory set to its default value of 2,147,483,647 MB — effectively unlimited — which allows the Buffer Pool to consume all available RAM and starve the operating system of the memory it needs for network stack, disk I/O, and other critical processes. This script reads …

A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and …

A copy-only backup is a special SQL Server backup that is independent of the normal backup sequence. It does not reset the differential base or interrupt the transaction log backup chain, making it the correct choice when you need an ad-hoc backup for a development or test environment restore without affecting …

Finding databases that have not been backed up recently is one of the most important checks a DBA can run. A database that grows unnoticed without a backup is a data loss event waiting to happen. This script queries msdb.dbo.backupset against sys.databases to surface any online user database with no full backup in the …

Foreign keys enforce referential integrity, but SQL Server does not automatically create indexes on the referencing (child) columns. An unindexed foreign key column forces a full table scan every time a join or a DELETE on the parent table triggers a referential check. This script identifies all foreign key columns in …

Backup compression reduces the on-disk size of SQL Server backup files and can significantly shorten backup duration by reducing the amount of data written to disk. This post shows how to enable compression at the server level, verify the setting is active, and confirm compression ratios from backup history using the …

SQL Server records CPU utilization data internally every minute and stores the last 256 readings in a ring buffer. This T-SQL script reads that buffer to produce a CPU history report showing SQL Server CPU usage, other process CPU usage, and total server CPU load — without requiring any external monitoring tool. …

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 …