I am encountering an error on one of our environments where further logins are not allowed anymore. Only way to resolve the problem is to restart the SQLServer Instance.
Different SQL Memory Changes were done on the instance from Enabling/Disabling AWE, changing min and max memory to adding -g to the startup option.
Environment is 32bit, SQL Server 2005 SP3. Memory on the server is 4GB.
After reading several articles, I started monitoring the VAS reservation. I used the query stated on this link http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-server-memtoleave-vas-and-64-bit.aspx
Currently AWE is enabled on the instance with max memory of 2.4GB. The -g512 was added to the startup parameter. However, it only prolonged the error being encountered. Usually, the error occurs(further logins not allowed) when Max Free Size goes down to 2MB.
From what I read, the resources/processes that is using the VAS are the ff:
1) Any OLE/COM components loaded in SQL Server
2) Extended Stored Procedures
3) SQL Mail components
4) Any prepared documents using sp_xml_preparedocument
5) Linked Server Providers
6) Large Plans stored in Procedure Cache
7) Very frequent Backups also may cause MTL area depletion.
8) SQL CLR
Are there any DMVs I could check to pinpoint which of these components is consuming the VAS? Or are there procedures to troubleshoot the problem?
Any suggestions, links and ideas are welcome :-)
Thanks In advance