Recently, we re-enabled the HistoryDB after the client had been disabled for some time. Once we reenable, we noticed the HistoryDB’s transaction log grew very large very fast until it ran we out of space the drive. So we decided to review the history tables on the MainDB.
TableName SchemaName RowCounts TotalSpaceKB TotalSpaceMB UsedSpaceKB UsedSpaceMB UnusedSpaceKB UnusedSpaceMB
DialogWatchProperty | dbo | 28592873 | 14413016 | 14075.21 | 14411352 | 14073.59 | 1664 | 1.63 |
DialogWatchOperation | dbo | 6627413 | 6591344 | 6436.86 | 6589096 | 6434.66 | 2248 | 2.2 |
JobHistory | dbo | 1888594 | 11633456 | 11360.8 | 11628248 | 11355.71 | 5208 | 5.09 |
DialogProcessChain | dbo | 1075576 | 863808 | 843.56 | 862968 | 842.74 | 840 | 0.82 |
DPRJournalObject | dbo | 1038197 | 1302656 | 1272.13 | 1302328 | 1271.8 | 328 | 0.32 |
EX0AddrListEntry | dbo | 843580 | 665688 | 650.09 | 664688 | 649.11 | 1000 | 0.98 |
QBMSplittedLookup | dbo | 761049 | 584560 | 570.86 | 583736 | 570.05 | 824 | 0.8 |
**** After Truncating*****
TableName SchemaName RowCounts TotalSpaceKB TotalSpaceMB UsedSpaceKB UsedSpaceMB UnusedSpaceKB UnusedSpaceMB
DialogWatchOperation | dbo | 1536 | 2096 | 2.05 | 1888 | 1.84 | 208 | 0.2 |
DialogWatchProperty | dbo | 1339 | 856 | 0.84 | 776 | 0.76 | 80 | 0.08 |
JobHistory | dbo | 298 | 1728 | 1.69 | 1600 | 1.56 | 128 | 0.13 |
We noticed that the DialogWatchProperty, DialogWatchOperation & Job History could be our problematic tables and decide to truncate. Then we restarted the HDB service and ran the schedules to trigger a transfer. After about hour we noticed the HistoryDB Transaction log grew just like before. So we stopped the service and deleted the single job in the DBQ on the HistoryDB and ran the query below to count the rows on the HistoyDB log file.
Ran the below query on the HistoryDB
Select count (*) from fn_dblog (null,null)
Results: 545,084,237
Now that number of 500 million rows just seems outrageous. So any idea what is causing this HistoryDB transaction log to grow so large? What can we do to solve for this issue?