May 5, 2009 at 7:30 am
We have a stored procedure that is used to supply data to a third party program. The program requires large datasets, so we give the program a view to a table that is refreshed daily.
The procedure that refreshes the table started failing after upgrading from SQL Server 2005 Standard to SQL Server 2008 Standard.
This is the error:
"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. [SQLSTATE HY000] (Error 1204). The step failed."
We have 12GB of RAM on the server, which is 32-bit using PAE. The memory appears to be getting used if I look at task manager.
The stored procedure inserts 20 million rows of data in 2 steps, 10 million at a time.
Just puzzled that it worked before the upgrade, but not anymore. I'm thinking of splitting up the insert into smaller parts, partitioning by year.
May 5, 2009 at 8:41 am
What is number of locks on the server? Is it configured dynamically? Check article at http://msdn.microsoft.com/en-us/library/aa258769(SQL.80).aspx and http://support.microsoft.com/kb/323630. Both articles can explain a lot.
Alex Prusakov
May 5, 2009 at 8:49 am
Standard question / answer but have you rebuilt all indexes associated with this procedure? If not then that should be the first step. The index rebuild will take care of your stats as well. Chances are you are getting a bad execution plan at this point and it is eating a significant amount of the memory.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 5, 2009 at 12:51 pm
using
exec sp_configure locks
returns:
name minimum maximum config_value run_value
----- --------- --------- ------------ ----------
locks 5000 21474836470 0
Default setting, I believe.
However, I did find a currency conversion table with no indexes and no PK, so I took care of that, which eliminated a table scan in the execution plan.
Ran the sproc again, got the same error.
So I split the two INSERTs into four chunks, partitioning the data by fiscal year, and finally it's working now.
Thanks to both of you for your suggestions!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply