What’s the issue?
The RECURSIVE_TRIGGERS database option controls whether triggers are allowed to fire themselves recursively. When enabled, a trigger that performs an action causing the same trigger to fire again (direct recursion) will continue to execute until the call stack limit is reached. The setting is off by default, but it can be enabled intentionally for specific scenarios or accidentally during database migrations and template based deployments.Why is this a problem?
Recursive triggers are notoriously difficult to debug and can produce unexpected behavior that is hard to trace. A single row update can cascade through multiple trigger executions, resulting in severe performance degradation, unintended data modifications, or stack overflow errors when the 32 level nesting limit is hit.Most applications do not need recursive trigger behavior, and when it is enabled without a clear design reason, it typically reflects a misconfiguration rather than an intentional choice. The risk is compounded because developers writing triggers often do not realize the option is enabled, leading to bugs that only surface under specific data conditions.
What should you do about this?
Identify affected databases by querying sys.databases where is_recursive_triggers_on = 1. For each one, confirm with application owners whether recursive trigger behavior is actually required by design. In the vast majority of cases it is not, and the setting should be disabled with ALTER DATABASE [DatabaseName] SET RECURSIVE_TRIGGERS OFF;.If recursion is genuinely needed for a specific business rule, consider whether the logic can be rewritten to avoid it, since explicit procedural code is usually easier to maintain and reason about than recursive trigger chains.