Maximum stored procedure, function, trigger, or view nesting level exceeded

Our team provides on-premise and virtual private cloud upgrade management for a number of products including TrialWorks case management software. This week we rolled out TrialWorks version 11.4a and all of our upgraded organizations were affected by a problem on the Depositions tab: new records being added threw a nested trigger exception.

While we are all human and capable of making mistakes, the nature of this issue seemed more in-line with a bug than an error. TrialWorks no longer offers free support for upgrades unless they fit into one of two criteria:  they are managed by their Octopus Deploy tenant or they specifically encounter a “Missing Objects” message during the upgrade process.  Since majority of our customers choose to self-manage their upgrades we are often the first line of defense for systemic issues before escalating issues to a vendor.  Besides, we relish the opportunity to debug a problem and deliver a solution.

Let us show you how simple it can be to troubleshoot work-stopping malfunctions and debug database errors.  As it turned out, the issue here was simply a relic that stayed behind in every database from historical changes.

The Problem:

The issue was that a user clicking on the Deposition’s tab would be able to enter information into Deposition’s Detail, but attempting to close the window using the “door”  button would prevent a save of the record (insert) and return the following two messages:

—————————

Error saving record

—————————

Unable to save record.

(1st attempt): -2147217900: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

(2nd attempt): -2147217900: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

The Solution Process:

Third-party software troubleshooting is tough because it deals with a number of unknowns and the success of the process is constrained by resources that are exposed to the customer.  In this case we could tell a few things about the nature of the issue. For example, the message referenced a “trigger” which is a database-side component and nesting suggested that there was some loop within the process. The reason we thought loop instead of a functionality is because we knew triggers were used extensively within this SQL Server database and nesting is typically permitted to a degree.

To confirm, we reviewed the “TW_DSV_11.4.sql” file looking for trigger changes on Depositions.  The review did not reveal anything obviously wrong with the script and we felt confident that the TrialWorks QA process (and the response received from their support) would have caught a script issue by this point.  Instead, we focused on deletions of relics – old things that were once used and should have been removed over time.  While we did not find a clear cut deletion that could have failed as the routine ran, we did find a comment that said:

That made things extremely simple.  According to that comment a trigger called ” [dbo].[Deposition_UTrig] ” was removed at some point in the past and the presence of this trigger would surely be responsible for the behavior. Given all of these databases were old and in production for many years, a relic such as this was highly probable.  Sure enough, we found the trigger in every instance and removed it.

Debrief:

This just goes to show you how good coding practices can help customers solve a problem and a tribute to shared responsibility.  After all, running complex systems today is a collaboration of vendors, suppliers, customers, and end users.  Here, a simple comment in source code that was clear, concise, and detailed solved a problem that easily could have consumed resources and affected production.  Kudos to good programming practices and easily readable code.