Every database with foreign keys eventually needs a single inventory: every constraint, its parent table and column, and the referenced table and column on the other side. This T-SQL script joins sys.foreign_keys to sys.foreign_key_columns, sys.tables, and sys.columns to produce the canonical foreign-key map of the …

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 …

Orphaned database users are a common problem after database restores, server migrations, or login deletions. A database user becomes orphaned when its SID does not match any server-level login SID. This script queries sys.database_principals and sys.server_principals to find every orphaned user in a database, and shows …