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 'dbnam...