cancel
Showing results for 
Search instead for 
Did you mean: 

Items building up in watchfile and journalarchive tables

Mark_Tkachyk
Level 3
Partner Accredited

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

1 ACCEPTED SOLUTION

Accepted Solutions

Tremaine
Level 6
Employee Certified

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.

View solution in original post

7 REPLIES 7

JesusWept3
Level 6
Partner Accredited Certified
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
https://www.linkedin.com/in/alex-allen-turl-07370146

Mark_Tkachyk
Level 3
Partner Accredited

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

JesusWept3
Level 6
Partner Accredited Certified
Any chance of a dtrace of StorageFileWatch and restart of storage services and trace for about 15 minutes?
https://www.linkedin.com/in/alex-allen-turl-07370146

GertjanA
Moderator
Moderator
Partner    VIP    Accredited Certified

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.

Regards. Gertjan

Tremaine
Level 6
Employee Certified

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
Level 5
Partner Accredited Certified

@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
Level 6
Employee Certified

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