Hi, we have recently done a fresh install of HMP 11.0.1 on a clean Windows 2008 R2 server and migrated our existing database to this new server which is also running SQL 2008 R2 SP1.

Since installing and migrating to HMP Pro 11 we are seeing our priority manager service crashing regularly (sometimes a couple of times a day). Below is a copy of the event log error that triggers the service shutdown. After this error occurs the service shuts down successfully however it doesn't trigger a restart. Is there anything we can do to resolve this issue or get the service to restart automatically?

Please note that we have already set the service to restart after a subsequent failures. This doesn't seem to restart it.

Thanks.

------------------------------------------------------------

Error Log from HMP Priority Manager

4/08/2011 9:08:18 AM : An error has occured on Processing Active Profiles.
- Error Code Location: 7
- Error Description: 'System.Data.SqlClient.SqlException: Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at HMP.DatabaseAccess.clsHMPObject.GetDataSet(String p_SQL)
at HMP.PriorityManager.Service.PriorityManagerService.N9odxSsog()'
- strSQL: SELECT DISTINCT tblHelpdeskJobs.pkID
FROM tblHelpdeskJobs INNER JOIN
tblLink_Jobs_Clients ON tblHelpdeskJobs.pkID = tblLink_Jobs_Clients.JobLink LEFT OUTER JOIN
tblLink_Staff_Skillgroup ON tblHelpdeskJobs.LoggedByLink = tblLink_Staff_Skillgroup.StaffClientLink LEFT OUTER JOIN
tblLink_Jobs_Assets ON tblHelpdeskJobs.pkID = tblLink_Jobs_Assets.JobLink LEFT OUTER JOIN
qryCrossTabCustomDetails_Job ON tblHelpdeskJobs.pkID = qryCrossTabCustomDetails_Job.JobPKID_Custom LEFT OUTER JOIN
qryRelationshipsSitesLinkedToJobs ON tblHelpdeskJobs.pkID = qryRelationshipsSitesLinkedToJobs.JobPKID LEFT OUTER JOIN
qryJob_TimeMetrics_Business_Total_Extended ON tblHelpdeskJobs.pkID = qryJob_TimeMetrics_Business_Total_Extended.JobPKID LEFT OUTER JOIN
tblLink_Staff_Skillgroup AS tblLink_Staff_Skillgroup_2 ON tblHelpdeskJobs.AssignedToLink = tblLink_Staff_Skillgroup_2.StaffClientLink LEFT OUTER JOIN
qryRelationshipsClientsLinkedToSites_All ON tblLink_Jobs_Clients.ClientLink = qryRelationshipsClientsLinkedToSites_All.ClientPKID LEFT OUTER JOIN
tblLink_Staff_Skillgroup AS tblLink_Staff_Skillgroup_1 ON tblHelpdeskJobs.ClosedByLink = tblLink_Staff_Skillgroup_1.StaffClientLink LEFT OUTER JOIN
qryKeywords_Job ON tblHelpdeskJobs.pkID = qryKeywords_Job.JobPKID
WHERE (tblLink_Jobs_Clients.IsPrimaryClient = 1) AND
DateLogged >= { ts '2009-09-01 00:00:00' } AND DateLogged <= { ts '2030-11-05 23:59:59' } AND PriorityLink IN (109) AND (dbo.tblHelpdeskJobs.StatusLink <> 2)

Hi Jesse, thanks for your post. We are currently working on resolving this issue permanently but do have a quick fix for you now. You need to truncate your Priority Manger log table and set your Priority Manager Event Log Options on the Priority Manager Service Setup screen to default settings as follows;

Truncate the Priority Manager event log table by running the following SQL against your HelpMaster database;

TRUNCATE TABLE dbo.tblPriorityManagerLog

To set the Priority Manager Event Log Options back to the default settings;

1. Go to "Automation" (ribbon) > Priority manager "Service Setup" (option) > "Event Logging Options" (tab) > "Database event logs" (section)
2. Set "Log these events to teh database event logs" to "Everything", and
3. Set "Only keep event logs for" to "1 year", and "Don't exceed this many event entries" to "10,000 Log Items".

Note that the Windows event log under Applications and Services Logs will still continue to log events based on settings configured there.

Let us know how you go after doing the above. Thanks.

Dennis

Hi Dennis,

I didn't realise that Stewart was also talking to you guys about this issue. I can confirm that your work around has resolve the crash issue.

Thanks,

Jesse