Failed imports / increased database usage with BFI 10.0.2

[AlexaVonTess]

Since upgrading to this version from 10.0.1.0, my daily imports are failing after about two hours (they normally go about 4). I’ve tried three times. Anyone else reporting this?

[JasonWalker]

I think we’d need to see the lines that start the error message…should be shortly before these.

[JasonWalker]

2020-10-28 15:05:26 (+0:00:00.015) ERROR: Sequel::DatabaseError: DBNAME: BFXInventory - NativeException: com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database ‘BFXInventory’ is full due to ‘ACTIVE_TRANSACTION’.

There’s the key, it got an error from SQL Server that the transaction log is full.
I don’t know for certain, but I have reason to think the first import after upgrading would be a larger-than-usual transaction. I believe you’d need to check for actual free space on the volume hosting the transaction log on your server; increase the maximum size of your transaction log file; or configuration your transaction log to auto-grow.

In SQL Server Management Studio, you can do that by right-clicking on the database, selecting Properties, and checking the Files entry. Click the three dots beside the “Autogrowth / Maximum” colum in the LOG row and see how they are set.

[AlexaVonTess]

Thank you for that response. I’ll sent this to our SQL Team and see what how they respond. I’ll update regardless.

[AlexaVonTess]

Looks like those were full and were already cleaned this morning (PST). Now there is 35GB free and I’m doing a manual import. We’ll see what happens.

[ssakunala]

One of the enhancements with 10.0.2 is:
Installation path of all discovered software
Inventory reports now show the installation path of all discovered software (IBM and non-IBM). If a product is installed in multiple paths, all paths are reported as separate instances.

Depending your BFI size and # of non-IBM instances, this task can trigger additional storage requirement

[AlexaVonTess]

This seems to be the case, because I never had such a jump in data (I’ve upgraded this database many times over the last four years). We increased the Transaction Log LUN to 100GB, but now it looks like the Backup Log is running out of space. I’ll have the SQL team look into that and try again.

2020-10-29 08:36:26 (+0:00:00.000) ERROR: Sequel::DatabaseError: DBNAME: BFXInventory - NativeException: com.microsoft.sqlserver.jdbc.SQLServerException: The transaction log for database ‘BFXInventory’ is full due to ‘LOG_BACKUP’.
com/microsoft/sqlserver/jdbc/SQLServerException.java:258:in makeFromDatabaseError' com/microsoft/sqlserver/jdbc/SQLServerStatement.java:1535:ingetNextResult’

[JasonWalker]

Disclaimer - I’m no DBA, so take this with a grain of salt…

That’s a slightly different error, still related to the transaction log… I did find at least one related SQL bug, you might have your DBA check whether you are impacted by https://support.microsoft.com/en-us/help/4132896/kb4132896-transaction-log-full-when-space-available-autogrowth-enabled

If you aren’t affected by this issue, the general resolution steps are at https://docs.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002 but your DBA probably has these already.

If the database recovery model is set to “full” rather than “simple”, then space in the transaction log cannot be reused until a database log backup runs. In “full” recovery mode, after restoring a full or differential database backup, you could replay the transaction logs to recover the database all the way up to the point-in-time of a failure; where I’ve used Full Recovery mode, we would take Transaction Log backups frequently (like every 15-30 minutes).

Usually, Full Recovery mode is overkill for BFI. We aren’t running credit card/banking transactions, if the server fails we’d just restore the previous day’s backup and run a new Import. If you have the DBA change the database’s recovery model to Simple, the Transaction Log space can overwrite itself as needed for any new transaction, it doesn’t require log backups before reclaiming the space.

I don’t know your specific needs, but I’d usually just set the BFI database for Simple Recovery and let it overwrite the transaction log as needed, take daily full backups, and not have to deal with transaction log backups.

[AlexaVonTess]

I’ll ask them… And I agree with it being set to Simple; as I do for the BES database itself (which I manage).

[AlexaVonTess]

We’ve set the DB into Simple mode, but now the Transaction Log being full has returned. The database is only 165GB and the transaction logs is growing over 100GB and filling up. The DBA is asking why the process isn’t adding a few commits between batches of data and states that this behavior is not typical for a DB this size. I think there may be more of an issue with this 10.0.2 (10.0.1) was fine.

At this point I’ve stopped the imports and will see if I can beg the DBA to give more transaction logs space, but I don’t even think they are going to increase the LUN as this is abnormal behavior.

[JasonWalker]

I’d expect 10.0.2 to use more transaction space (mostly due to providing the “Install Path” as part of the table data results), but I agree yours does sound really high…do you have a support incident open yet? We may need Support to watch what’s happening with your server.

[AlexaVonTess]

I’ll look to open one… Thanks for all your input.

[JasonWalker]

I’ve moved this thread to a new topic to keep it all in one place.

I’ve checked with the BFI devs and an increase the size of yours is not expected. A support case is the way to go. If you can post the incident number here I’d like to have a look at it too; most of the customers I work with have upgraded to 10.0.2 without issue, but I do know of one other having similar issues with transaction logs and tempdb usage.

[AlexaVonTess]

I was able to get our DBA to give me 300GB for the Transaction log to grow (keep in mind my database is 165GB). Well, it worked. It only too just over 11 hours! The Transaction log grew to 230GB at around 57% completion (where I think all the heavy lifting is complete). I re-enabled the daily import process and hope that things return to normal. I have to think others would see this with this newer minor version of BFI, but we’ll see.

Thank you so much for you help!