Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    6

    Unanswered: 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,

    Alph

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Have one or both of your jobs stop and restart SQL Server as its last step?

    -PatP

  3. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    How much free space is there on your hard drive and how much memory do you physically have in the server?

  4. #4
    Join Date
    Nov 2004
    Posts
    6
    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..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •