SQL SERVER – Msg 3013 – Cannot Create Worker Thread. BACKUP LOG is Terminating Abnormally

One of my clients sent email to know my quick suggestion on one of the error messages. Let us learn how to fix error related to worker thread.

Cannot create worker thread.
BACKUP LOG is terminating abnormally.

As per my client, this error was not consistent, but it does happen multiple times a day.  They also told that they had many databases backing up at the same time.

WORKAROUND/SOLUTION

I told them to increase worker threads.

Below is the script to find current value

USE master
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;
sp_configure 'max worker threads' 
GO  

If the value is zero, then SQL would create threads based on number of CPUs.

As per the article the default values for worker threads is set for 576 for 8 processors. I have asked to increase to 640 threads as they had many databases.

EXEC sp_configure 'max worker threads', 640;  
GO  
RECONFIGURE;  
GO  
EXEC sp_configure 'show advanced options', 0;  
GO  
RECONFIGURE;  
GO 

Have you ever changed default value of parameter? This is very unique scenario and I have not seen quite often in the industry. I would love to know your feedback about this blog post and share your story if you have ever faced such situation in the production environment.

Reference: Pinal Dave (https://blog.sqlauthority.com)