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

A client emailed me about log backups failing several times a day, never at the same time, never the same database. The message said the server could not create a worker thread. Raising the worker thread setting is the answer you will find everywhere. It is sometimes right. It is more often a way of not looking at the real problem, and I want to show you how to tell which one you have.

The Error

Cannot create worker thread.
Msg 3013, Level 16, State 1
BACKUP LOG is terminating abnormally.

Two messages, and only the first one matters. Msg 3013 is the backup saying it gave up. The line above it is the reason, and it is nothing to do with backups. SQL Server ran out of workers, and the backup happened to be the thing asking at that moment.

That is why this never lands on the same database twice. It is not the database. It is the clock.

What a Worker Thread Limit Actually Is

SQL Server does not make one thread per connection. It keeps a pool and hands workers out to tasks. The size of that pool has a ceiling, and by default you do not set it. SQL Server calculates it from the processor count.

Here is my own instance, and every number below is from it:

SELECT cpu_count, scheduler_count, max_workers_count, affinity_type_desc
FROM   sys.dm_os_sys_info;

cpu_count  scheduler_count  max_workers_count  affinity_type_desc
16         16               704                AUTO

Sixteen processors, 704 workers. The documented formula for 64-bit is 512 workers, plus 16 more for every processor above four. I checked it against my own box rather than trusting it:

SELECT cpu_count,
       CASE WHEN cpu_count <= 4 THEN 512
            ELSE 512 + ((cpu_count - 4) * 16) END AS formula_says,
       max_workers_count                          AS sql_server_says
FROM   sys.dm_os_sys_info;

cpu_count  formula_says  sql_server_says
16         704           704

Exact. Put 8 through the same formula and you get 576, which is the number you will see quoted in most articles on this error. It is correct, for a machine with eight processors and no others.

Read the Setting Correctly

The usual advice is to run sp_configure. You do not need to, and the catalogue view is easier to read and safer, because it changes nothing:

SELECT name, value, value_in_use, minimum, maximum
FROM   sys.configurations
WHERE  name = 'max worker threads';

name                value  value_in_use  minimum  maximum
max worker threads  0      0             128      65535

A value of 0 does not mean no workers. It means automatic, and the real number is the 704 from the view above. People see the zero, panic, and set a number. Do not read this column on its own.

Note the maximum too. SQL Server will let you type 65535. It will not thank you for it.

Before You Change Anything, Look at the Waits

When a task wants a worker and cannot have one, it waits, and the wait has a name. THREADPOOL. This is the query that decides whether you have a problem:

SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms,
       CAST(wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS decimal(10,3)) AS avg_ms
FROM   sys.dm_os_wait_stats
WHERE  wait_type = 'THREADPOOL';

And here is why you must look at all four columns. My instance, sixteen hours after starting:

waiting_tasks_count  wait_time_ms  max_wait_time_ms  avg_ms
133896               120502        40                0.900

A hundred and thirty-three thousand THREADPOOL waits. That number alone looks alarming, and a lot of people would go and change the setting on the strength of it.

Now read the rest. Two minutes of waiting spread across sixteen hours. Under a millisecond each. The longest any task ever waited was 40 milliseconds. That is a healthy server. A worker being handed over is a wait, and waits happen constantly on a machine that is doing its job.

THREADPOOL waits existing proves nothing. Their size is the whole story. On a genuinely starved server the maximum runs into seconds and the average climbs with it.

The Live Check

Wait stats are cumulative since startup, so they tell you about the past. For right now:

SELECT SUM(current_workers_count) AS current_workers,
       SUM(active_workers_count)  AS active_workers,
       SUM(runnable_tasks_count)  AS runnable_tasks,
       SUM(work_queue_count)      AS work_queue
FROM   sys.dm_os_schedulers
WHERE  status = 'VISIBLE ONLINE';

current_workers  active_workers  runnable_tasks  work_queue
192              130             0               0

192 workers alive out of a possible 704. Nothing queued. work_queue_count is the column that matters. It counts tasks that have been accepted and have no worker to run them. Zero is what you want. A number above zero that stays above zero is thread starvation, and it is the only reading that settles the argument.

Run it a few times during your backup window rather than once at lunchtime. This error only happens when the server is busy, so a reading taken when it is quiet tells you nothing at all.

When Raising It Is Right

If work_queue_count sits above zero during the failures, and THREADPOOL maximums are in seconds, you are genuinely short. Raise it, modestly:

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

Pick a number a little above what SQL Server chose for you, not a number you like the look of. Every worker is real memory, half a megabyte on 64-bit, so 2,000 workers is a gigabyte you have taken away from the buffer pool to hold threads that are mostly asleep.

And this setting needs a restart of the service before it takes effect, which is worth knowing before you change it at nine in the morning and expect the afternoon to be different.

When Raising It Is the Wrong Answer

My client told me the important part in passing, the way people do. They had many databases backing up at the same time.

That is the cause. Every backup takes workers of its own, and they all wanted them at once. Raising the ceiling lets more backups run at once. It does not make the disk faster, and the backups were already competing for that too.

So look at these before you touch the setting.

How many log backups start at the same minute. If it is forty, stagger them. Spread across five minutes, the same forty backups cost a fraction of the workers.

Whether anything is blocked. A blocked session holds its worker while it does nothing. A hundred sessions blocked behind one transaction is a hundred workers gone, and no amount of raising the ceiling fixes a blocking chain.

Whether parallelism is set sensibly. A parallel plan takes a worker per thread per operator branch, so one query can take twenty. If MAXDOP is left at 0 on a machine with a lot of cores, a handful of reports can eat the pool between them.

Each of those is a real fix. Raising max worker threads is a way of making room for the problem.

What I Would Do First

Capture work_queue_count and THREADPOOL every minute for a day, or at least across the backup window. Then look at the failures and see whether the two line up.

If they do, stagger the backups before you raise anything, because it is free and reversible.

If they still fail after that, raise the setting to a modest number and restart. By then you will know why, which is a better position than where most people start.

Msg 3013 is not a backup problem, it is a busy server that ran out of hands, and a backup was simply next in the queue.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.