Ole Db Provider Problems

I have been pulling data from an Oracle database into my Sql Server on an hourly basis for the last 12 months without any problems. I was then asked to pull data from another oracle server hourly as well.

Everything would work ok at first, for say a couple of hours, but then one of the jobs would start failing and the error message would be that the Ole db Provider had run out of memory. The only way I can get it working again is to start the SQL Server service and then restart it.

Each job takes about 10 minutes to run and they are offest by 30 minutes. When I was just running the one job at hourly intervals there has been no problem. I have tried using both the Microsoft and Oracle providers for Oracle but it makes no difference. Also, it isn't just the Oracle Provider that stops working its all of the ole db providers.

The problem seems to be when the SQL Server reaches is max memory on the server. I have the /3GB switch set on the server so the SQl Svr can use 3Gb of memory, but when it reaches the 3Gb thats when the ole db providers start having problems. The message I recieve is the the old db provider is 'out of memory'.

When I stop the service and restart it again, this obviously resets the memory usage and everything is fine again, at least for a few hours until it reaches 3gb of memory again.

I have tried clearing out the buffers after each job but this seems to make no difference.

Any help would be greatly appreciated.

Thanks,

AlphHave one or both of your jobs stop and restart SQL Server as its last step?

-PatP|||How much free space is there on your hard drive and how much memory do you physically have in the server?|||4Gb Physical memory - Windows 2000 Advanced Server
60Gb Drive of which 35Gb is free

The problem with having one of the jobs stop and restart the service is that this is my only server and it also holds the data for a reporting application. If I have to stop the service the reporting application is unavailable for the time it takes to restart it.

Its as if the ole db providers do not (immediately) release memory when the job has finished. Hence, when the next job starts it requests more memory etc..