bkuk insert failed, cannot obtain lock resources
Posted on 2011-03-02
Many times when I execute our ssis package job to bulk insert data, it return me errors below,
I also have drop all cleanbuffer, procedure cache and set bulk insert to 1000 instead of 10k per batch but still getting same errors, actually this job is trigger from another sql server but actual db is at another set of sql server, is this error from the server which I installed and deploy the package or in actual db server ?
How do we fix this kind of issues ?
Date 3/3/2011 4:39:07 AM
Log Job History (Import)
Step ID 2
Job Name Import
Step Name MportNOw
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
Executed as user: SA. Microsoft (R) SQL Server Execute Package Utility
Version 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 4:39:07 AM Error: 2011-03-03 04:39:38.78 Code: 0xC002F304
Source: DBIMport Bulk Insert Task Description: An error occurred with the following error message:
"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.".
End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 4:39:07 AM Finished: 4:39:41 AM Elapsed: 34.203 seconds.
The package execution failed. The step failed.