davidle
Intrepid explorer

Failed to execute writer 'WriteFileEvidence - Insert unmatched evidence'

Jump to solution

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?

0 Kudos
1 Solution

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.

View solution in original post

12 Replies
bheadley
Flexera
Flexera

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

 

 

davidle
Intrepid explorer

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?

0 Kudos
ChrisG
Community Manager Community Manager
Community Manager

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.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
davidle
Intrepid explorer

@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!

0 Kudos
ChrisG
Community Manager Community Manager
Community Manager

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)

 

 

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)
0 Kudos

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.

View solution in original post

@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!

0 Kudos

@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

0 Kudos
davidle
Intrepid explorer

@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! 🙂

bfaller
Active participant

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.

0 Kudos
didiercottereau
Occasional contributor

Hello,

I think it will be the same issue for tables with old history for example for the table SofwareValue_MT with more than 1.4G rows.

Best regards 

Thank you, best regards.
Didier Cottereau
Credit Agricole SA
0 Kudos
didiercottereau
Occasional contributor

Hello 

It took 1h20 to reseed the tables (SoftwareTitleFileEvidence_MT and NewFileEvidence_MT) from 2:43 pm to 4:03 pm

But the next inventory import batch (with import + reconcile) take more than 15 hours

Thank you, best regards.
Didier Cottereau
Credit Agricole SA
0 Kudos