Article Content
Article Number | 000034178 |
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: usp_reindex_db 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:<ExceptionType>ArcherTech.Kernel.Utility.Data.ArcherDbException, ArcherTech.Kernel, |
Resolution | To avoid this problem, the Database Index Rebuild job can be modified to use an alternate version of the stored procedure:usp_reindex_db_2 This version of the stored procedure performs the index rebuild with the ONLINE option: ALTER INDEX ALL REBUILD WITH (ONLINE = ON)
exec usp_reindex_db
exec usp_reindex_db_2
|