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 script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each. Purpose and Overview When a SQL Server instance is under CPU or I/O pressure, …

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 …

Giving an application role read, write and execute rights on a database usually means one GRANT per table per permission plus one per stored procedure, which runs to hundreds of statements on a mid-sized schema. The script below writes those statements for you from sys.objects, schema-qualified and bracket-quoted, so …

SQL Server DBCC DBREINDEX Script with Timing - Database Maintenance Guide Database maintenance is a critical aspect of SQL Server administration, and index rebuilding plays a vital role in maintaining optimal database performance. This comprehensive guide explores powerful DBCC DBREINDEX scripts that include timing …

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 …

SQL Server Database Table Row Count Report Script - Complete Guide Database administrators frequently need to monitor table sizes and row counts across their SQL Server databases. This comprehensive guide explores a powerful T-SQL script that generates detailed table row count reports using cursor-based iteration and …