SQL Server Performance Forum – Threads Archive

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 told that the data needed refreshing every 30 minutes. As soon as I set my job to run every 30 minutes I started to get problems. Everything would work ok at first, for say a couple of hours, but then the job 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. The job takes about 5 minutes to run and like I say with 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 them. It seems like some form of memory allocation problem but I have no idea as to how to fix it. Any help would be greatly appreciated. Thanks,
Dom

Have you tried upgrading to the laetst MDAC on all involved servers?

I have MDAC 2.8 on the sql server.

Do a search on googlecom and groups.google.com with your exact error message and see if anyone has had the same problem.

We have had similar problem with one of the DTS job that is associated from SQL server to port data to Oracle database. Both SQL & ORacle DBAs have had enough fight and one day it found to be the ODBC drivers on SQL Server and outdated drivers on Oracle server were the cluprits. IF you get any sort of warning or error message post them here for more information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

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 tries clearing out the buffers etc.. but no joy.

What is the memory setting on SQL Server?
Ensure no other application is maxing out the resources, collect PERFMON counters for CPU, PHysical disk, memory and other SQL related counters for further assessment. Checkout KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;268520 for further clarification. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>