Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it

09 January,2016 by Jack Vamvas

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

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer