Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

migrated from stackoverflow.com Mar 18 '13 at 20:40

This question came from our site for professional and enthusiast programmers.

2 Answers

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.

share|improve this answer
    
Thanks. that helped. –  Pertinent Observer Mar 19 '13 at 21:09
add comment

I agree with everything Remus said and I started writing a long comment and realized this may be an additional answer.

You never indicated that this was causing a problem, just that you happened to catch it. That said if you are seeing waits on Log_Manager latches - it is a good indication that you are potentially not getting the best performance out of your transaction log. This could be due to several factors that you should look into:

1.) As Remus rightly pointed out - look at your log file. Is it constantly growing? Why? Are you shrinking it every night? Is it improperly sized? When this wait happens it is typically because you are growing the log file either because you aren't reusing it or you haven't sized it. These procs could be victims or aggressors (or both). Go to the links Remus suggested and check out this DBA.SE answer where I talk about various reasons why a log file can grow.

2.) Also for the log - if it is not properly sized that could cause some stress. Your log file should be sized right for your environment. The answer linked above explains that and links to some external posts from others that go into more detail there if you suspect that is the cause.

3.) Log Drive performance - while this isn't the primary cause of a log file having to grow, it could definitely cause a log growth to go on longer and could cause a log_manager latch to hang around longer. I would suggest that you look at where your log files are. Log fles are critical to your performance and success with SQL Server. SQL uses a write-ahead log methodology to maintain transactional consistency - If it isn't hardened to the log the transaction cannot be complete. Also, as Remus pointed out, the log file can't grow with instant file initialization so the growths require the operating system to zero out the files. If you are not setup for optimal log file performance you can suffer in many ways, this is potentially one of them (at least an exacerbation to the problem). Some guidelines to help:

  • Generally speaking you want separate storage from your data files - there are caveats here based on what type of SAN you are on and the drive speed, etc. - but in general you want to keep your log files isolated for performance and recovery reasons.
  • You want your fastest IO to be reserved for your log files (some can argue TempDB and I'll even join them depending on the environment, but log writes need to be fast, keep that in mind with your performance)
  • Again this will differ depending on your storage provider but Log Files don't typically love RAID 5 - Mirrors or Mirroring and Striping tend to work out better here.
share|improve this answer
    
Thanks. That answers my question –  Pertinent Observer Mar 19 '13 at 21:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.