Sql-server – What does wait_resource LOG_MANAGER mean in SQL Server 2008

sql serversql-server-2008

Let's say we have two Stored Procedures A & B within the same database.

Stored Procedure A:

  1. Executes for a long time
  2. Has transactions within
  3. Makes Calls over Linked Server

Stored Procedure B:

  1. Simple SP that inserts/updates a table
  2. Quick. Within 1 second

A & B execute on the same database but DO NOT have any common tables.

Yesterday, B was blocked by A and I found that the

  • wait_type was LATCH_EX
  • wait_resource was LOG_MANAGER

What does this LOG_MANAGER mean? What kind of resource are they waiting on? Plus, does it have anything to do with with transactions within the 'A' SP.

A quick Googling indicates that it has something do with growth of logs, but I would appreciate a more indepth explanation.

Best Answer

Whenever the log fills up it has to grow. See How to shrink SQL Server log for an explanation how it grows. Any transaction could be the one that triggers growth, simply because any transaction could be the one that happened to take the very last unused byte in the existing file. When growth occur all transaction block. And if you have default 10% auto-growth in place and your database log already grew out- of check then the log growth can be very slow as instant initialization is not possible on log files.

What matters is that you have a log growing, so you must take immediate measures to understand why is growing and to mitigate accordingly. Go read Factors That Can Delay Log Truncation right now. The fact that you caught B in the fact of being blocked by A could be just random. B could had just as easily trigger the growth, it would had taken exactly the same time to grow it, and you would have catch A being blocked by B. The fact that A is causing a long running transaction may be one of the factors that prevent truncation, but that has to be confirmed.