Re-Initializing Cache Database
THIS ARTICLE APPLIES TO:
PROCESS:
Below are the steps to be used to re-initialize the cache database.
- Stop IIS, Moxie Channels service and All CommCore services
- Go to Start > Run > Type “Regedit” and take a backup of the registry using File > Export Option.
- Double click on INIT_CACHE and set the value from ‘0’ to ‘1’ and click OK
(INIT_CACHE can be found on the application server’s registry path: HKLM/Software/Wow6432Node/MoxieSoft/NetAgentServer//CurrentVersion )
Start only channels service. This will reinitialize the cache database. -
If the Cache DB re-initialization is successful, you should see the below message in the DBTool log file.
If channels service throws an error at this point, it may be with an error similar to the one shown below:
If the error is seen, Stop Channels Service. Take a backup of existing cache database. DROP or DELETE the CacheDatabase. Recreate the Cache Database with the same name. You can refer the value in the ‘Database’ column from result of query below for same companyId to get the Cache DB Name. SELECT * FROM [Channels MainDB Name]..DatabaseConnection Now that you have an empty cache database, perform steps 3, 4 & 5 again. Once Cache DB is re-initialized successfully, execute the following SQL query On the Cache Database only if you are on 9.2 HF1 Patch 7 with Service releases:Execute the following SQL query On the Cache Database: \ \conf\ a) 9201_P02_Cache.sql b) 9201_P07_SR05_Cache.sql c) 9201_P07_SR06_Cache.sql d) defaultdata_mssql_cache.sql e) makeprocs_mssql_cache.sql Note: Patch 8 and above does not need these queries to be run. Start all the Commcore services at this point. Once Commcore services are started successfully, run the below SQL query on the main database to validate if the agent permissions are synchronized.
SELECT Count(1) FROM
UserMailboxPermissions A LEFT JOIN [Cache Database Name].dbo.CacheAgentPermission B ON A.UserID = B.AgentID AND A.MailboxID = B.SubchannelId AND B.ChannelType = 2
WHERE B.Permission IS NULL
If the query returns the count as 0, then the permissions are in sync.
But if the query returns a nonzero value, follow the below steps to Re-Sync it.
IF EXISTS (select TOP 1 1 from [Cache Database Name].dbo.sysobjects where name = 'FK_CACHEAGENTPERMISSION_AGENTID' and xtype = 'F')
ALTER TABLE [Cache Database Name].dbo.CacheAgentPermission DROP CONSTRAINT FK_CACHEAGENTPERMISSION_AGENTID
Go
DELETE FROM [Cache Database Name].dbo.CacheAgentPermission
Go
INSERT INTO [Cache Database Name].dbo.CacheAgentPermission (AgentID, SubChannelID, ChannelType, Permission, CanRoute, CompanyID)
SELECT A.ID, B.MailBoxID, 2, B.Permissions, CASE (B.Permissions & 2) WHEN 2 THEN 1 ELSE 0 END, 1
FROM Users A INNER JOIN UserMailboxPermissions B ON A.ID = B.UserID
WHERE A.Status = 0 AND B.DepartmentID = -1
ORDER BY A.ID, B.MailboxID
Go
Notes:- Replace the “Cache Database Name” in above sql with appropriate cache database name before executing them.
- If there are errors about duplicate keys while running the above query, please stop all the Channels services and then run the query. Now rerun the validation query to confirm the data has been synchronized. In case you find the validation query still returns nonzero value, it might be due to emails in personal mailbox for a particular user / users in inactive state (status=2). To address this issue, enable the user temporarily and re-run the SQL script for synchronization of data. This time the validation script should return 0 results. Query to find deactivated user’s having emails in personal mailbox
Note: This query is to be run on the Main database.
SELECT A.ID, B.MailBoxID, 2, B.Permissions, CASE (B.Permissions & 2) WHEN 2THEN 1 ELSE 0 END, 1
FROM Users A INNER JOIN UserMailboxPermissions B ON A.ID = B.UserID
WHERE A.Status <> 0 AND B.DepartmentID = -1
ORDER BY A.ID, B.MailboxID
Once you identify the Agent ID from the above query, use this query to enable the agent.
Update Users
Set Status=0
Where ID=<Agent ID>
Now run the Data synchronization query again and confirm the validation result is 0. Once verified, please deactivate the agent id using the query below
Update Users
Set Status=2
Where ID=<Agent ID>