Intermittent "nesting level exceeded" error

Question

Post reply

Intermittent "nesting level exceeded" error

michio42

SSC Enthusiast

Points: 173
More actions
July 29, 2022 at 6:16 pm

#4070077

We're having a very peculiar problem with the "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" error and I was just wondering if the circumstances rang a bell with anybody. I'm certainly not expecting any form of in-depth investigation - that's my job.
Here are the details:
We continually run some automated tests. These call SPs etc. The database has triggers on tables.
These are run on new restores of the same backup file.
Restores are made to 2012, 2014, 2016, 2017 and 2019 instances on a number of different servers (i.e. we are running 2012 on several servers, 2014 on several servers, etc.)
As of about a month and a half ago, we have had occasional instances of the nesting error.
These have only been on 2012 instances - none on 2014, 2016, 2017 or 2019.
They have only been on two particular 2012 instances - but not always on these two instances.
We have five other 2012 instances where it has never happened.
The error is reported in a stored procedure at a line where records are inserted in to a particular table.
The table has a trigger. All that trigger does (apart from read operations) is write to a different table.
That other table has a trigger, but no write operations, other than to itself.
What's perplexing us is the fact that it's an intermittent failure when there have been no code changes.
Does this ring a bell with anybody? i.e. mysterious "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" errors on a 2012 instance?
Thanks.
August 31, 2022 at 9:10 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 2

michio42 wrote:

That other table has a trigger, but no write operations, other than to itself.

Just at a quick glance, I'm thinking the above from your original post is the source of the issue. The trigger is updating something in the base table to cause it to fire again and that causes it fire again, etc, etc for at least 33 iterations where it, of course, blows up. I know there's a setting for this at the server level but I don't remember if there's one at the table or trigger level because I've never actually run into such a problem before. Something like "cascade on update" or some such.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 3

I just checked the MS documentation... Here's the link of the document section I'm looking at.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql#recursive-triggers

It's amazing to me that this is still only a server level option but it would appear that you can disable recursion without disabling nesting. Here's the quote from one of the paragraphs at the link above...

Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

The make it sound like you can disable Recursive tricks without disabling Nested triggers. I don't know because I've never had these issues before.

I do know that there's a system function that will let you see the level of transactions running. Perhaps adding a test for that in the trigger in question would prevent the situation.

Here's the documentation for the function I'm talking about.

https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed B Hall of Fame Points: 3623 More actions · Answer 4

If the second table has a trigger that writes to itself, what would stop it running to infinity if it didn't error out at 32?

When the trigger does not generate the max recursion error, are you sure that it fired at all? I would check whether you have recursive triggers enabled. On the server I'm currently using none of the databases have recursive triggers enabled. It may be that your code will always reach 32 if allowed to.

SELECT [name] AS DatabaseName,    
       is_recursive_triggers_on AS RecursiveTriggersEnabled
FROM   sys.databases ;