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.

Someone sent me a request to diagnose the error message : 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.

A close inspection of the error logs revealed some relate messages. Most importantly

                            There was insufficient memory to run this query

Observations:

Max Memory not set. It was set with no limit . This means it was competing with OS  memory requirements

One of the nice features of SQL Server when there is extreme memory pressure is a good level of detail in the error log. The output of errorlog had dbcc memorystatus dump and what I noticed was At the time of the problem .7 GB was left – very LOW and no memory left in buffer pool

Message

Process/System Counts                        Value

---------------------------------------- ----------

Available Physical Memory                 771760128

Available Virtual Memory                 140699792551936

Available Paging File                   2168729600

Working Set                              10083323904

Percent of Committed Memory in WS               78

Page Faults                             3068606988

System physical memory high                       0

System physical memory low                       0

Process physical memory low                      1

Process virtual memory low                       0

A quick review of the DBCC MEMORYSTATUS revealed the CACHESTORE_SQLCP memory clerk as one of the largest consumers. The OBJECTSTORE_SQLCP is Object Plans include plans for stored procedures, functions, and triggers

CACHESTORE_SQLCP (node 0)                       KB

---------------------------------------- ----------

VM Reserved                                       0

VM Committed                                     0

Locked Pages Allocated                           0

SM Reserved                                       0

SM Committed                                     0

Pages Allocated                           11468784

Advice

- Specify Optimize for AdHoc = true

- Always configure MAX SERVER MEMORY Setting  . Read up on other settings during installation on SQL Server Install Checklist

 - Monitor the size and usage of your plan cache .This is how : SQL Memory usage query and cachestore_sqlcp (SQL Server DBA)