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

When I was trying  to insert large data into a table.  For example:

INSERT INTO tblPost

SELECT TOP(60000000) S.ntUserID,S.ntUserID,DATEADD(MINUTE,ntTime,S.dtStartTime ),DATEADD(MINUTE,ntTime,S.dtStartTime )

FROM tblSystemLogin S WITH(NOLOCK READUNCOMMITTED) CROSS JOIN (

     SELECT TOP(40)

          ROW_NUMBER() OVER(ORDER BY message_id) * 10 AS ntTime

     FROM sys.messages WITH(NOLOCK READUNCOMMITTED)

)M

After 22 minutes I got following error message:

Msg 1204, Level 19, State 4, Line 1

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.

Solution:

Then I used table hints TABLOCK for minimal logging. So my modified query is:

INSERT INTO tblPost WITH (TABLOCK)

SELECT TOP(60000000) S.ntUserID,S.ntUserID,DATEADD(MINUTE,ntTime,S.dtStartTime ),DATEADD(MINUTE,ntTime,S.dtStartTime )

FROM tblSystemLogin S WITH(NOLOCK READUNCOMMITTED) CROSS JOIN (

     SELECT TOP(40)

          ROW_NUMBER() OVER(ORDER BY message_id) * 10 AS ntTime

     FROM sys.messages WITH(NOLOCK READUNCOMMITTED)

)M

Then 60000000 records has inserted successfully in the table!!!