|Applies To||RSA Product Set: Archer|
|Issue||RSA recommends that customers set up a process to periodically rebuild the indexes for the Archer instance database. Many DBAs set up their own process to do this. |
As an alternative, the Archer installation package includes a SQL script (JobDeploy.sql) to create a recurring SQL Agent job -- RSA Archer Database Index Rebuild. This job executes a stored procedure:
This script uses the default setting for the SQL command to rebuild indexes OFFLINE.
ALTER INDEX REBUILD
This means that tables are locked while they are being indexed. As a result, while the database index job is running, some Archer processes may fail with a database connection error.
|Cause||If a user or process tries to access Archer while tables are locked by the database index rebuild job, the Archer log files may contain errors such as:|
|Resolution||To avoid this problem, the Database Index Rebuild job can be modified to use an alternate version of the stored procedure:|
This version of the stored procedure performs the index rebuild with the ONLINE option:
ALTER INDEX ALL REBUILD WITH (ONLINE = ON)