Trying to take a test database offline, we hit Msg 5061: ALTER DATABASE failed because a lock could not be placed on database. Here is how to fix it.
SQL SERVER – OLE DB Provider ‘Microsoft.ACE.OLEDB.12.0’ for Linked Server ‘(null)’ Returned Message ‘Unspecified Error’
Reading Excel through OPENROWSET failed: the OLE DB provider for linked server (null) returned message ‘Unspecified error’. Here is how I fixed it.
How to Grow All Files in a File Group Equally? – TRACE FLAG 1117 – Interview Question of the Week #201
How do you grow all files in a file group equally? The answer is Trace Flag 1117, plus the AutoGrow option that SQL Server 2016 brought for filegroups.
SQL SERVER – Huge Size of SSISDB – Catalog Database SSISDB Cleanup Script
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.