Msg 217 appears when triggers fire each other past the nesting limit of 32. A reader asked what happens when one trigger calls another. Two AFTER INSERT triggers show it, and both tables stay empty.
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
Clear Procedure Cache for One Database in SQL Server
To clear procedure cache for one database, run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE inside that database. This post tests it on two demo databases, shows that the other database keeps its plans, and shows how to remove a single plan by its plan handle. It also covers the cost and the lighter alternatives.
SQL SERVER – Simple Script to Match Session ID to OS Thread ID
How is a SQL Server Session ID mapped to an OS Thread ID? Here is a simple script to match Session ID to OS Thread ID, with the scheduler and task state.
SQL SERVER – Authentication in SQL Server (Windows and Mixed Mode) – GDPR Series
Authentication in SQL Server identifies the caller through supported identity choices. My GDPR series considered those choices alongside access controls.
SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE
Has your identity column ever jumped by 1000 after a restart? SQL Server 2017 fixes it with IDENTITY_CACHE. Here is a demo with the setting ON and OFF.
Kill All Processes for a Database in SQL Server
To kill all processes for a database, set it to SINGLE_USER with ROLLBACK IMMEDIATE and then back to MULTI_USER. This post tests that method against two open sessions, compares it with a KILL script and with RESTRICTED_USER, and explains when each one fits. It also covers what the killed sessions see and why you should not do this on production without asking.