So I've been beating my head against the wall for two days about this and I can't find a solution. I'm running a script to stage a large amount of data. This script requires roughly 35 million locks in order to complete; this translates into needing 3.36GB of memory available for locks. I set the number of locks to 37 million in the configured values, however after the reboot the running values are only allowing for 16,695,296 locks.
This corresponds to roughly 14% of available memory. As SQL Server 2012 can use 40% of available memory for locks, I should have plenty a room, but I don't. My question is: why can't SQL Server utilize the extra 26% of available lock memory? Everything I've read says that it should be able to.
Also, I did try running the locks on dynamic, but that also failed for the same reasons and with the same error codes: error 1204, instance can not obtain a lock resource. I know it's the server configuration and not the script, because I ran the exact same script, with the exact same data set on an older server (SQL Server 2008) without issue.
I appeal to the database admin gods for help; help me oh great ones!
Edit: This is a VM on a fail-over cluster, the VM has 16 GB of memory allotted to it. I've given SQL Server 12GB and left 4GB available for the host operating system.