Forum Discussion

Mark_Tkachyk's avatar
Mark_Tkachyk
Level 3
12 years ago

Items building up in watchfile and journalarchive tables

I have a customer with EV 9.0.2 that has built up a large backlog of items awaiting backup.    I took a look at how they were doing their partition backups and identified some problems but that didn't explain the high number (over 7.5 million items).   I ran queries against the watchfile and journalarchive tables to see what was there and came up with this:

  

 

indexcommited

backupcomplete

count

0

0

64044

1

0

7623934

0

1

63882
     

itemsecured

count

 

0

591032

 

1

7096946

 

 

I tried to replicate what was going on in my lab but couldn't.   When I test in the lab I find that as items get archived, rows are added to watchfile with itemsecured=0 and to journalarchive with backupcomplete=0.   After backups of the partitions, the watchfile rows get changed to itemsecured=1 first and then the corresponding journalarchive rows get updated to backupcomplete=1 and the watchfile rows are deleted.   That's not what I'm seeing in the customer data.   For some reason, the journalarchive table rows are never getting set to backupcomplete=1 and the watchfile rows are not getting purged.   This would seem to indicate that the trigger files were picked up and processed but the second pass through the tables never occurred.    Has anyone seen this before and knew of a cause/resolution?

 

thanks,

Mark

Backing Up
Enterprise Vault
Information Governance
Troubleshooting
Windows Server (2003-2008)
  • Tremaine's avatar
    Tremaine
    12 years ago

    The Itemsecured in the Watchfile table just tells EV whether the DVS file has been backed up or not. The Backupcomplete in the JournalArchive table when set indicates that any/all associated SISParts from remote VSDB's have also been backed up and you should be able to tell this from the 'Secured' column in the Fingerprint DB for any associated SiSParts (Obviously you need to track them all through)

    In some scenario's it is possible that the Secured column in the FPDB for the SISPart is set to 0 albeit the DVS/SisParts in the remote (shared) VSDB have been backed up. When this happens on earlier (Pre V10.0.2) EV versions the code and SQL Stored Procs dealing with post processing of these records had some shortcomings and could potentially result in massive amounts of processing and never actually achieve anything depending on the range of the sequence numbers to be processed and because of this may never actually get to processing of recently archived items as the process spends all its time potentially looking for legacy content to process. This can end up in a vicious cycle that just gets worse over time.

    I am speculating a bit based on the description here, however it is a possibility. Would really need to see some Dtrace as well entries in the DB's to confirm. Suggest raising a support call or upgrade.

7 Replies

Sort By
  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago
    Only time I've seen that is when the compatibility is set to SQL2000 and not 2005+ Other than that there is an issue is 9 sp3 so it's doubtful you have that issue
  • Mark_Tkachyk's avatar
    Mark_Tkachyk
    Level 3
    12 years ago

    Compatibility level reads "SQL Server 2008 (100)" and the SQL instance is running 2008 R2.

  • JesusWept3's avatar
    JesusWept3
    Level 6
    12 years ago
    Any chance of a dtrace of StorageFileWatch and restart of storage services and trace for about 15 minutes?
  • GertjanA's avatar
    GertjanA
    Moderator
    12 years ago

    Hello Mark,

    How is the maintenance on the databases? I see the same in the environment where I am in, and that is mainly due to the huge defragmentation of indexes on the databases. If you check the KB's look for 'SQL Maintenance'. There is a doc that shows which SQL command to run to get the indexdefragmentation. (something with DBCC)

    If you find high defragmentation, then you need to setup a maintenance job, and make sure that runs to completion. You will need (depending on your db-size) a lot of additional SQL Log disk space, but once finished, it should be ok.

  • Tremaine's avatar
    Tremaine
    Level 6
    12 years ago

    The Itemsecured in the Watchfile table just tells EV whether the DVS file has been backed up or not. The Backupcomplete in the JournalArchive table when set indicates that any/all associated SISParts from remote VSDB's have also been backed up and you should be able to tell this from the 'Secured' column in the Fingerprint DB for any associated SiSParts (Obviously you need to track them all through)

    In some scenario's it is possible that the Secured column in the FPDB for the SISPart is set to 0 albeit the DVS/SisParts in the remote (shared) VSDB have been backed up. When this happens on earlier (Pre V10.0.2) EV versions the code and SQL Stored Procs dealing with post processing of these records had some shortcomings and could potentially result in massive amounts of processing and never actually achieve anything depending on the range of the sequence numbers to be processed and because of this may never actually get to processing of recently archived items as the process spends all its time potentially looking for legacy content to process. This can end up in a vicious cycle that just gets worse over time.

    I am speculating a bit based on the description here, however it is a possibility. Would really need to see some Dtrace as well entries in the DB's to confirm. Suggest raising a support call or upgrade.

  • Dead-Data's avatar
    Dead-Data
    Level 5
    12 years ago

    @Ghost

    Question, are the issues with unsecured remote SISparts more likely when sharing is at the Vault Store group level rather than within a Vault store? 

  • Tremaine's avatar
    Tremaine
    Level 6
    12 years ago

    Couldnt say for sure. I would expect the probability will be higher though.