Append to MS SQL Server, System Resource Exceeded

[ottman2]

I'm using MS SQL Server 7.0 and MS Access2000 with NT 4.0 sp6. I have a append query that tries to append 50,000 records from Access to SQL Server. It comes up with the error "cannot undo", which is fine, I tell it to continue, then shortly after, it comes up with "System Resource Exceeded". If I limit the append query to less records, then it works fine. I took out the identity field to avoid any other variables to the problem. I have done this before with other databases with 200,000 records.

 

[nealvenables]

Possibly the SQL server is running out of locks? This doesn't seem likely though, as you normally get an explicit message advising that this has happened.

The other possibility, which I've run into in the past, is that the client PC has simply run out of RAM/pagefile resource.

Food for thought?

 

[ottman2]

I have 394mb RAM with 10gb of free c drive space. My pagefile is 700mb. I also tried it on a XP machine and I got the same error. I tried it on fresh data and it still failed. I've done a compact and repair as well.

How do I determine if the SQL Server is running out of locks? I don't know what locks are.

 

[nealvenables]

Locks are resources that SQL uses to keep track of who is changing what. As these resources have a cost in terms of memory usage, SQL sets an upper limit to how many locks can be used - if you run a long, complex update it is possible for all the locks to be used up.

As you're not already familiar with locks I don't feel comfortable recommending that you start tinkering with what is a fairly complex part of your system configuration.

However, if you want to research this off your own back, the key search words in BOL are "Locks" and "sp_configure".

 

[ottman2]

Its looks like I was able to resolve it. The error was misleading. After I originally imported the data to create the table in SQL Server. I fixed my append problem by changing all fields that were set to 'ntext' to nvarchar. I'm now able to append the records. I appreciate your help anyway.

PS. Your right about the locks, it looks complex.