Dear Friends,

I read lot of article and could not get satisfied answer, what is memory leak is it means when sqlserver memory go beyond the maximum memory set for sqlserver. how we can find memory leak in sqlserver and what are the properties of memory leak in sqlserver.

thank you.

regards,

That is normal and expected behavior and does not indicate a memory leak in SQL Server.  Not all memory allocations are controlled by the "max server memory" setting and you have no control over those allocations.

Please see:

https://support.microsoft.com/en-us/kb/321363?wa=wsignin1.0

what is memory leak is

Generally you won't see a memory leak in SQL Server. There have been a few rare bugs that have cropped up over the years, like the varbinary over linked server leak. If you run CLR, it's possible you may have a leak in the .NET code. It all depends and it's not necessarily ever a leak (unless it's JAVA) :) haha! 

In some leak cases you will see ERROR 703 messages in the sql error log when a process is leaking.

Do you have a specific scenario you're trying to troubleshoot? What version and build are you running? Have you installed the lasted SP + CU?

For processes outside of SQL Server I'd use perfmon and review the PROCESS\PRIVATE BYTES counter to see if it continually grows, which would be an indicator of a leak but may also indicate the process is just being used more.

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

thank you it was usefull, Please  in our case sqlserver 2012 with asp.net, how I can find  .net  CLR leak as well sqlserver using  TSQL.

Dear Friends,

I read lot of article and could not get satisfied answer, what is memory leak is it means when sqlserver memory go beyond the maximum memory set for sqlserver. how we can find memory leak in sqlserver and what are the properties of memory leak in sqlserver.

Memory leak simply means that SQL Server component is not releasing memory when required or when its work is done or Taking memory when not required.

If SQL Server is updated to latest SP and patches there is very very minimum chance that there would be memory leak

From 2012 onwards all memory allocation for SQL Servr is done from max server memory setting value. Only some allocation for third party drivers and providers is done from outside max server memory value. CLR, XML and other stuff take memory from max server memory setting value.

To find memory leak you have DMV

Select * from sys.dm_os_memory_clerks

Select * from sys.dm_os_memory_objects

select * from sys.dm_os_memory_cache_counters

thank you it was usefull, Please  in our case sqlserver 2012 with asp.net, how I can find  .net  CLR leak as well sqlserver using  TSQL.

There is a CU which addressing memory leak apply the same.

https://support.microsoft.com/en-us/kb/2894115