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.
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.
SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns
A query slowed to over 10 minutes because of a big key lookup. Here is how I optimize key lookup operations by creating an index with include columns.
SQL SERVER – Steps to Change IP Address of SQL Server Failover Cluster Instance
Moving to a new VLAN? These are the exact steps to change the IP addresses of the Windows cluster nodes and of a SQL Server failover cluster instance.
SQL SERVER – Initializing the FallBack Certificate Failed With Error Code: 1, State: 20, Error Number: 0
After a service account password change, SQL Server would not start: Initializing the FallBack certificate failed, error code 1. Here is the fix.