I ran a bunch of Bacula dbcheck commands back in March 2023 and never published. Well, here they are.
I doubt this is useful to anyone else but I’d rather have this here and searchable by myself instead of gathering dust in the drafts folder.
[11:14 bacula dan ~] % dbcheck -c /usr/local/etc/bacula/bacula-dir.conf -f
Hello, this is the database check/correct program.
Modify database is on. Verbose is off.
Please select the function you want to perform.
1) Toggle modify database flag
2) Toggle verbose flag
3) Repair bad Filename records
4) Repair bad Path records
5) Eliminate duplicate Filename records
6) Eliminate duplicate Path records
7) Eliminate orphaned Jobmedia records
8) Eliminate orphaned File records
9) Eliminate orphaned Path records
10) Eliminate orphaned Filename records
11) Eliminate orphaned FileSet records
12) Eliminate orphaned Client records
13) Eliminate orphaned Job records
14) Eliminate all Admin records
15) Eliminate all Restore records
16) Eliminate all Verify records
17) All (3-16)
18) Quit
Select function number: 9
Checking for orphaned Path entries. This may take some time!
Found 300000 orphaned Path records.
Deleting 300000 orphaned Path records.
Found 300000 orphaned Path records.
Deleting 300000 orphaned Path records.
Found 167434 orphaned Path records.
Deleting 167434 orphaned Path records.
1) Toggle modify database flag
2) Toggle verbose flag
3) Repair bad Filename records
4) Repair bad Path records
5) Eliminate duplicate Filename records
6) Eliminate duplicate Path records
7) Eliminate orphaned Jobmedia records
8) Eliminate orphaned File records
9) Eliminate orphaned Path records
10) Eliminate orphaned Filename records
11) Eliminate orphaned FileSet records
12) Eliminate orphaned Client records
13) Eliminate orphaned Job records
14) Eliminate all Admin records
15) Eliminate all Restore records
16) Eliminate all Verify records
17) All (3-16)
18) Quit
Select function number:
That's 767,434 rows deleted. The table now contains 8,339,568 rows... about a 10 percent reduction
I'll do some more tomorrow.
1) Toggle modify database flag
2) Toggle verbose flag
3) Repair bad Filename records
4) Repair bad Path records
5) Eliminate duplicate Filename records
6) Eliminate duplicate Path records
7) Eliminate orphaned Jobmedia records
8) Eliminate orphaned File records
9) Eliminate orphaned Path records
10) Eliminate orphaned Filename records
11) Eliminate orphaned FileSet records
12) Eliminate orphaned Client records
13) Eliminate orphaned Job records
14) Eliminate all Admin records
15) Eliminate all Restore records
16) Eliminate all Verify records
17) All (3-16)
18) Quit
Select function number: 10
Checking for orphaned Filename entries. This may take some time!
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 300000 orphaned Filename records.
Deleting 300000 orphaned Filename records.
Found 86363 orphaned Filename records.
Deleting 86363 orphaned Filename records.
1) Toggle modify database flag
2) Toggle verbose flag
3) Repair bad Filename records
4) Repair bad Path records
5) Eliminate duplicate Filename records
6) Eliminate duplicate Path records
7) Eliminate orphaned Jobmedia records
8) Eliminate orphaned File records
9) Eliminate orphaned Path records
10) Eliminate orphaned Filename records
11) Eliminate orphaned FileSet records
12) Eliminate orphaned Client records
13) Eliminate orphaned Job records
14) Eliminate all Admin records
15) Eliminate all Restore records
16) Eliminate all Verify records
17) All (3-16)
18) Quit
Select function number:
That's 46 x 300000 + 86363 = 13,886,363 records
Select function number: 17
Checking for Filenames with a trailing slash
Found 0 bad Filename records.
Checking for Paths without a trailing slash
Found 0 bad Path records.
Checking for duplicate Filename entries.
Found 0 duplicate Filename records.
Checking for duplicate Path entries.
Found 0 duplicate Path records.
Checking for orphaned JobMedia entries.
Found 1 orphaned JobMedia records.
Deleting 1 orphaned JobMedia records.
Checking for orphaned File entries. This may take some time!
Checking for orphaned Path entries. This may take some time!
Checking for orphaned Filename entries. This may take some time!
Found 254 orphaned Filename records.
Deleting 254 orphaned Filename records.
Checking for orphaned FileSet entries. This takes some time!
Found 0 orphaned FileSet records.
Checking for orphaned Client entries.
Found 0 orphaned Client records.
Checking for orphaned Job entries.
Found 0 orphaned Job records.
Checking for Admin Job entries.
Found 258 Admin Job records.
Deleting 258 Admin Job records.
Checking for Restore Job entries.
Found 105 Restore Job records.
Deleting 105 Restore Job records.
Checking for Verify Job entries.
Found 7 Verify Job records.
Deleting 7 Verify Job records.
1) Toggle modify database flag
2) Toggle verbose flag
3) Repair bad Filename records
4) Repair bad Path records
5) Eliminate duplicate Filename records
6) Eliminate duplicate Path records
7) Eliminate orphaned Jobmedia records
8) Eliminate orphaned File records
9) Eliminate orphaned Path records
10) Eliminate orphaned Filename records
11) Eliminate orphaned FileSet records
12) Eliminate orphaned Client records
13) Eliminate orphaned Job records
14) Eliminate all Admin records
15) Eliminate all Restore records
16) Eliminate all Verify records
17) All (3-16)
18) Quit
Select function number:
03-Oct 00:54 bacula-dir JobId 359872: Fatal error: ./bdb.h:140 Update failed: affected_rows=0 for UPDATE Job SET JobStatus='R',Level=' ',StartTime='2023-10-03 00:54:51',ClientId=43,JobTDate=1696294491,PoolId=0,FileSetId=0 WHERE JobId=359872
03-Oct 00:54 bacula-dir JobId 359872: Fatal error: ./bdb.h:140 Update failed: affected_rows=0 for UPDATE Job SET JobStatus='f',Level=' ',StartTime='2023-10-03 00:54:51',ClientId=43,JobTDate=1696294491,PoolId=0,FileSetId=0 WHERE JobId=359872
03-Oct 00:54 bacula-dir JobId 359872: Warning: Error updating job record. ./bdb.h:140 Update failed: affected_rows=0 for UPDATE Job SET JobStatus='f',EndTime='2023-10-03 00:54:51',ClientId=43,JobBytes=0,ReadBytes=0,JobFiles=0,JobErrors=1,VolSessionId=0,VolSessionTime=0,PoolId=0,FileSetId=0,JobTDate=1696294491,RealEndTime='2023-10-03 00:54:51',PriorJobId=0,HasBase=0,PurgedFiles=0 WHERE JobId=359872
03-Oct 00:54 bacula-dir JobId 359872: Warning: Error getting Job record for Job report: ERR=sql_get.c:303 No Job found for JobId 359872
03-Oct 00:54 bacula-dir JobId 359872: Error: Bacula 9.6.7 (10Dec20): 03-Oct-2023 00:54:51
JobId: 359872
Job: InitiateCopyToBaculaSD03-Full.2023-10-01_22.00.00_22
Scheduled time: 01-Oct-2023 22:00:00
Start time: 03-Oct-2023 00:54:51
End time: 03-Oct-2023 00:54:51
Termination: *** Admin Error ***
