I'm running a copy of SQL Server 2000 SP3 on Windows NT4 SP6a on a dual 800MHz with 1GB RAM.
I'm running a busy web application which uses ASP to talk to a VB COM object which in turn connects to SQL. It handles relatively large amounts of data and can be quite processor intensive. The main problem I have seems to be due to a memory leak. When the application is running some of its larger methods you can watch the memory go up in the task manager on the server. When the method finshes the memory doesn't seem to be released. This can consume 300 to 400 MB of memory during just one call to the COM object. I've triple checked the ASP and COM object for non-destroyed objects and am 100% sure that everything is being cleaned up correctly.
To test SQL I have written a DTS package in VBScript to transfer data from one database to another on the same server. I have intentionally made the row size quite big to simulate the functionality in the web app. The field datatypes are:
The table has well over 100000 records so I've set the DTS to batch 1000 at a time to test the memory.
The DTS uses an ActiveX Script containing 4 ADO objects (2 connections and 2 recordsets). These are created and opened at the start and closed and destroyed at the end.
Each batch of 1000 records transferred takes about 13MB more memory!? This memory never seems to be released from SQL so it eventually runs out and paging starts.
Does anyone know how I can force this memory to deallocate or what I might be doing wrong.
Looking forward to your replies!