SSISDB grew huge on a client’s server. Here is my cleanup script for the SSISDB catalog database, and why I avoid scripts that modify system objects.
Schema Changes History Report: See Who Dropped a Table
The Schema Changes History report shows who created, altered or dropped a table. This post reads the same default trace with T-SQL, tests it on a table and a database drop, and explains the limits: file rollover and shared logins. It ends with ways to keep the history longer.
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.
Multi-Statement Table-Valued Functions and Interleaved Execution
See how interleaved execution corrects estimates for multi-statement table-valued functions and when an inline rewrite wins.
Dynamic SQL Result Into a Variable with sp_executesql
To get a dynamic SQL result into a variable, use sp_executesql with an OUTPUT parameter. This post tests the pattern on a small demo, explains Msg 214 and shows counts, JSON, new identity values and generic table names. It also covers INSERT with EXEC for result sets.
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 Trim TIME Part in DATETIME Values? – Interview Question of the Week #200
How to trim TIME part in DATETIME values? A reader wanted to keep only the date and the hour, so here are ways to cut off the minutes and seconds.