1.

I'm running a time & memory consuming operation on SQL server 2000
(Bulk Copy from a big XML file). The box is Windows 2000 Advanced Server
with 2GB RAM. SQL Server 2000 has SP 4.

I'm getting the following error:-

"Error: 17803, Severity: 20, State: 12

2006-06-02 18:20:30.13 spid53 Insufficient memory available.."

I read several forums and learnt that adding /3GB switch in boot.ini
file will make SQL Server recognize more than 2GB.

My question here is, since the box has only 2GB, does it make sense to
add /3GB switch? Does it help? If not, what else would be the solution?

The worst part is sql server stops executing while some more operations are still pending.

2.

I have another box which has the same settings (infact less RAM, ~1.5
GB RAM). this box performs exactly same operations as the above one, but
finishes gracefully without givig me any error.

Any clue why the above one errors out and not this one?

Appreciate your help on these.

Thanks

Jai

Hi Jaikanth!

There may be a case of Virtual Memory Specification. The virtual memory may be specified less than the required. That is why one of the box is working fine and other unable to even perform.

Microsoft recommends that you should declare Virtual memory 1.5 times of your physical memory. Also, you can make the pagefile.sys static by defining Max and min equal.

I hope you should check it out, how much Virtual memory has been declared. If you are unable to declare it, tell us about that. Reply soon .. whether it works or not.

- Som Dutt

thanks som.
Virtual memory is declared to be 1000 MB (min) & 3600 MB (max) on both boxes.
sql server memory is declared as 4 MB min to 2GB max (= RAM)
Minimun Query memory is 1024 KB

aren't these good numbers? pl let me know.

Thanks
Jai

Check the recovery model for the two boxes. Is there a discrepancy? Full, Simple, Bulk-logged?