SQL Server Agent Job History can be queried from msdb. A New Zealand customer requested this report during our weekly health check.
Diagnosing Blocking Chains With sys.dm_exec_requests
Trace blocking chains from active requests to a candidate head session, inspect sleeping transactions, and verify the owner before action.
How to Shrink All the Log Files for SQL Server? – Interview Question of the Week #203
Question: How to Shrink All the Log Files for SQL Server? Here is the script, plus why you should shrink only log files and never data files.
Find the Oldest Query Plan in the Plan Cache
To find the oldest query plan in the plan cache, sort sys.dm_exec_query_stats by creation_time. This post tests the idea on a demo with three procedures, compares the oldest plan with the server start time, and lists what clears the plan cache.
Columnstore Rowgroup Health: Finding Small and Open Rowgroups
Inspect columnstore rowgroup health, understand small and deleted groups, and choose reorganize or rebuild from measured evidence.
SQL SERVER – Get List of the Logical and Physical Name of the Files in the Entire Database
During a Health Check we needed the logical and physical name of every file in every database on the instance. Here is the script I built, with the file type too.
How to Find Size of the Index for Tables? – Interview Question of the Week #198
How do you find the size of the index for tables? A client asked me during a Health Check, and this script lists schema, table, index and size in KB and MB.