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.

05 December,2017 by Jack Vamvas

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)

 

 

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on 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.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer