Archive

Posts Tagged ‘SQL Timeout’

SQL Server Timeout Error – Importing CSV file

Other day I got a SQL Timeout error while importing Article’s using a CSV file which is of 75 KB size.

SQL Timeout Error - Data Import

SQL Timeout Error – Data Import

Below are my troubleshooting steps and Fix .

Troubleshoot steps

  • Verified both SQL Server and CRM App server’s event viewer.
  • On CRM App server there was only one warning with an ‘Insert’ query. However there was no error information in the log, it’s just that Query was not completely written in the log.
CRM Warning - Insert Query

CRM Warning – Insert Query

  • On SQL Server machine there were no Error or Warning event logs. But there was an Information log around same time which had useful information.
Log - Autogrow of file

Log – Autogrow of file

Reason

  • In the SQL Server event log, there was an event with below details

 “Autogrow of file ‘mscrm’ in database ‘XXXX_MSCRM’ was cancelled by user or timed out after 30824 milliseconds” information.

  • So, the issue was with my CRM DB (i.e.XXXX_MSCRM) setting’s “AutoGrowth” property which has higher ‘File Growth’ value.
  • It is recommended to keep 1024 MB for data files and 256 MB for log files.

Fix

  • Open ‘XXXX_MSCRM’ AutoGrowth setting by following below steps
    • Right click on the database.
    • Go to Properties.
    • Then go to the Files section.
    • Click on either the data or log autogrowth column (Click the …)
  • Provide the recommended ‘File Growth’ setting
Auto Growth Setting

Auto Growth Setting

Refer MSDN AutoGrowth Settings article for more details

🙂

SQL Timeout error while exporting solution – CRM

Other day my CRM solution export stopped working and I was getting ‘SQL Timeout’ exception while export.

In the event viewer there was an entry with below content

Query execution time of 30.7 seconds exceeded the threshold of 10 seconds. Thread: 38; Database: {Organization_MSCRM}; Server:192.168.36.7; Query: update [WorkflowBase] set [SupportingSolutionId]=’00000000-0000-0000-0000-000000000000′, [OverwriteTime]=’01/01/1900 00:00:00′

Its bit surprising because my solution hardly had 10 components and timeout due to oversize is unlikely.

Troubleshoot steps 

  • Reset IIS and Restarted CRM Async & SQL Services
  • Finally restarted both CRM Application server and DB Server to complete software updates. But no luck.

 Reason

With failed restart machines solution I dig deeper in to event viewer log and found the reason

  • In my case since my event viewer log pointing to “update [WorkflowBase]” query I thought problem would be with workflow. I removed all workflows from my solution and tried exporting. But no luck.
  • Then I removed my ‘SLA’ from the solution since SLA create workflows when you configure SLA Items. Now solution export worked.

Fix

  • By fixing the SLA issue (i.e., In our case we delete a field from Case entity and same not updated in SLA Success criteria). After correcting my SLA  I was able to include SLA in my solution and export .

🙂

Categories: CRM Tags: , ,