web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Error in Store Database,
Microsoft Dynamics AX (Archived)

Error in Store Database,

(0) ShareShare
ReportReport
Posted on by w.sallam 2,259

Hi All, we have an issue when opening POS in one store,

the error in event viewer,

LSRetailPosis.POS.Program: System.Data.SqlClient.SqlException (0x80131904): Possible schema corruption. Run DBCC CHECKCATALOG.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

....

and when we apply DBCC CHECKCATALOG in SQL Server, this following error shows,  (this is part of the error as it is really LONG)

Msg 3857, Level 16, State 1, Line 1
The attribute (internal_type=NULL,parent_id=NULL,parent_minor_id=NULL) is required but is missing for row (object_id=278460962) in sys.internal_tables.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=358461247) of row (object_id=358461247,column_id=1) in sys.columns does not have a matching row (object_id=358461247) in sys.objects.
Msg 3853, Level 16, State 1, Line 1


as in AX the Download jobs are working except Jobs 1070&1060

anydesk00002.png

 this is the message error in download sessions for Jobs 1070&1060

Microsoft.Dynamics.Retail.SynchClient.Core.GetColumnsForTableInDatabaseException: Error occurred when trying to find column names for table [AX].[DIRPARTYTABLE] in database FD4-AXOnestopDB on SQL server instance C6833\SQLEXPRESS. Query: SELECT COLUMN_NAME AS COLUMN_NAME
                  FROM INFORMATION_SCHEMA.COLUMNS
                  WHERE TABLE_SCHEMA = @schemaName
                  AND TABLE_NAME = @tableName
                  ORDER BY ORDINAL_POSITION; ---> System.Data.SqlClient.SqlException: Possible schema corruption. Run DBCC CHECKCATALOG.
Cannot continue the execution because the session is in the kill state.
A severe error occurred on the current command.  The results, if any, should be discarded.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.Dynamics.Retail.SynchClient.Core.Utility.GetColumnsForTableInDatabase(SqlConnection connection, String tableNameWithSchema)
   --- End of inner exception stack trace ---
   at Microsoft.Dynamics.Retail.SynchClient.Core.Utility.GetColumnsForTableInDatabase(SqlConnection connection, String tableNameWithSchema)
   at Microsoft.Dynamics.Retail.SynchClient.Core.DownloadAgent.ApplySessionFileToClientDatabase(SessionManager sessionMgr, String fileName)

thanks in advanced,

 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Girish V Profile Picture
    Girish V on at

    Hi Sallam,

    Looks like your schema of channel database got corrupted, Please run dbcc command to check which table has issue.

    Regards,

    Girish

  • w.sallam Profile Picture
    w.sallam 2,259 on at

    Hi Girish ,

    thanks for reply,

    can you tell me how to fix the corrupted tables.?

    already run dbcc

  • Verified answer
    Rodolfo Recalde Profile Picture
    Rodolfo Recalde on at

    Hi Wasim,

    When the store server is in a SQL Server Express on a machine without no-break, this type of database corruption can become frequent. Attention in this observation of infrastructure for the future.

    I'll try to target some alternatives, however, if all transactions are "uploaded" to AX (P-0001), perhaps the quickest alternative to restore store availability, be to create a new channelDB and synchronize full.

    let's go....

    • Get the database files corrupted and attach in another instance, to test this operation.

    CREATE DATABASE [CorruptDB] ON
( FILENAME = N'C:\temp\CorruptDBs\CorruptDB1\DBFiles\CorruptDB1.mdf' ),
( FILENAME = N'C:\temp\CorruptDBs\CorruptDB1\DBFiles\CorruptDB1_log.ldf' )
FOR ATTACH;
GO
    • Run CHECKDB command to verify the status of the database.
    DBCC CHECKDB(CorruptDB1)
WITH ALL_ERRORMSGS, NO_INFOMSGS

    • Use the REPAIR_REBUILD command to attempt to repair the database without loss of data.
    ALTER DATABASE CorruptDB1 SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

DBCC CHECKDB(CorruptDB1, REPAIR_REBUILD)
WITH ALL_ERRORMSGS, NO_INFOMSGS
     
    • If the above command does not work, use the REPAIR_ALLOW_DATA_LOSS command, however you may lose data.
    DBCC CHECKDB(CorruptDB1, REPAIR_ALLOW_DATA_LOSS)
WITH ALL_ERRORMSGS, NO_INFOMSGS



                                     ***

    other possible approaches...

    Apparently one of the tables corrupt is the ax.DIRPARTYTABLE table

    [....[AX].[DIRPARTYTABLE] in database FD4-AXOnestopDB...] (try to make a simple select * from ax.DirPartyTable to verify if it is accessible [probably not])

    A possible alternative is to delete this table and create it again. You can synchronize again ax.DirPartyTable to the store db, so no data loss occurs.




    I Hope this Helps!

    Regards,

    Rodolfo Recalde

  • w.sallam Profile Picture
    w.sallam 2,259 on at

    Thanks Rodolfo for your answer,

    I will try the solution you gave and see if it will solve this.

    our client is have more than 10 stores all with SQL Server Express,

    can you please tell me how to make sure this error won't happen in other store databases.?

  • Rodolfo Recalde Profile Picture
    Rodolfo Recalde on at

    Data corruption is random and can occur with anyone in the most diverse scenarios.


    I will try to pass some possible actions that can be evaluated.


    1. Use an instance of SQL SERVER Standard / Enterprise on a server within your company headquarters data center, or on a Virtual Machine on the cloud. This way you can mitigate problems and plan to re-establish the operation of the store more quickly, as more resources are available. However, this type of solution is only valid if the store does not operate offline and also if the latency of the internet is not a problem where the stores (POS) are located.

    As this solution does not apply in most scenarios, either because of the technical unfeasibility or in relation to the costs involved, also evaluate these other indications:

    2. Use on all store servers, "no-Break"  equipment and other power grid filters that can control the quality of electrical power that reaches the store server. Unexpected power outages can cause corruption in the database.

    3. Organize the synchronization processes, so that there is minimal competition between them, especially among the most "heavy" jobs, such as 1010, 1070, 1060. 1040. Preferably, perform this operation when the store is closed.

    4. Do the indexing maintenance frequently. SQL Server Express does not have the possibility of a job, but you can run an index maintenance script through a windows task.

    5. Monitor your server's memory,  I / O and HD capacity. Make sure that some process no generates some "peak", locks, long queries, or "high anomaly".

    6. Use SSD disks for the SQL Server files.

    7. Disable Antivirus from SQL Server files and .exe. See this link for more details.

    I Hope this Helps!

    Regards,

    Rodolfo Recalde.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans