Each sub-folder is a topic. Within each folder, scripts are named for what they do.
The Categories
lock/ — Blocking and Locking
The three scripts every DBA runs when someone reports that “the database is slow”:
blocking_sessions_report.sql: The first script to run during a blocking incident. Queries sys.dm_exec_requests to show every session that is currently waiting, what it is waiting for, and which session is blocking it.
sp_who2.sql: The classic. Shows all active sessions with their status, blocking chain, and the database they are using. Fast to read at a glance.
specific_session.sql : When you know the session ID, this gives you the full picture: current query, wait type, locks held, transaction state. Use it after blocking_sessions_report.sql identifies the blocker.
index/ — Index Management
Nine scripts covering the full lifecycle of SQL Server indexes.
check_index_fragmentation.sql: Before rebuilding anything, measure first. This script uses sys.dm_db_index_physical_stats to show the fragmentation percentage of every index in the database.
rebuild_index.sql / reorganize_index.sql : The two remediation actions. Rebuild completely recreates the index (eliminates fragmentation, updates statistics). Reorganize defragments online at the leaf level (faster, less I/O, but less thorough).
clustered_index.sql, nonclustered_index.sql, nonclustered_index_include.sql : Templates for creating each index type.
clustered_columnstore_index.sql / nonclustered_columnstore_index.sql : Columnstore templates for analytical workloads and data warehouse tables where scan-heavy queries need maximum compression and batch mode execution.
statistics.sql: Creates statistics on specific columns manually, for situations where the auto-created statistics are not sufficient.
ola_hallengren.sql : A reference script that invokes IndexOptimize from the maintenance solution, useful when you want ad-hoc index maintenance on a specific table or database without running the scheduled SQL Agent job.
backup_recovery/ — Backup and Restore
The scripts that matter most when something goes wrong.
backup_history.sql : Queries msdb.dbo.backupset to show recent backup history by database, type, and duration. The script you run to confirm last night's backup completed before you make a production change.
restore_script.sql : A template for a full + differential + log restore sequence. The script that saves time when every minute counts during a recovery.
restore_start_time.sql : Estimates how long a restore will take based on the backup file size and historical restore rates. Useful when you need to give a realistic ETA to stakeholders during an incident.
search_backups_folder.sql : Queries the backup directory for available backup files when you need to find the right backup for a point-in-time restore.
clean_backup_history.sql: The msdb.dbo.backupset table grows indefinitely. This script prunes old backup history records to keep msdb healthy.
change_simple_to_full.sql / check.sql : Changing a database to FULL recovery model is a prerequisite for transaction log backups. These scripts handle the change and verify the current recovery model across all databases.
database_size/ — Storage Monitoring
Six scripts for understanding where your disk space is going.
database_files_detail.sql: Shows every data and log file for every database: physical location, current size, used space, free space, and autogrowth setting. The first script to run when disk space is running low.
largest_table.sql: Identifies the top tables by row count and allocated space. When a database grows unexpectedly, this is how you find out which table is responsible.
database_storage_breakdown.sql: Breaks down storage by database across the instance. Essential for capacity planning.
database_storage_alert.sql: A threshold-based query that returns databases where data or log files have less than a configurable percentage of free space. Schedule this as a SQL Agent job to generate alerts before disks fill up.
memory_settings.sql: Shows the current max server memory configuration and how much memory SQL Server is actually using. Useful during memory pressure investigation.
total_used_storage.sql: A quick instance-wide summary: total storage allocated vs. total space used across all databases.
free_space/ — Disk and Filegroup Management
partition_filegroup_allocation.sql: For databases that use multiple filegroups (typically for partitioned tables or archive data), this shows space allocation per filegroup and which tables live in each.
database_cleanup.sql: Removes old data from specific tables (truncate or batched delete). A parameterized template for archive and cleanup operations.
shrink.sql: Shrinks data or log files. Use with caution, shrinking a data file causes severe index fragmentation and should only be done in specific circumstances (after removing a large amount of data permanently, for example). The script includes warnings about when not to use it.
sql_access/ — User and Permission Management
The scripts you reach for whenever someone needs access to a database or an access audit is required.
role_and_login.sql: Creates a SQL Server login and database user, assigns them to a database role. The template for provisioning a new user.
grant_view_access.sql / grant_statements_access.sql: Granular permission grants. grant_view_access.sql gives SELECT on all tables in a schema. grant_statements_access.sql grants specific statement-level permissions (INSERT, UPDATE, DELETE) on targeted objects.
deny_view_access.sql: Explicit DENY overrides any GRANT. Used to prevent access to specific tables for a user who has broad role membership.
users_info.sql: Lists all users in the current database with their roles and permissions. The starting point for access audits.
who_is_trying_access.sql: Queries SQL Server audit or login failure logs to identify failed connection attempts. Useful when investigating unauthorized access attempts.
drop_user.sql: Cleanly removes a user and their associated database permissions before removing the login.
sql_agent/ — Job Monitoring and Management
monitor_currently_agent_jobs.sql: Shows all currently running SQL Agent jobs with their start time, duration, and step. The script you open when a maintenance job is taking longer than expected.
check_running_and_stop_jobs.sql: Identifies running jobs and provides the sp_stop_job call to stop a specific job if it is hanging or consuming resources it should not.
sql_agent_sessions.sql: Shows which sessions are SQL Agent job sessions vs. user connections. Useful for distinguishing maintenance activity from user workload.
purge_job_history.sql: The msdb.dbo.sysjobhistory table grows indefinitely. This script deletes old job history records, keeping the table manageable and msdb performance healthy.
grant_access.sql: Grants specific permissions for a user to view or manage SQL Agent jobs without being a sysadmin.
query_store/ — Query Store Analysis
Ten scripts for working with SQL Server’s built-in query history tracking.
enable.sql: The Query Store configuration script. Enable it on any database where you want historical query analysis.
last_queries_executed.sql: Shows the most recently executed queries with their execution plans and runtime statistics.
longest_avg_exec_time.sql: Top queries by average execution time. The starting point for identifying slow queries.
highest_avg_row_count.sql: Top queries by average rows returned. Queries returning millions of rows when they should return hundreds are often missing predicates or indexes.
queries_that_recently_regressed_performance.sql: Compares the last execution interval to the previous one and surfaces queries whose performance degraded. This is the script that catches plan regressions automatically.
historical_regression_performance.sql: Shows the performance history of a specific query over time, with plan changes highlighted. When you know which query regressed, this shows you exactly when it happened.
force_plan.sql: Forces a specific execution plan for a query. When a plan regression is confirmed and you need an immediate fix while the root cause is investigated, plan forcing stops the bleeding.
queries_w_multiple_plans.sql: Identifies queries that have been compiled with multiple execution plans. Multiple plans for the same query often indicate parameter sniffing issues.
audit/ — Activity Auditing
audit_toolkit.sql: Sets up SQL Server Audit to track specific events (logins, data access, permission changes) and write them to a file or SQL Server table. A starting point for compliance-driven audit requirements.
audit_users_table.sql: Creates a trigger-based audit on a specific table, logging all inserts, updates, and deletes with the user, timestamp, and changed values. For tables where you need a full change history without enabling server-level audit.
custom_alert_emails/ — Database Mail and Custom Alerts
enable_database_mail.sql: Configures SQL Server Database Mail from scratch: creates a mail profile, configures the SMTP server, and tests the setup.
dbo.usp_send_job_custom_email.sql: A stored procedure that generates and sends a formatted HTML email with job execution results. More useful than the default SQL Agent notification emails. Includes execution time, duration, error messages, and a link to the job history.
sql_profiler/ — Trace and Profiling
automatically_profiler.sql: Creates a server-side trace (the programmatic equivalent of SQL Profiler) that captures slow queries above a configurable duration threshold. Writes to a trace file for offline analysis.
check_traces.sql: Shows currently active traces on the instance. Useful for confirming a trace is running or finding traces left open by previous sessions.
ssrs/ — SQL Server Reporting Services
Six scripts for managing SSRS environments without needing the Report Manager UI.
list_ssrs_objects.sql: Lists all reports, data sources, and folders in the SSRS catalog. The fastest way to get an inventory of what is deployed.
info_reports.sql: Shows report metadata: creation date, last modification, who created it, and the report definition XML.
subscriptions.sql: Lists all report subscriptions with their schedule, delivery method, and parameters. Essential for auditing scheduled report delivery.
users_access_ssrs.sql / check_ssrs_permissions.sql: Show who has access to the SSRS instance and at what permission level. The starting point for SSRS access audits.
analyze.sql: Queries the SSRS execution log to show report execution frequency, duration, and failure rate.
ssis/ — SQL Server Integration Services
create_jobs_schedule.sql: Creates a SQL Agent job that executes an SSIS package from the SSIS catalog. The template for scheduling SSIS package execution through the Agent.
create_proc_trigger_job.sql: Creates a stored procedure that triggers a SQL Agent job. The pattern for situations where you need to invoke an SSIS package from T-SQL (at the end of another process).
ssis_maintenance.sql: Cleans up old SSIS catalog execution records. The SSISDB execution log grows indefinitely.
server/ — Server Configuration and Administration
server_info.sql: A quick instance snapshot: SQL Server version, edition, CPU count, memory, collation, and key configuration settings. The first script to run when connecting to an unfamiliar server.
linked_server.sql: Creates a linked server connection to another SQL Server instance. Includes security configuration for the mapped credentials.
query_with_other_server.sql: Examples of four-part name queries and OPENQUERY against linked servers. The template for distributed queries.
server_roles.sql: Lists all server-level role memberships. The starting point for privilege audits at the server level.
helps/ — Helper Scripts and Templates
A collection of useful utilities that do not fit neatly into one category.
iterate_clean_tables.sql: A cursor-based pattern for iterating over a list of tables and executing a cleanup operation on each. The template for batch maintenance operations.
check_cascade.sql: Shows the cascade delete/update relationships on a table. Before dropping or modifying a table, this reveals what dependent tables will be affected.
create_credential_proxy.sql: Creates a SQL Agent credential and proxy for executing SSIS or PowerShell steps under a specific Windows account.
maintenance_solution.sql: A reference script showing how to download and install Ola Hallengren's scripts directly from the web (using xp_cmdshell or SQLCLR, depending on your setup).
median_of_total_points.sql: A T-SQL implementation of a median calculation (which SQL Server does not have a native aggregate for). Useful for analytical queries where AVG is too sensitive to outliers.
spatial_data/: Three scripts demonstrating SQL Server's spatial data capabilities: creating polygon geometries, querying spatial relationships, and working with route/path data.
triggers/ and functions/
trg_prevent_delete_where.sql: An INSTEAD OF DELETE trigger template that prevents deletion of rows matching a condition. Useful for protecting reference data or audit records from accidental deletion.
dbo.google_data.sql: A scalar function that formats coordinates for Google Maps integration. An example of extending SQL Server with business-specific utility functions.
sql_docker/
A Docker Compose configuration for spinning up a SQL Server instance locally for development and testing. Includes a .gitignore pattern for excluding the data volumes from version control.
When to Reach for Which Category
“Something is slow” > lock/ first, then query_store/
“Disk is filling up” > database_size/, free_space/
“Who has access to X?” > sql_access/, audit/
“Last night’s backup?” > backup_recovery/
“Job failed” > sql_agent/
“Index fragmentation?” > index/
Query slowed down recently?" > query_store/
"Who is trying to connect?" > sql_access/
"SSRS report not working?" > ssrs/
"Configure email alerts?" > custom_alert_emails/
What’s Next?
This solution is part of Data Eyes! 🧿
Data Eyes is an open-source toolkit for SQL Server that I developed over my years of experience. Contributions are welcome!
