000034178 - Database connection errors while RSA Archer Database Index Rebuild is running in RSA Archer GRC

Document created by RSA Customer Support Employee on Oct 11, 2016•Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 3Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000034178
Applies ToRSA Product Set: Archer
 
IssueRSA 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. 
CauseIf 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, 
  Version=5.5.10100.1009, Culture=neutral, PublicKeyToken=null</ExceptionType>
<Message>A transport-level error has occurred when receiving results from the server. 
  (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

 
ResolutionTo 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)


The ONLINE option allows concurrent user access so it does not cause Archer processes to fail while the database indexes are being rebuilt.
To modify the RSA Archer Database Index Rebuild job to use the alternate stored procedure with the ONLINE option:


  1. Open SQL Server Management Studio.
  2. In the Object Explorer pane, expand SQL Server Agent and expand Jobs.
  3. Right-click on the job RSA Archer Database Index Rebuild (My_Archer_Instance) and select Properties.
  4. Under Select a Page, click Steps.
  5. Highlight Step 1 (that is the only step) and click Edit.
  6. In the Command text box, the third line will be: 
exec usp_reindex_db

  1. Change this to: 
exec usp_reindex_db_2

  1. Click OK.

Attachments

    Outcomes