Forum Discussion
the SQL server database engine cannot obtain a lock ressource at this time
hello
I have a problem with the configuration of SQL Server
I have SQL Server 2019 with the last cumulative update
i fixed the max server memory with 40000 Mo =40 Go
and maxdop to 0
i have enough disk space
the problem is when i execute this query :
(insert into table A select * from table B) this probleme happen
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.
but when i execute the same wuery like this ( select * into table A from table B ) the query work fine with no problem
Can anyone help me
thanx a lot
3 Replies
One difference is that
SELECT col1, col2, [...] INTO tableA FROM tableBCreates a new table, tableA, then performs the insert operation.
INSERT INTO tableA SELECT col1, col2, [...] FROM tableBon the other hand, appends the selection to an existing tableA.
While I cannot verify this, I'd assume SELECT INTO puts a table lock on the table, as it's created as part of the statement, whereas INSERT INTO is attempting to use clever locking to minimize impact on SELECTs on the target table, resulting in a hot mess exhausting your resources.
As others have suggested, I'd suggest specifying WITH (TABLOCK), which will impose a table wide exclusive lock for the INSERT.
You can also get this error message if there's not enough memory resources available.- Try to set Database recovery model to 'Simple' or 'bulk-logged', and use exclusive lock on target table like below
insert into table A WITH (TABLOCK) select * from table B AhmedYeddes , see Microsoft articles
MSSQLSERVER_1204 - SQL Server | Microsoft Learn
Configure the locks (server configuration option) - SQL Server | Microsoft Learn
for more details about.
