Knowledge Sharing: Maximum Stored Procedure, Function, Trigger, or View Nesting Level Exceeded (limit 32)

Ghost of UAT Refresh

Error: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Couple of day back I did uat database refresh from production and all the steps were executed successfully as planned

1. Take user backups from Uat database

2. Copy latest backup from prod to restore

3. Restore prod backup to uat database

4. Apply security permissions backedup at step 1

5. Fix orphan user.

When I came back on Monday I saw a ticket in my queue where user said “After refresh they are not able to run couple of scripts , Please check if restore and permissions are applied in last databases refresh are correct.”

I don’t ever like these kind of tickets, It needs rework and unnecessary frustration. But now some thing messed up.

To keep my self safer side I always try to do things with jobs so whenever I need I can check history and find out what went wrong , so I checked job and found nothing was wrong with any of steps. This means there is a issue from application end. As a dba I always feel good to say issue is not from DBA but you cant leave like that until issue is fixed. So give a quick call to application guys and confirmed what errors they are getting.

To my surprise ticket error was A cheese to mouse, they just want to engage dba. Actual issue was when ever they deploy script (includes , create alter , execute) , it throws error

Error while deploying table

Oh Man , This issue is not a refresh issue its due to some application nested process. And I have to give some clues to application else they will not gona leave me.

So did some quick checks.

I tried to execute stored procedure :

Then I tried to alter one table and here I got this error

One more check , tried to create one new table “Test”

I got it , Issue is only when we use alter and create table , in short when we use DDL (Data definition language) command.

So this must be some trigger which is creating issue and as I am getting issue on new table it must be database level. But to do a check I thought to check triggers in database using below query

/**********Triggers in database ***************/

select so.name [Trigger], so2.name [Table]

from sysobjects so
join sysobjects so2 on so.parent_obj = so2.id
and so.xtype = 'tr'
order by so2.name
/*********************************************/

Ah today is full of surprises , database have 1085 triggers.

So I decide to stick to DDL trigger and I will only go deep if issue demands.

Now I have to check DDL triggers

/**********List of DDL triggers on DATABASE *********/

SELECT * FROM sys.triggers where parent_class = 0

--parent_class = 0 ; Trigger on database

--Parent_class = 1 ; Trigger on table
/*************************************************/

I am lucky there is only one trigger.

Before debugging trigger , I prefered to disable and then check nested triggers setting in sp_configure.

DISABLE TRIGGER trgLogDDLEvent ON DATABASE

ENABLE TRIGGER trgLogDDLEvent ON DATABASE;

While trigger was disable there was no issues reported.