Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems

This is an interesting case that came up recently, and that I’ve seen a few times before, but which I haven’t seen explained anywhere.

Using database mirroring, you may see 1204 and 1454 errors on the mirror server with no other databases or activity on the mirror server apart from database mirroring:

2013-01-15 12:00:00.410 spid29s Error: 1204, Severity: 19, State: 4.
2013-01-15 12:00:00.410 spid29s The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.
2013-01-15 12:00:00.430 spid29s Error: 1454, Severity: 16, State: 1.
2013-01-15 12:00:00.430 spid29s Database mirroring will be suspended. Server instance 'instancename' encountered error 1204, state 4, severity 19 when it was acting as a mirroring partner for database 'dbname'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

With no activity on the mirror server apart from mirroring, why are locks being taken?

The answer is that locks must be acquired when a transaction is rolled back on the mirror and is just how logging and recovery work. I discussed this some in my post Lock logging and fast recovery back in 2009 – locks are acquired during rollback to ensure that other transactions don’t prevent rollback from completing. The behavior is the same when a mirrored transaction rolls back – the locks need to be acquired on the mirror before the rollback can commence.

You can easily see this for yourself. Create a mirroring session and then a constant insert workload of small transactions. If you look at the locks on the mirror server using sys.dm_tran_locks, you’ll see no locks being acquired in the mirror database. Now create a transaction on the principal server that inserts 10,000 records. Roll it back and quickly examine the locks on the mirror server – you’ll see thousands of locks being acquired in the mirror database.

Why can we see 1204 errors with database mirroring?

Each lock structure takes up a small amount of memory and the more locks that are acquired and held at any one time, the more memory is required for the lock structures. On a memory-constrained system it’s possible to run out of lock memory and result in error 1204.

With database mirroring, if the principal server has a lot more memory than the mirror server, and there are a lot of large rollbacks occurring, or there a multiple mirroring sessions where rollbacks are occurring, it’s possible that the mirror server will run out of lock memory, resulting in errors 1204 and 1454 as above.

How can we stop 1204 errors occurring?

The easiest fix for this (and to hopefully prevent it happening) is to ensure that the mirror server has the same amount of memory as the principal server – so the principal should not be able to create more locks than the mirror server can accommodate. Even then, you may still see these errors if there is a lot of rollback activity across mirrored databases and it all happens to be replayed on the mirror server at the same time – in which case you need more memory on the mirror server than on the principal.

You need to also ensure that nothing else is using memory on the mirror server – e.g. using database snapshots, or any other activity apart from just mirroring.

I hope this explanation helps prevent some of you out there wasting time trying to troubleshoot this issue.

8 thoughts on “Database mirroring: avoiding ‘cannot obtain a LOCK resource’ problems

  1. We have a system where the customer is running SQL Server 2008 R2 CU8 on both the principal and mirror servers. They have 256 GB RAM on both servers and Max Server Memory is set to 230 GB on each server. They have 4 dbs which are being mirrored and they are getting the above errors on the mirror server. As per the above posting, if the amount of memory configured on the mirror server is the same as the principal, then they should not be hitting this. Is my understanding correct? Thanks!

    1. No – because of the way mirroring works, the rollbacks from various databases may all get replayed on the mirror at the same time, whereas they didn’t happen at the same time on the principal. Are they doing anything else at all with the mirror server? E.g. using database snapshots? This will decrease the available memory.

  2. Hi Paul,

    Thanks for the great article. I am in the process of testing Index rebuild when the Database Mirroring in High Performance Mode ( SQL 2008 R2). The rebuild index process completed in 20 minutes in Principal. Principal mirroring monitors shows following information. Unsent Log 0KB, Oldest Unsent Transaction is none. No other transaction is running in Principal.

    In the Mirror side, the Unrestored Log is close 19 GB (even after 45 minute ) and Current Restore rate is only 8 MB. The current restore rate is very low and extremely slow. When I did the rebuild process using High Availability mode, the current restore rate in Mirror went up to 60 MB per sec.

    1) Any idea why the restore rate is very low in High Performance Mode?

    2) I did the log backup after the index rebuild. Status column in DBCC Loginfo is changed to 0 for 50% of VLFs. Rest of them still shows 2 and status is not changing to 0 even I perform the log backup multiple times. is this behavior due to the fact that mirror has not completed the redo process?

    Regards
    Praveen

  3. Paul Sir, only at time of rollback, locks are acquired? While we have read in your blogs rollback acquire same locks which are required during commit.

  4. Hello Paul,
    I am a long time fan and reader of your blogs, and I am curious to ask your option on this topic.
    Mirroring has been deprecated since SQL 2012, but only recently Microsoft offered an equivalent option in Standard Edition, the so called Simple Availability Groups.
    From my experience as DBA, Mirroring is significantly easier to setup and automate than AGs, since we don’t have to deal with Windows Failover Clusters, quorums and multiple IPs. Now I learned that even Amazon RDS uses Mirroring for multi availability zones.
    Now comes the question, in your personal opinion, what’s the future of SQL Mirroring?
    Do you think Microsoft will remove it any time soon?

    Many thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.