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.
no of locks
as SQL Server is smart enough to allocate lock memory dynamically. If it was SybaseASE, you could have tune that.select SERVERPROPERTY ('Edition') AS ServerEdition, CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) + ' + ' + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build' + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion, RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.]
. Also, have you compared thesys.configurations
on both servers ? Especially Max Memory setting.SELECT request_session_id, COUNT (*) num_locks FROM sys.dm_tran_locks GROUP BY request_session_id ORDER BY count (*) DESC
to find out what is using up all the locks.