• State Not Answered
  • Locked Locked
  • Replies 1 reply
  • Subscribers 92 subscribers
  • Views 1768 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Very Large HistoryDB Transaction Log

Michael
over 5 years ago

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?