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.
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)
It is not uncommon to see this at a non default value. What is neat is you can set this value where you want and sql server only spins up what is needed and disposes of the worker thread once a threshold is met, I think 15seconds.
For that machine I wouldn’t go over double the default values and monitor for thread exhaustion if they are going to backup many databases at once.
Another alternative would be to break up and stagger the backup operations for a few databases at a time so thread spawning never reaches threshold.