Technical Tip: FortiAnalyzer SQL database rebuild

Description

This article describes how to perform the FortiAnalyzer SQL database rebuild.

Although this procedure does not remove any log files, it is highly recommended to back up the system and log files beforehand as a precaution.

Scope

FortiAnalyzer.

Solution

Important notes:

• Resource-intensive process: Be aware that the SQL DB Rebuild is a resource-intensive and time-consuming process. Reports, Log View, and FortiView will not be fully usable until the rebuild is complete. Ensure the FortiAnalyzer meets the Minimum system requirements, as this process may significantly impact system stability and performance.

• Duration variability: The time required for the SQL DB rebuild depends on factors such as system resources, the amount of stored logs, logs being received during the process, and other simultaneous tasks. Consequently, it is difficult to predict the exact time needed; the process may take hours or even days in some cases.

• No acceleration: There is no way to speed up the SQL DB rebuild process with current resources. But in case of VM To help processing faster the rebuild process by adding temporary more CPU cores which will require to shutdown the VM, add cores and power on the VM again. Then check and monitor the rebuild process which suppose to resume once the VM is powered on again.

• Reboot required: The exec sql-local rebuild-db command causes the FortiAnalyzer to reboot, and the rebuild begins once the system is back online.

Initiate the rebuild process.

The FortiAnalyzer offers different options for starting the rebuild:

Rebuild for all ADOMs:

This is the default procedure, rebuilding the SQL database for all ADOMs. This may take a long time, depending on the volume of logs stored across all ADOMs.

execute sql-local rebuild-db

Rebuild for specific ADOM (not available since v7.6.x onwards):

The command rebuilds the database for a single, specific ADOM only:

execute sql-local rebuild-adom <AdomName>

Use this command in firmware v7.6.0 and onward:

execute sql-local rebuild-metadb

Rebuild from a specific date:

Defines the starting date from which the database is rebuilt.

config system sql
    set start-time <hh>:<mm> <yyyy>/<mm>/<dd>
end

Example: If the current date is 01-Sep-2025 and the logs for the last 90 days are needed, set the start date to 01-Jun-2025.

config system sql
    set start-time <00>:<00> <2025>/<06>/<01>
end

Check the rebuild process status:

Use the command below to see the progress of the database rebuild.

diagnose sql status rebuild-db

Command to check when the database migrates after v7.6:

diagnose sql status migrate-db

If the command above gives an error, try this one:

diagnose sql status sqlplugind
logtbl-upgrade: Running total=6623 finished=5947 perc=89% tbl-rewrite=no copy-upg=56 failed=0

When the rebuild is complete, the output of 'diagnose sql status rebuild-db' will confirm the completion date and time:

diagnose sql status rebuild-db
Rebuilding all database accomplished on
Wed Nov 6 07:32:46 2025

Troubleshoot the rebuild process.

diagnose test app sqllogd 4

Shows the specific log file the rebuilding process is currently handling. Must be run multiple times to track the SQL rebuild status over time.

If a specific log is stuck, it will show the file. It will be possible to delete the file from Log View -> Log Browse to continue the database rebuild.

diagnose sql show db-size

Checks whether the database size is increasing, indicating active rebuilding. Run this command multiple times, a few minutes apart, to confirm growth.

diagnose sql process list

Lists active SQL processes, which may sometimes show commands like 'Create table', 'Insert', or mention specific filenames. Use D to view background database activity.

Related documents:

• Technical Tip: Restarting SQL rebuilds

• Technical Tip: FortiAnalyzer SQL database rebuild start-time

• sql-local