SageCRM unexpected error and SQL error

This question is not answered

Hi,

I am having a big problem with sageCRM at the moment. When my client tries to update a field against a company in CRM, CRM will eventually time out with an error "SQL error". There is no error code attached to this message.

Similarly, they are trying to create an appointment and CRM shows "unexpected error has occurred". It appears that any update/insert into sql is causing a big issue. 

I have extracted the update query out of the logs and run this directly in sql to see if i get anything. SQL subsequently times out. If we restart the server, then CRM will work for a bit but then the errors return.

Here is an entry in the sql logs:

Feb 1 2019 7:24:45.200	7124	5160	1	execsql,time,sql,errormsg	60532	UPDATE PhoneLink SET plink_createdby=1,plink_createddate='20190201 07:23:44',plink_entityid=5,plink_phoneid=89597,plink_recordid=15410,plink_type=N'Business',plink_UpdatedBy=1,plink_TimeStamp='20190201 07:23:44',plink_UpdatedDate='20190201 07:23:44' WHERE ( PLink_LinkId = 89597)
	Query timeout expired
Feb 1 2019 8:09:51.382	7124	708	1	fqopen,time,sql,errormsg	181922	SELECT * from vListCommunication WHERE ((CmLi_comm_userid IN (30)) AND (((comm_todatetime BETWEEN '20190201 00:00:00' and '20190201 23:59:59') or (comm_datetime BETWEEN '20190201 00:00:00' and '20190201 23:59:59') or ('20190201 00:00:00' BETWEEN comm_datetime and comm_todatetime)))) AND ( COMM_Status = N'Pending' OR  COMM_Status = N'InProgress') AND ( comm_type = N'Appointment' ) ORDER BY cmli_comm_userid
	Unspecified error


Any idea what could be causing this? It has stopped all work in CRM for my client.

Regards
Matt
All Replies
  • Hi Matthew,

    Could you show the full text on each line. Just so I can see what the actual query is that it is trying to run.

      CRM Consultant

    Pinnacle Computing Ltd

  • Hi Matthew,

    Here is the update:

    SQL Error : Query timeout expired (UPDATE PhoneLink SET plink_createdby=1,plink_createddate='20190201 07:23:44',plink_entityid=5,plink_phoneid=89597,plink_recordid=15410,plink_type=N'Business',plink_UpdatedBy=1,plink_TimeStamp='20190201 07:23:44',plink_UpdatedDate='20190201 07:23:44' WHERE ( PLink_LinkId = 89597))

    Here is the appointment:

    SQL Error : Unspecified error (SELECT * from vListCommunication WHERE ((CmLi_comm_userid IN (30)) AND (((comm_todatetime BETWEEN '20190201 00:00:00' and '20190201 23:59:59') or (comm_datetime BETWEEN '20190201 00:00:00' and '20190201 23:59:59') or ('20190201 00:00:00' BETWEEN comm_datetime and comm_todatetime)))) AND ( COMM_Status = N'Pending' OR  COMM_Status = N'InProgress') AND ( comm_type = N'Appointment' ) ORDER BY cmli_comm_userid

    )

  • Have you checked things like how much hard drive space there is. Also have you restarted the SQL instance then tried to run the query in SSMS immediate after it has started. It sounds more like a SQL Server issue than CRM if the query also times out in SSMS.

    Sage CRM specialist and all round fan! Please feel free to follow me on twitter @dannycrm

  • Also for the PhoneLink one, is there any SQL Triggers on that table?

      CRM Consultant

    Pinnacle Computing Ltd

  • How many records are in phone link ?

    You could try rebuilding the phone link table there's a documented process for doing it.

  • Sorry for late reply. Checked now, there is 13gb free on the hard drive, im thinking this is a bit low so could be the issue.

    Restarting the sql server seems to fix the issue, however only for a short time. The locks will continue after some user activity.

  • 89968 total records in the table.

    I did try rebuilding indexes prior to posting here across the entire db to try solve the issue.