6

Every few days my SQL Server Instance terminates unexpectedly and restarts. In the logs, see:

A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. (Event ID 6536).

Prior to the termination I see several messages saying:

AppDomain XXX is marked for unload due to memory pressure.

I have 60 CLR Stored Procedures, which I've inherited from my predecessor. All are supposed to be short stored procedures using a small amount of data. How do I find which CLR Stored Procedure is causing the memory pressure?

I'm running:

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64)   Jul 16 2017 18:08:49   
Standard Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: ) (Hypervisor) 
  • are minidumps being generated? – StrayCatDBA Apr 18 '18 at 16:30
  • 3
    Do you have max server memory and min server memory configured appropriately? – Max Vernon Apr 18 '18 at 16:32
  • You can monitor DMVs like sys.dm_clr_appdomains and for more detail take frequent snapshots of sys.dm_clr_tasks and tie that to sys.dm_os_workers on task_address (before the crash and while all appdomains/assemblies are loaded, obviously). – Aaron Bertrand Apr 18 '18 at 16:45
  • It seems like some unsafe assemblies are getting loaded into SQL Server space they must be dumped in errorlog.Something like spid51 Unsafe assembly '<assembly name>, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil' loaded into appdomain 1 (<appdomain name>) can you confirm – Shanky Apr 18 '18 at 16:54
4

Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

SAFE CLR procedures do not crash SQL Server, so looking at the managed memory usage of CLR is unlikely to lead you to the root cause.

It's more likely that you have unsafe CLR code interacting with native code that has a memory leak. So look at any the CLR projects for any use of 3rd party native components (like OleDB or ODBC drivers), or any use of P/Invoke or COM Interop.

2

Just to point out a couple of things before heading out for a wild goose chase:

  1. .NET / CLR is used by SQL Server for a variety of things, even if you never added any custom SQLCLR code. There are built-in functions such as FORMAT(), there are features such as the CLR datatypes (Geography, Geometry, and HierarchyID), Change Tracking, etc. Even if you have disabled the server configuration option of "CLR enabled", you could very well still be using CLR in one or more areas. Meaning, this is not guaranteed to be coming from your code (even if custom code is the best first place to check).

  2. Memory pressure is a function of the system running out of physical memory, which may or may not be related to actual usage of custom CLR Assemblies. Getting that message in the log file about an App Domain being marked for unload due to memory pressure does not imply anything about what was being done in that App Domain outside of merely existing and hence being a candidate for unloading.

You can try paying attention to the survived_memory_kb field from the following DMV:

SELECT * FROM sys.dm_clr_appdomains;

as that should be the amount of memory that is not being released. Of course, if all 60 SQLCLR stored procedures are in the same App Domain, then that might not help, except to possible prove / disprove the idea that a large amount of memory is being held by the App Domain.

Also, how busy is your system? Sometimes garbage collection doesn't run if the system is busy. In which case you can force it by creating a simple CLR function that does garbage collection (it's a single command) which doesn't get rid of everything, and shouldn't be called often, but it definitely clears out some stuff.

Finally, it need not be UNSAFE code locking up resources. If you have EXTERNAL_ACCESS code that interacts with the network or file system, etc, then you need to make sure that those resources are being properly disposed of, either explicitly via a call to .Dispose() or by the instantiation of the object being wrapped in a using() construct. If not properly disposing external resources, it is theoretically possible to use up all available handles, especially if the code is called at high volumes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.