This T-SQL script kills one or more SQL Server sessions (SPIDs) chosen by filters: login, database, session state, open transactions and the age of the last request. It reads sys.dm_exec_sessions, builds a KILL statement for each match, and by default only prints what it would do. Purpose and Overview Clearing …

This script queries the SQL Server backup history catalog to report the most recent full, differential, and transaction log backup for every database, including how many hours and minutes ago each backup completed. Purpose and Overview Which databases on this instance have not had a full backup since yesterday, and …

Before a column is renamed or a table is dropped, the question is which procedures, views, and functions still mention it. This script answers that by searching the stored text of every SQL module in the current database for a keyword, matching on the object name as well as the definition. It returns the schema, object …

A database goes suspect at 2 a.m., a backup job reports failure, or one login is being refused again and again, and the answer is a line in the SQL Server error log. This script finds that line from T-SQL: it filters by keyword, narrows by time window, reads archived logs and loads the result into a temp table for …

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 …

Verify SQL Server Backup Files with RESTORE VERIFYONLY A backup file that cannot be restored is worthless, and the problem often surfaces only when a restore is attempted under pressure. The two scripts below use RESTORE VERIFYONLY to confirm that backup files are complete and readable before a real disaster occurs. …

This T-SQL query lists the rowstore indexes in the current database whose leaf level is fragmented and large enough to be worth maintaining, and returns a ready-to-review ALTER INDEX statement for each one. It reads sys.dm_db_index_physical_stats in SAMPLED mode and filters on avg_fragmentation_in_percent and …

Which databases on this instance hold the most disk, and how large is each one in MB and GB? One query against sys.master_files and sys.databases answers that for every user database, sorted with the largest first. Purpose and Overview Capacity planning starts with a number per database: how much file space each one …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …

DBCC CHECKIDENT with NORESEED reports the current identity value of a table next to the largest value actually stored in the identity column, without changing either. It works on one table per call, so checking a whole database means writing one command per table. The script below generates those commands from the …