We are running a Win2003 SP1 Server running SQL 2005 SP2 9.00.3042.00 Enterprise Edition. We have 16 processors and 32GB of memory. We begin to receive messages in the log:
AppDomain 2 (MVXPRD.dbo[runtime].1) is marked for unload due to memory pressure.
And then after, (sometimes hours later, sometimes minutes later) our SQL server stops responding with errors "not enough memory to run the query". All our users get hung up and we go down in flames. We are working with Microsoft on this issue but I thought I would throw it out to this great group of gurus and see if anyone has seen this before and maybe some things that were done to track down the cause. Here is what they are saying:
"Two noticeable errors in SQL Log:
1. Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576
2. Downgrading backup log buffers from 1024K to 64K
To explain the second error, whenever we take log backup using VIRTUAL_DEVICE it tries to allocate memory from SQL MTL(MemToLeave) memory area, which is 384MB, by default. If, while performing the log operation, SQL does not have enough contiguous memory requested by log backup operation, SQL will try to shrink the buffer size to complete the log backup operation. From the “Downgrading backup log buffers from 1024K to 64K” message, it indicates MTL pressure which results either failure of the backup operation or slowness in completing the log operation and “Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE 1048576” error is a definitive indication of MTL pressure.
To provide more MTL area, because of accommodating larger memory request by SQL, you can implement the following action plan that will give you a relief on this memory pressure issue.
1. Add –g512 switch as startup parameter and re-cycle SQL server
2. Monitor the server performance for few days and check if the memory issue re-surfaces
3. If yes, then we may need to figure out the root cause of the issue, as follows:
a. Is there any in-process linked server? If yes, try to throw the linked servers out of process. Note: some linked servers stop working if we throw the associated .dll out-of-process.
b. The VIRTUAL DEVICE backup request memory from MTL and it follows the calculation BLOCKSIZE + BUFFERCOUNT X MAXTRANSFERSIZE. It would be a good test if you can stop these VIRTUAL_DEVICE backup operations and try taking native SQL backups
c. The error log doesn’t say that you’re using any 3rd party XProcs, that again takes memory from MTL
d. You also don’t use sp_xml_preparedocument which again takes memory from MTL, if not using sp_xml_removedocument, will keep reserving the MTL memory not releasing it
e. A large query plan may also occupy MTL area and that happens if either SQL is generating a bad plan or query is written badly. In this situation, you can either tune the memory or update the statistics of the tables on the database in regular interval, say every weekend. To update statistics for all the tables in a database, use the following command
i. Use databasename
ii. Exec sp_msforeachtable ‘update statistics ? with fullscan’"
Then I got another technician telling me that we have too many databases (30) and my maintenance plans need to be reduced. I do a full backup of all databases nightly (1am) and 15 minute transaction log backups of only our production database. On Sunday, I run my maintenance tasks (rebuild indexes, update statistics and check database integrity). Here is what this analyst had to say:
"According one of my previous mail there are +30 databases installed. It’s necessary to reduce the number. The maintenance jobs should be also reduced – there are overlapping jobs and e.g. backup ALL databases job"
Does that sound right???? I have a Diagnostic tool that I run when we get these memory errors but I can't find any one thing that is causing this problem. Any help and/or suggestions from anyone will be greatly appreciated!!!