hi team. yesterday i got a call from developer informing the error below There is insufficient system memory in resource pool 'internal' to run this query. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: There is insufficient system memory in resource pool 'internal' to run this query. Source Error: after some time server application started working there after in the mid night SQL Server, got restarted and now it is fine what might be the problem ,how can this be resolved
A quick google of this error seems to bring up lots of various causes and resolutions, the common theme being memory pressure outside of the buffer pool caused by third party software or even extended features of SQL (linked servers, XML processing, etc, etc.) If this is a recurring problem, I would be tempted to open a case with Microsoft Support, as they are best place to diagnose what exactly is eating all the memory. You can find lots of 'solutions' on the web including trace flags, memory config, startup params, but unless you really know what you are doing, you could be harming the server more!
64 bit or not, how much memory is on the machine? Do you have other software running on the same machine? SQL Server is very much a memory pig. I would strongly recommend monitoring the system, put some diagnostics in place to see what is using memory and when. Simply relying on the error messages just isn't enough to be able to suggest what's happening on the system. I would also suggest you take a look at wait states and queues to see what things are slowing stuff down. After that, I agree with @Kev. If you're hitting a recurring issue, it's either a configuration problem or possibly an internal error & you need to get MS involved.
To get SQL Server memory usage try querying `sys.dm_os_sys_memory` with something like SELECT total_physical_memory_kb, available_physical_memory_kb , total_page_file_kb , available_page_file_kb, system_memory_state_desc FROM sys.dm_os_sys_memory This will give you an idea of the memory used by the SQL instance and what is left. there are tools that can show the memory usage built in with the OS - others have mentioned perfmon, task manager etc. You can also get 3rd party tools that show the usage too, RedGate do one called SQLMonitor that lists all processes and their memory usage in real time. I believe Idera's product that does the same is called SQL Doctor.