Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it

Question: There was a failure on a regular SQL Server job. There are now repeated errors focused on NESTING_TRANSACTION_FULL. Double checked all usual resource indicators such as CPU,IO,Memory and all appear to be within normal boundaries

Timeout occurred while waiting for latch: class 'NESTING_TRANSACTION_FULL',

I checked the waiting tasks and discovered the wait type as LATCH_EX with resource description as :

NESTING_TRANSACTION_FULL (0000000E64CA80F8)

Answer:This is a tough nut to crack. I’ve experienced this problem in the past under different circumstances.

These are some of the methods I’ve used to solve or alleviate the symptoms. Without full understanding whether it was a product bug or sever contention

There is some research around the internet , which I’ve found to be useful. It’s helped inform my troubleshooting.

1. Check for parallelism overuse.CPU 100% - Check for Parallel Queries - SQL Server DBA
2. Checking for delays on the IO sub system SQL Server – LOGMGR and how to reduce it - SQL Server DBA
3. If you have an RCSI enabled database and it’s SQL Server 2012 or 2014 read this knowledge base article Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014