SQL Server 2016 experiences insufficient memory or sessions exceeds the maximum allowed in the server

Question

In SQL Server 2016, we are getting the stack dump for an error

Insufficient memory or the number of configured sessions exceeds the maximum allowed in the server

This is newly upgraded from SQL Server 2014 to SQL Server 2016 (SP2). It's on a VM and has 48 GB of memory. When this error happened the Max memory was set to 24GB and since i say this error i increased the MAX Server memory to 36 GB.

But this is only SSIS db server and that was the reason 24 GB was assigned before and i highly doubt its because of memory pressure

Version:

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64)

Error:

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

IO Completion Listener (0x8c4) Worker 0x0000006B1F680160 appears to be non-yielding on Node 0. Approx CPU Used: kernel 0 ms, user 0 ms, Interval: 15038.

Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task SSB Task due to errors in starting up the task (setup state 3).

Error: 28709, Severity: 16, State: 19.
Dispatcher was unable to create new thread.

Error: 17300, Severity: 16, State: 1.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task XStoreLeaseRenewalTask2 due to errors in starting up the task (setup state 1).

Error: 17803, Severity: 20, State: 13.
There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: ]

Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task XStoreLeaseRenewalTask due to errors in starting up the task (setup state 1).

Error: 9602, Severity: 16, State: 1.
Failed to start a system task with error code 28709, state 19.

Error: 28709, Severity: 16, State: 19.
Dispatcher was unable to create new thread.

Error: 17803, Severity: 20, State: 13.
There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: ]

Error: 17803, Severity: 20, State: 13.
There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed. [CLIENT: ]

Error: 17300, Severity: 16, State: 1.
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.

Error: 28709, Severity: 16, State: 19.
Dispatcher was unable to create new thread.

Error: 17312, Severity: 16, State: 1.
SQL Server is terminating a system or background task SSB Task due to errors in starting up the task (setup state 1).

Error: 28709, Severity: 16, State: 19.
Dispatcher was unable to create new thread.

Error: 9602, Severity: 16, State: 1.
Failed to start a system task with error code 28709, state 19.

And memory counters. I am getting this issue only after upgrading to 2016. Please share your thoughts how i can solve this issue.

EDIT:

Found out issue was happening during the Index optimization. I use Ola script, Source: https://ola.hallengren.com for optimizing the Index. This server has only SSIS DB. Any help is deeply appreciated.

EXEC [Indexoptimize]   
  @Databases = 'ALL_DATABASES',   
  @FragmentationLow = NULL,   
  @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',   
  @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',   
  @FragmentationLevel1 = 5,   
  @FragmentationLevel2 = 30,   
  @PageCountLevel = 1000,   
  @SortInTempdb = 'Y',   
  @FillFactor = NULL,   
  @PadIndex = NULL,   
  @LOBCompaction = 'Y',   
  @UpdateStatistics = 'ALL',   
  @OnlyModifiedStatistics = 'N',   
  @StatisticsSample = 100,   
  @StatisticsResample = 'N',   
  @PartitionLevel = 'N',   
  @MSShippedObjects = 'N',   
  @Indexes = NULL,   
  @TimeLimit = NULL,   
  @Delay = NULL,   
  @LockTimeout = NULL,   
  @LogToTable = 'Y',   
  @Execute = 'Y'   

Answer 1

I recently had the same issue. I checked the number of connections, but there were not very many connections being made to that server. I also looked for memory leaks, but the database server is not connected to any application servers.

Finally, I found the problem was due to low RAM. The same database server is in the migration phase. The old one had 256 GB, but the new one has 96 GB. The issue only starts when I run the DBCCCHECKDB for one of the big databases. Adding more RAM to the database server fixed the problem.