In high volume SafeCom Multiserver environment where the slave servers are set to use offline tracking, the Tracking database on the slave server can become so big, that collection of offline tracking data stalls.
The result on the Master server is that tracking data from specific periods of time seems to be missing.
The reason can be that because of the size of the slave database, the search routine during collection of offline tracking data, takes longer than allowed by the timeout. The result is that the SafeCom Master server will wrongly report that no tracking data was to be collected from the slave, and in SafeCom Administrator the log will state: "Slaveserverxx: 0 entries collected"
By making sure that the columns "ExportID" and "trackingstate" in the sctracking table of the sctracking database is indexed, the search routine will complete much faster.
This will improve the performance success rate of the collection of offline tracking data from Slave to Master database.
The attached sql script will create the mentioned indexes.
When completed, the sql search time for tracking data will be dramatically improved.
It is to be run from a SQL Query in SQL Management Studio while connected to the Slave servers database.
At the time running the script, the Print Spooler service and SafeCom Service on the slave must be stopped. And no other SQL connection to the Slave database be open. Please also ensure that the SafeCom master server will not attempt to collect tracking data from the slave at the time the script is run. Eventually temporary disable collection of offline tracking in SafeCom Administrator, server settings for the Master server.
Please note that while the SQL script is running, it has to be allowed time to complete.
A FULL SQL backup of the Slave database should be done prior to running the script.
1. Do a FULL SQL backup of the SafeCom slave database. (sccore, scevent, scpurse, sctracking)
2. In SafeCom Administrator, look at the server settings for the Master 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.
3. On the Slave server, stop the Print Spooler service and SafeCom service.
(If the slave is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service offline)
4. Close any SQL connection to the slave database
5. Open up one instance of SQL Management Studio, and connect to the Slave database
6. Open a new Query Analyzer (File, New Query)
7. Paste in the content of the attached .sql file into the Query.
8. Press the "Parse" button to validate the script. If successful continue
9. Press the "Execute" button
Allow for the process to complete. If the tracking database contains like 2 million entries, the process depending on the speed of the SQL server, could take more than 5 minutes.
10. When done, the SQL query will respond with "Command(s) completed successfully."
11. Close the SQL Management Studio.
12. On the Slave server start the Print Spooler service and SafeCom service
(if the slave is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service online)
13. In SafeCom Administrator, enable collection of offline tracking in the server settings for the Master server. Tracking tab.
Enable collection for "Monday", "Tuesday", .... Press the "Apply button to commit changes"
Create ExportID and tracking state indexes.zip (367 bytes)