MAXDOP (max degree of parallelism) controls how many CPU threads SQL Server can use for a single parallel query. The default value of 0 allows SQL Server to use all available processors, which can cause large parallel queries to monopolise the server. This script reads the CPU topology from system DMVs and calculates …

Tables without primary keys are heap structures with no guaranteed row ordering and no unique row identifier. They cause full table scans in queries that could use seeks, slow down DELETE and UPDATE operations, and are incompatible with SQL Server replication and Change Data Capture. This script finds every user table …

Query Backup Duration and File Size from SQL Server History This script queries SQL Server's msdb backup history to report the backup type, start and finish time, duration in minutes, and file size in both megabytes and gigabytes for every backup across all user databases on the server. Purpose and Overview Knowing …

Find Active Long-Running Queries in SQL Server This script queries sys.dm_exec_requests and sys.dm_exec_sql_text to show all currently executing queries on the SQL Server instance, sorted by how long they have been running, along with CPU time, I/O activity, blocking status, and wait type information. Purpose and …

Report SQL Server Agent Job Status and Failure History This script queries the SQL Server Agent job history stored in msdb to produce a detailed report of each job's most recent run status, failure and success counts over the last 60 days, total lifetime run counts, and job step information including subsystem type and …

Kill SQL Server Sessions with Targeted Filters Using KILL and dm_exec_sessions This article covers a practical T-SQL script that kills one or more SQL Server sessions (SPIDs) while giving you precise control over which sessions are targeted. The script uses sys.dm_exec_sessions to query active sessions and a cursor to …

Check SQL Server Backup Age Across All Databases 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 Database backups are your last line …

Audit SQL Server Database User Permissions and Role Memberships This script queries the SQL Server system catalog to produce a complete audit of database-level permissions, covering direct object grants to users, permissions inherited through database roles, and permissions granted to the public role — all in a single …

Search Stored Procedure and View Definitions in SQL Server This script searches the text of stored procedures, views, functions, and triggers in the current database using sys.sql_modules, returning any programmable object whose name or definition contains a given keyword. Purpose and Overview Finding which stored …

Search the SQL Server Error Log with xp_readerrorlog This script reads and filters the SQL Server error log using xp_readerrorlog and sys.sp_readerrorlog, supporting keyword search, date range filtering, and access to archived log files — all from T-SQL without touching the file system. Purpose and Overview The SQL …