Improve batch archiving speed

The speed at which batches are archived can be affected by the number of entries in the AdminLog table in the DVHisDB database. The AdminLog table contains records of batches, campaigns, and events that have been archived or deleted and records when the Batch Historian has stopped or started.

Periodically check the number of entries in the AdminLog table. If the number of entries is greater than 20,000, export and delete all admin data to improve archive performance. The time it takes archive batches rises exponentially with the number of entries in the AdminLog.

Check the number of entries in the Adminlog table in DVHisDB

Start SQL Server Management Studio.
Connect to the Batch Historian Server node.
Expand the Historian server node name.
Expand the Databases folder below the Batch Historian SQL Server.
Expand the DVHisDB folder.
Right-click on the dbo.AdminLog and click Open Table. The application displays the number of entries.

Export the AdminLog table entries from DVHisDB

Start SQL Server Management Studio.
Connect to the Batch Historian Server node.
Expand the Batch Historian server node name.
Expand the Databases folder below the Batch Historian SQL Server.
Right-click DVHisDB database and click Tasks, then click Export Data.
Select the SQL Native Client date source and DVHisDB as the database.
Select the Destination preference (for example, Excel) and a file path to store the file.
Click Copy data from one or more tables or views.
Select Source Table [DVHisDB].[dbo].[AdminLog] by checking the appropriate box.
Click Execute immediately, and then click Finish.
Locate the output file and make sure the export was created successfully.

Delete old entries in the Adminlog table for DVHisDB

Run the following SQL query to delete records that have been exported. The example below deletes all records in the last three months.

delete from AdminLog 
where occurTime < dateadd(month, -3, GETDATE ())