The error is printed in terse mode because there was error during formatting

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: Tom Collins (http://www.sqlserver-dba.com)

Share:

The error is printed in terse mode because there was error during formatting

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We are frequently getting the below error in SQL server errorlog.I have checked error log completely and there is no other alerts reported.

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

None of the above link helps me to trace the issue. Please suggests to prevent this error..

Answers

You may need to run a SQL Trace to find out wha tis happeneing at the same time as the error (or just before).

The TDS is an applciation layer protocol (http://en.wikipedia.org/wiki/Tabular_Data_Stream) so I guess you will get the error when an app connects to the server, so you’ll need to profile under normal usage.

There is an MS connect issue for this:

Part of the MS reply says:

The Error code 7884 implies a TDS protocol violation which most often is caused by the previous exception. you can find that by looking through your error log and finding out what the previous error is and rootcausing that. If you can send us the error log, we can also help you in doing that.

So you need to take a look through the error log to see what the previous entries say.

Post them on here if you find anything.

There is no other errors reported in SQL errorlog and eventviewer as well.

Источник

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 Tom Collins

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

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

Locked Pages Allocated 0

Pages Allocated 11468784

— Specify Optimize for AdHoc = true

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

Источник

KB2769594 — SQL Server 2012 experiences out-of-memory errors

Symptoms

Microsoft SQL Server 2012 may experience some out-of-memory errors because of an internal memory leak. In this situation, the leaked memory is consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk that is used internally for transaction log activities. For example, the MEMORYCLERK_SQLLOGPOOL memory clerk is used internally for the following transaction log activities:

AlwaysOn change-capturing activities on the primary replicas

AlwaysOn redo manager activities on the secondary availability replicas

This issue may be aggravated by a high volume of transactions that are being synchronized on an instance of SQL Server that hosts many availability databases. When this issue occurs, an error message that resembles the following is logged in the SQL error log:

When the instance of SQL Server hosts a database that is involved in a SQL Server AlwaysOn availability group, the data synchronization between replicas may fail. To work around the problem, you can stop and then restart SQL Server.

When the out-of-memory conditions occurs, the availability group may fail over to the secondary availability replica, and the former primary replica may continue achieving errors until the service is stopped and restarted. The availability group may go into the Resolving or Unknown state, and all the databases in that availability group may be inaccessible, until the SQL service is restarted to free the log pool memory.

You can run the DBCC MEMORYSTATUS statement when the failure begins to notice in the output that many gigabytes of memory are consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk. The following is an example output:

MEMORYCLERK_SQLLOGPOOL (node 0) KB
—————————————- ————
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 823544You can also see the number of memory pages of the problem by running the following DMV queries:

Another possible issue is that SQL Server queries could wait for RESOURCE_SEMAPHORE waittype when the buffer pool memory cannot be immediately obtained because of the high number of Log Pool consumers.

This hotfix resolves the following issues:

A memory leak is corrected in the internal read-ahead mechanism that is used to locate the transaction log pools that are used by AlwaysOn and other transaction log intensive activities in SQL Server 2012.

When there is low memory available, SQL Server releases the internal log pool memory aggressively to recover from out-of-memory failures more gracefully.

Resolution

Cumulative update information

Cumulative Update 2 for SQL Server 2012 SP1

The fix for this issue was first released in Cumulative Update 2. For more information about how to obtain this cumulative update package for SQL Server 2012 SP1, click the following article number to view the article in the Microsoft Knowledge Base:

2790947 Cumulative update package 2 for SQL Server 2012 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Cumulative Update 5 for SQL Server 2012

The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:

2777772 Cumulative update package 5 for SQL Server 2012Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a «Hotfix download available» section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, go to the following Microsoft website:

http://support.microsoft.com/contactus/?ws=supportNote The «Hotfix download available» form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Prerequisites

To apply this hotfix, you must have SQL Server 2012 installed.

File information

The global version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Источник

KB2769594 — SQL Server 2012 experiences out-of-memory errors

Symptoms

Microsoft SQL Server 2012 may experience some out-of-memory errors because of an internal memory leak. In this situation, the leaked memory is consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk that is used internally for transaction log activities. For example, the MEMORYCLERK_SQLLOGPOOL memory clerk is used internally for the following transaction log activities:

AlwaysOn change-capturing activities on the primary replicas

AlwaysOn redo manager activities on the secondary availability replicas

This issue may be aggravated by a high volume of transactions that are being synchronized on an instance of SQL Server that hosts many availability databases. When this issue occurs, an error message that resembles the following is logged in the SQL error log:

When the instance of SQL Server hosts a database that is involved in a SQL Server AlwaysOn availability group, the data synchronization between replicas may fail. To work around the problem, you can stop and then restart SQL Server.

When the out-of-memory conditions occurs, the availability group may fail over to the secondary availability replica, and the former primary replica may continue achieving errors until the service is stopped and restarted. The availability group may go into the Resolving or Unknown state, and all the databases in that availability group may be inaccessible, until the SQL service is restarted to free the log pool memory.

You can run the DBCC MEMORYSTATUS statement when the failure begins to notice in the output that many gigabytes of memory are consumed by the MEMORYCLERK_SQLLOGPOOL memory clerk. The following is an example output:

MEMORYCLERK_SQLLOGPOOL (node 0) KB
—————————————- ————
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 823544You can also see the number of memory pages of the problem by running the following DMV queries:

Another possible issue is that SQL Server queries could wait for RESOURCE_SEMAPHORE waittype when the buffer pool memory cannot be immediately obtained because of the high number of Log Pool consumers.

This hotfix resolves the following issues:

A memory leak is corrected in the internal read-ahead mechanism that is used to locate the transaction log pools that are used by AlwaysOn and other transaction log intensive activities in SQL Server 2012.

When there is low memory available, SQL Server releases the internal log pool memory aggressively to recover from out-of-memory failures more gracefully.

Resolution

Cumulative update information

Cumulative Update 2 for SQL Server 2012 SP1

The fix for this issue was first released in Cumulative Update 2. For more information about how to obtain this cumulative update package for SQL Server 2012 SP1, click the following article number to view the article in the Microsoft Knowledge Base:

2790947 Cumulative update package 2 for SQL Server 2012 Service Pack 1Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 SP1 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2772858 The SQL Server 2012 builds that were released after SQL Server 2012 Service Pack 1 was released

Cumulative Update 5 for SQL Server 2012

The fix for this issue was first released in Cumulative Update 5. For more information about how to obtain this cumulative update package for SQL Server 2012, click the following article number to view the article in the Microsoft Knowledge Base:

2777772 Cumulative update package 5 for SQL Server 2012Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2012 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

2692828 The SQL Server 2012 builds that were released after SQL Server 2012 was released

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.

If the hotfix is available for download, there is a «Hotfix download available» section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, go to the following Microsoft website:

http://support.microsoft.com/contactus/?ws=supportNote The «Hotfix download available» form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Restart requirement

You do not have to restart the computer after you apply this hotfix.

Prerequisites

To apply this hotfix, you must have SQL Server 2012 installed.

File information

The global version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.

Источник

Hi Guys
Microsoft SQL 2005
VB.Net 2003
I have written a vb.net project which runs every 2 minutes as a scheduled task.

After a few days I receive this 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.

No one can access anything on SQL 2005. I have to restart the SQL Service.

I think it might be the code not closing database connections correctly.

This only occured since I inserted error trap code.

The code is below

I think this code is causing the stress and no where else.
That’s because the code   sql = «Select * from Activation where Activated is null»
            OBJSQLdbcommand = New OleDb.OleDbCommand(sql, objConn)
            objdatareader = OBJSQLdbcommand.ExecuteReader
will not be null as there is nothing to activate yet.

Private Sub CheckLicenseKeyValidation()

        Dim strConnectionString As String = «Provider=sqloledb;Data Source=DSVR003226;Initial Catalog=Test;User Id=MASTER;Password=test;»
        Dim objConn As New System.Data.OleDb.OleDbConnection(strConnectionString)
        Dim objConn1 As New System.Data.OleDb.OleDbConnection(strConnectionString)
        Dim objConn2 As New System.Data.OleDb.OleDbConnection(strConnectionString)

        Dim objdatareader As OleDb.OleDbDataReader
        Dim objdatareader1 As OleDb.OleDbDataReader
        Dim objdatareader2 As OleDb.OleDbDataReader
        Dim objdatareader3 As OleDb.OleDbDataReader
        Dim objdatareader4 As OleDb.OleDbDataReader

        Dim OBJSQLdbcommand As New System.Data.OleDb.OleDbCommand
        Dim OBJSQLdbcommand1 As New System.Data.OleDb.OleDbCommand
        Dim OBJSQLdbcommand2 As New System.Data.OleDb.OleDbCommand
        Dim OBJSQLdbcommand3 As New System.Data.OleDb.OleDbCommand
        Dim OBJSQLdbcommand4 As New System.Data.OleDb.OleDbCommand

        Try

            objConn.Open()
            objConn1.Open()
            objConn2.Open()

            Dim sql As String
            Dim LicenceKey As String
            Dim Company As String
            Dim UserName As String
            Dim Email As String
            Dim Inform As String
            Dim Telephone As String
            Dim Mobile As String
            Dim Source As String
            Dim ProductCode As String
            Dim FirstName As String
            Dim LastName As String
            Dim Note As String

            Dim regSQL As String
            Dim regLicenceKey As String
            Dim regCompany As String
            Dim regUserName As String
            Dim regEmail As String
            Dim regInform As String
            Dim regTelephone As String
            Dim regMobile As String
            Dim regSource As String
            Dim regProductCode As String
            Dim regFirstName As String
            Dim regLastName As String
            Dim regNote As String
            Dim sqlProcess As String

            sql = «Select * from Activation where Activated is null»
            OBJSQLdbcommand = New OleDb.OleDbCommand(sql, objConn)
            objdatareader = OBJSQLdbcommand.ExecuteReader

            Do While objdatareader.Read()
                If objdatareader.IsDBNull(0) Then
                    LicenceKey = «»

                Else
                    LicenceKey = objdatareader.GetValue(0)
                End If

                If objdatareader.IsDBNull(1) Then
                    Company = «»

                Else
                    Company = objdatareader.GetValue(1)
                End If

                If objdatareader.IsDBNull(2) Then
                    FirstName = «»

                Else
                    FirstName = objdatareader.GetValue(2)
                End If

                If objdatareader.IsDBNull(3) Then
                    LastName = «»

                Else
                    LastName = objdatareader.GetValue(3)
                End If

                If objdatareader.IsDBNull(4) Then
                    Email = «»

                Else
                    Email = objdatareader.GetValue(4)
                End If

                If objdatareader.IsDBNull(5) Then
                    Inform = «»

                Else
                    Inform = objdatareader.GetValue(5)
                End If

                If objdatareader.IsDBNull(6) Then
                    Telephone = «»

                Else
                    Telephone = objdatareader.GetValue(6)
                End If

                If objdatareader.IsDBNull(7) Then
                    Mobile = «»

                Else
                    Mobile = objdatareader.GetValue(7)
                End If

                If objdatareader.IsDBNull(8) Then
                    Source = «»

                Else
                    Source = objdatareader.GetValue(8)
                End If

                If objdatareader.IsDBNull(9) Then
                    ProductCode = «»

                Else
                    ProductCode = objdatareader.GetValue(9)
                End If

                If objdatareader.IsDBNull(11) Then
                    Note = «»

                Else
                    Note = objdatareader.GetValue(11)
                End If

                UserName = FirstName & » » & LastName

                ‘Check if License key matches in EIB_Table1_Customer
                Dim sql2 As String
                Dim ActivationLicense As String
                Dim ActivationCompany As String

                sql2 = «Select Distinct License_Key,Company_Name from EIB_Table1_Customer where License_Key = ‘» & LicenceKey & «‘»
                OBJSQLdbcommand1 = New OleDb.OleDbCommand(sql2, objConn1)
                objdatareader1 = OBJSQLdbcommand1.ExecuteReader
                Do While objdatareader1.Read()
                    If objdatareader1.IsDBNull(0) Then
                        ActivationLicense = «»
                    Else

                        ActivationLicense = objdatareader1.GetValue(0)
                    End If

                    If objdatareader1.IsDBNull(1) Then
                        ActivationCompany = «»
                    Else

                        ActivationCompany = objdatareader1.GetValue(1)
                    End If

                    SetActivation(LicenceKey)
                    InsertActivationFields(ActivationLicense, ActivationCompany, UserName, Email, Inform, Telephone, Mobile, Source, ProductCode, «Yes», Note)
                    GenerateLicenceKey(ProductCode, Source, ActivationCompany, FirstName, LastName, Email, ActivationLicense)

                Loop
                ‘If objdatareader1.RecordsAffected = 0 Then

                sqlProcess = «Select Top 1 License_Key from EIB_Table2_Registration where License_Key = ‘» & LicenceKey & «‘ order by Date_Registered desc»
                OBJSQLdbcommand4 = New OleDb.OleDbCommand(sqlProcess, objConn2)
                objdatareader4 = OBJSQLdbcommand4.ExecuteReader
                Do While objdatareader4.Read()

                    If objdatareader4.IsDBNull(0) Then
                        ActivationLicense = «»
                    Else

                        ActivationLicense = objdatareader4.GetValue(0)
                    End If

                    SetActivation(LicenceKey)
                    InsertRegFields(ActivationLicense, Company, UserName, Email, Inform, Telephone, Mobile, Source, ProductCode, «Yes», Note)
                    RegistrationDetails(LicenceKey, Company)
                    GenerateLicenceKey(ProductCode, Source, Company, FirstName, LastName, Email, ActivationLicense)
                Loop
                ‘End If

                If objdatareader4.RecordsAffected = 0 And objdatareader1.RecordsAffected = 0 Then
                    SetActivation(LicenceKey)
                    AddInvalidLicenceCustomer(LicenceKey, Company, UserName, Email, Inform, Telephone, Mobile, Source, ProductCode, «Yes», Note)
                    SendInvalidLicencemail(Company, UserName, LicenceKey, Email)
                End If
                objdatareader1.Close()
                objdatareader4.Close()

                ‘Close Form if nothing to Activate

                If LicenceKey = «» Then
                    ‘MsgBox(«help»)
                    Me.Close()
                Else

                End If

            Loop
            Me.Close()

        Catch exc As Exception
            ‘ MsgBox(«Error: » & exc.Message)
            Send_Genlicense_System_email(«Error: » & exc.Message)

        Finally

            ‘If Not dr Is Nothing Then dr.Close()
            If Not objdatareader Is Nothing Then

                objdatareader.Close()

            End If

            If Not objdatareader1 Is Nothing Then

                objdatareader1.Close()
            End If

            If Not objdatareader2 Is Nothing Then

                objdatareader2.Close()

            End If

            If Not objdatareader3 Is Nothing Then

                objdatareader3.Close()
            End If

            If Not objdatareader4 Is Nothing Then

                objdatareader4.Close()

            End If

            If Not objConn Is Nothing Then
                objConn.Close()
            End If

            If Not objConn1 Is Nothing Then
                objConn1.Close()
            End If

            If Not objConn2 Is Nothing Then
                objConn2.Close()
            End If

            Me.Close()

        End Try

    End Sub