I am working on a vb.net application which stores video files into SQL2000 database. I had a lot of performance related and other issues initially. But with help of many friends at dbforums, I was able to overcome most of them.
The problem still haunting the application is the performance of SQL2000. When I copy a video file to database, the memory usage of server goes up by the size of the file. (I know this because I keep track of memory usage via Task Manager and run the program in debugging mode). The same behavior was observed on the client machine also, but then we managed to improve client machine performance by using more direct approach of appending data in chunks using "UPDATETEXT". So the memory utilization goes up by few MB and comes down when application is closed. But the SQL server memory goes up exactly by the size of file. If I have multiple files to upload, the memory goes up by total size of file and does not come down when applicaiton is closed. Next time when application runs, it keep appending to memory usage till a point when the server crashes because of low virtual memory
I suspect this may be because of a bug in some library somewhere that is not releasing memory. I am not sure, if there in any way I can tune SQL to release the memory.
If anyone else have experienced the same problem, please write me a solution. It would be a great help to me as I have already invested 10 days trying to figure out the problem.