Transaction log occasionally grows rapidly
In OBM 2019.05 Classic on Windows we have had an issue since way back where the transaction log file for the Event DB suddenly grows rapidly (we use a dedicated MS SQL Server, with Windows authentication). The size of our Event DB is just 10GB, but we have experienced that the transaction log file has grown to 200-300 GB and thus filling up the disk where it resides.
We have had a support case on this previously. It did not really help us much. The R&D pushed hard to make us change the recovery mode of the DB to Simple. We did try this for a while, and we certainly did not experience any problems during that period, but we had to change it back to Full RM again, since we intend to establish an SQL AlwaysOn solution. Based on suggestions from R&D, we also increased the frequency of the transaction log backup to every 15 minutes. We were then forced to close the support case, as MF's R&D basically claimed that everything else was by design.
Since then (back in February) the transaction log file has not grown that much, until last week. It then two nights in a row grew rapidly again. The second night the file reached 100GB, which is less than experienced previously, but still unacceptably high, considering that the actual DB-file size is just 10GB.
Do to our previous bad experiences with this, one of our DB people has created a solution that triggers when the actual transaction log file grows, and then catches what is going on in the DB. When the logfile size grew to 100GB he discovered that one specific SQL query was executed literally hundreds of times over each of four different 5-10 minute long time periods during the night (starting at 16:32, 23:30, 03:52 and 04:30, respectively). The query was:
(@P1 int,@P2 varchar(8000),@P3 varchar(8000),@P4 varchar(8000),@P5 varchar(8000),@P6 varbinary(max),@P7 bit,@P8 varchar(8000),@P9 varchar(8000),@P10 int)update CFG_PKG_DEPLBIN set VERSION= @P1 , PROCESSOR_ARCH= @P2 , OS= @P3 , PATCH_ID= @P4 , BDL_DESCRPTR_FILE= @P5 , CONTENT= @P6 , IS_STUB= @p7 , DEPLPKG_VERS_ID= @P8 where ID= @P9 and VERSION= @P10
I realize this is a rather detailed and internal technical question to post here, but since support was not able/willing to dig any further into this, I would still like to ask; What could possibly cause OBM to try to update this particular table (CFG_PKG_DEPLBIN) that frequently?
Could we perhaps have a problem with OBM's package inventory, trigging this somewhat crazy behaviour under certain circumstances? We have not installed any new packages during the last couple of months, by the way.
If I am not mistaken, that table is used for the node synchronization packages, you may want to double-check if you have been doing "synchronize installed packages information" for your nodes (check for failed deployment jobs). Also double-check the packages you currently have and make sure your jobs don't complain because of a non-existing package can be deployed. # opr-package-manager.sh -lp -user xx -password xx
That table also is known for using large objects when performing such tasks. I'd recommend you to also check your DB https://docs.microfocus.com/itom/Operations_Bridge_Manager:2019.11/DatabaseIntegFragm and make sure it does not need some maintenance
I have one Custoemr that uses OMi with an MS AlwaysON SQL Cluster. I cant see the sympthoms that you describe.
So open a case about the query that runs hundred of times will be a good idea in my opinion.
Christian, that is very interesting info. We have not run any "synchronize installed packages information" towards nodes lately, that I am aware of (there are no failed deployments jobs for it either).
The opr-package-manager.sh -lp does not give any error messages, and it gives the following output:
Operations Agent Product
The interesting thing is that the number of rows in that CFG_PKG_DEPLBIN table is 25, which is more than expected based on the relatively short output above, isn't it? Or could this be due to previous such package synchronizations run towards nodes, perhaps? I mean, historically, some of our nodes have been migrated an old HPOM/Ux environment, and having nodes with OA version 11.14 and 12.01 (and probably other versions), and a package synchronizations were probably run from OMi towards them during the migration.
Many of the rows in the CFG_PKG_DEPLBIN table contain NULL in some of the columns. I have uploaded an image showing the full content of the table (all 25 rows).
Andreas, good to hear that your customer does not suffer from the same problem. I would also like to mention, though, that we have not yet migrated to SQL AO. We intend to do so, but our DB admin is somewhat sceptic, due to this transaction log file issue.