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

SQL
1 Comment

SQL SERVER - Msg 3013 - Cannot Create Worker Thread. BACKUP LOG is Terminating Abnormally backupazure 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.

Solarwinds

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)

Solarwinds
, , ,
Previous Post
SQL SERVER – Msg 0, Level 11 – A Severe Error Occurred on the Current Command. The results, if Any, Should be Discarded
Next Post
SQL SERVER- Msg 22004, Level 16 – xp_readerrorlog – Failed to Open Loopback Connection. Please See Event Log for More Information

Related Posts

1 Comment. Leave new

  • 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.

    Reply

Leave a Reply

Menu