We have a SQL Server 2005 SP3 on Windows Server 2008 x64 that has 14 different database on it. The server has 32GB of memory. One of the databases runs a job that goes for about 24 hours and during that time it does a lot of deleting from tables. This causes physical size on disk to grow as well as the logs. However, our concern is the amount of memory it consumes during this time. Currently, it's consuming 21.4 GB of our 32 GB. I'd like to limit the amount of memory in the buffer pool that it can use down to somewhere in the 8-10GB range. It isn't critical that the job runs quickly, but it is critical that the other databases use more memory and perform faster. When this job is running, everything else slows to a crawl and the other databases are starved for memory.
I've done some searching and haven't found that there is a way to do this. Does anyone have any recommendations?