Feed Detail

davidle asked a question.

May 12, 2021 at 8:52 PM

Failed to execute writer 'WriteFileEvidence - Insert unmatched evidence'

Hello.

We recently discovered that the Reconciliation Jobs has not been able to finish over the last two days.

Within the importer.log file, we have discovered the following error message:

2021-05-12 13:26:52,944 [INFO ] Failed to execute Writer 'WriteFileEvidence - Insert unmatched evidence' from file C:\ProgramData\Flexera Software\Compliance\ImportProcedures\Inventory\Writer\FileEvidence.xml, at step line 108

2021-05-12 13:26:52,960 [ERROR] System.Data.SqlClient.SqlException (0x80131904): Arithmetic overflow error converting IDENTITY to data type int.

Arithmetic overflow occurred.

Is there a way to determine which database table that is causing the Write to Fail on Execution?

FlexNet Manager Suite On-Premises

Top Rated Answers

tkarmiste1
5 years ago
The NewFileEvidence_MT table stores all file evidence which has been added to FNMS which is not part of the Flexera Application Recognition Library. Reseeding this table to use negative values is incorrect as negative values are used for file evidence which is part of the Flexera ARL.
The correct solution is to use the attached script, which reseeds the table, and then deletes and reinserts all of the records so that the numbering is restarted at 1.
It is not normal for an implementation of FNMS to exceed the number of possible values in the NewFileEvidence_MT table. An investigation should be performed to identify where this excessive amount of file evidence is coming from. We have seen before that gathering inventory using the Flexera Agent on Linux machines can result in excessive file evidence. This is especially apparent if the Docker containers, print servers, or email servers are being inventoried as a large quantity of temporary files will be reported by the agent.
Expand Post
- Unknown file type
  NewFileEvidence_MT reseed
  5KB
Selected as Best

All Answers

bheadley (Flexera Software)
5 years ago
Hi @davidle ,
It is likely that the table in question is going to be one of the FileEvidence related tables. I've used the SQL found in this page to figure out which table is causing troubles like the arithmetic overflow you are seeing:
https://www.mssqltips.com/sqlservertip/5554/sql-server-current-identity-value-report-for-all-tables/
This usually means that your database table has run up to the max value of an INT data type for new records (i.e. the max value for an INT data type is 2,147,483,647).
You should probably open a support case as the support team should be able to provide a SQL script to re-seed whichever table is causing you troubles.
HTH,
Bill

Expand Post
- davidle
  5 years ago
  Hi @bheadley ,
  Thanks for providing the suggestion!
  I created the stored procedure based on the link you provided and executed it on the FNMSCompliance DB. Based on the query, I do not see any of the dbo.FileEvidence* table current values that exceed 2,147,483,647.
  Thoughts?
  Expand Post
  Image file
  SS_IdentityColumnReport_FileEvidence
  74KB
  - ChrisG (Flexera Software)
    5 years ago
    The NewFileEvidence_MT table is one that can see its ID values overflow. Check what the query you've executed shows for that table.
    Note that you're looking for ID values that are nearing the limit. It isn't possible to have values that exceed the limit.
    Expand Post
    davidle
    5 years ago
    @ChrisG
    Thank you so much for providing your input on that specific table!
    I see that the current value is 2147483647.
    Should I be able to re-seed that table specifically?
    Thanks!
    Expand Post
    Image file
    SS_IdentityColumnReport_NewFileEvidence
    39KB
    ChrisG (Flexera Software)
    5 years ago
    Update: See @tkarmiste1 's following response which is better.
    A tactic that I have seen used is to reseed the identity column for the affected table to use negative IDs.
    For a table with a 32-bit INT identity column like NewFileEvidence_MT, this could be done by executing a SQL statement like the following on the compliance database:
    
    DBCC CHECKIDENT('dbo.NewFileEvidence_MT', RESEED, -2147483648)
    
    Expand Post
    tkarmiste1
    5 years ago
    The NewFileEvidence_MT table stores all file evidence which has been added to FNMS which is not part of the Flexera Application Recognition Library. Reseeding this table to use negative values is incorrect as negative values are used for file evidence which is part of the Flexera ARL.
    The correct solution is to use the attached script, which reseeds the table, and then deletes and reinserts all of the records so that the numbering is restarted at 1.
    It is not normal for an implementation of FNMS to exceed the number of possible values in the NewFileEvidence_MT table. An investigation should be performed to identify where this excessive amount of file evidence is coming from. We have seen before that gathering inventory using the Flexera Agent on Linux machines can result in excessive file evidence. This is especially apparent if the Docker containers, print servers, or email servers are being inventoried as a large quantity of temporary files will be reported by the agent.
    Expand Post
    Unknown file type
    NewFileEvidence_MT reseed
    5KB
    Selected as Best
    davidle
    5 years ago
    @tkarmiste1
    Thank you for the explanation!
    I am currently executing the attached script on the FNMSCompliance DB and it's been running for more than 10+ minutes.
    Is this expected?
    Thanks!
    Expand Post
    bheadley (Flexera Software)
    5 years ago
    @davidle ,
    Speaking on behalf of @tkarmiste1 , the time for execution you are experiencing is expected.
    This process can take a fair amount of time though without knowing the number of records currently in the NewFileEvidence table we cannot say exactly how long.
    Please be patient and let us know when it has finished.
    Thanks,
    Bill
    Expand Post
    davidle
    5 years ago
    @bheadley @tkarmiste1 @ChrisG
    I wanted to let you all know that the "NewFileEvidence_MT reseed.sql" took two hours to finish executing on the FNMSCompliance DB.
    We then executed a manual reconciliation to verify the results. Based on the importer.log output, the writer "WriteFileEvidence - Insert unmatched evidence" was able to process with no issue successfully!
    2021-05-13 14:44:38,438 [INFO ] WriteFileEvidence - Insert unmatched evidence
    
    2021-05-13 14:48:26,818 [INFO ] Added 332332 File Evidence records from the source database connections.
    
    2021-05-13 14:48:26,818 [INFO ] Successfully processed in 3 minutes, 48 seconds
    I've also attached the output results of the script that was executed on the NewFileEvidence_MT table for reference.
    Thank you to everyone that contributed!
    Expand Post
    bfaller
    5 years ago
    Hello, we had same bug this morning....
    1) don't you think the number should be reset to 1 before the bug occurs ?
    2) what recommandations for file evidence exclusion for that ?
    Regards.
    Expand Post

10 of 12