but you could set up a job to poll this memory counter say every 1 minute and put it into a place holder table with a timestamp. then you could check last 2 values to see if it exceeded your threshold ... if it did send an email - database mail ... telling you that threshold exceede in the last 1 minute.

last step of this job could be to delete data older than a day or so from your place holder table to keep it under control.

--------------------------------------------------AmitGive a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! - desparately trying to fish

Not sure why you would be worried about that - since you have control over the max memory setting for SQL Server. If you set the max memory - you wouldn't need to worry about SQL Server taking up too much memory on the server.

This is a 32-bit Windows 2003 Enterprise server having 4GB RAM.Earlier we had enabled the /3GB switch on it to allow user processes including SQL to consume upto 3GB memory.But then we ran into memory leak issues arising out of older bios drivers and other stuff.Microsoft gave us some action items among which one was to remove this /3GB switch.So now we are back to a 2GB user memory and I have to monitor the SQL memory consumption over the next few weeks to see if it is running fine under this limit or if it needs more memory.This is why I need to automate this memory monitoring process.Now can you suggest something more substantial than a max memory setting?

Nope - can't suggest anything more than setting the max memory. SQL Server will take the max memory you assign it - and shouldn't take any more than that.

What you are really needing to look at is memory pressure issues? Again, SQL Server is not going to take more memory than is allocated - and in your configuration won't be able to use more than 2GB anyways.

So, you need to be monitoring for memory pressure - and not target memory/total memory on the server. These counters will just tell you if you have another memory leak issue - and not whether or not SQL Server is performing well with 2GB.

What are the appropraite counters which can be used from inside SQL Server for monitoring memory usage withing 2GB,which can tell me whether the allocated 2 GB max memory is fine or if I need to configue more memory?

Currently i have configured: Buffer Cache Hit ratio,Connection memory,Cache Hit ratio and Lazy writes/sec. Any thoughts?

As this discussion open for the discuss about how we can actively monitor SQL Server memory utilization and you want to know by email.

Before we jump in to the any memory we have to first know how SQL will grab memory in to Server memory since we are talking about the SQL Server 32-bit edition on Windows Server 2k3 32-bit on without /3GB or USRENVA environment.

As we all know SQL or any application will get the 2GB of virtual Address now internally SQL Server allocated memory in two mail part. which is known as

1. Buffer pool (used for SQL Optimizer,Plan Generation Etc..2. Non Buffer Pool (Which is AKA "Mem to leave Area")(Used for the 3rd party DLL COM component for extended stored proc and CLR related operation and it will be 256 MB default and

Now these two sections has their own separate function in the SQL Server. so if we are talking to the which region is consuming more memory is decided by the queries to SQL Sever DMV which is very use full.

-- I will give you some list good DMVs in SQL Server 2005 which will describe how you will find memory consumption by querying them

-- Now you can check the performance Counter with the SQL Server-- SQL Server memory related DMV-- DBCC MEMORYSTATUS output

-- In the SQL Server 2008 there are new DMV added for the dwell in to the problem of the SQL Server sys.dm_os_memory_brokerssys.dm_os_memory_nodessys.dm_exec_procedure_stats

-- Like we have there existing DMVs in the SQL Server 2005sys.dm_os_sys_infosys.dm_exec_requestssys.dm_exec_requests

Please refer to the BOOKS Online for further description of the these DMV's these all DMVs will talk about the buffer pool

-- Now let's talk about how will you check for Non Buffer pool memory (Mem to leave) we can use the following DMV sys.dm_os_virtual_address_dump