Question / Problem:
In high volume SafeCom Multiserver environments where the secondary servers are set to use offline tracking, the Tracking database on the secondary server can become so large that collection of offline tracking data stalls.
The result is that on the primary server, the tracking data from specific periods of time seems to be missing.
Answer / Solution:
This can be caused by the size of the secondary database. The search routine used during collection of offline tracking data takes longer than allowed by the timeout. The result is that the SafeCom primary server will wrongly report that no tracking data was to be collected from the secondary server.
To improve performance, the columns "ExportID" and "trackingstate" in the sctracking table inside of the sctracking database should be indexed. This will improve the performance success rate of the collection of offline tracking data from secondary to primary database.
The attached sql script will create the mentioned indexes. When completed, the sql search time for tracking data will be dramatically improved.
Note: The provided script needs be run from a SQL Query in SQL Management Studio while connected to the secondary servers database.
At the time running the script, the Print Spooler service and SafeCom Service on the secondary must be stopped. And no other SQL connection to the secondary database be open. Please also ensure that the SafeCom primary server will not attempt to collect tracking data from the secondary at the time the script is run.
Please note that while the SQL script is running, it has to be allowed time to complete.
A FULL SQL backup of the secondary database should be done prior to running the script.
- Do a FULL SQL backup of the SafeCom secondary database. (sccore, scevent, scpurse, sctracking)
- In SafeCom Administrator, look at the server settings for the primary server. Tracking tab.
- Make sure collection of offline tracking will not happen within the next hour.
- Alternatively temporary disable collection of tracking data by unticking checkboxes for "Monday", "Tuesday",..... Press Apply button to commit changes.
- On the secondary server, stop the Print Spooler service and SafeCom service.
- If the secondary is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service offline
- Close any SQL connections to the secondary database
- Open up one instance of SQL Management Studio, and connect to the secondary database
- Open the attached SQL file.
- Press the "Parse" button to validate the script.
- Press the "Execute" button
- Note: Allow for the process to complete. If the tracking database contains a large number of entries, the process could take more than 5 minutes.
- When done, the SQL query will respond with "Command(s) completed successfully."
- Close the SQL Management Studio.
- On the secondary server start the Print Spooler service and SafeCom service
- If the secondary is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service online
- In SafeCom Administrator, enable collection of offline tracking in the server settings for the primary server. Tracking tab.
- Enable collection for "Monday", "Tuesday", .... Press the "Apply button to commit changes"
Create ExportID and tracking state indexes.zip (367 bytes)