SQL Timeouts in CRM 4.0 - import of customizations fail

When importing customizations to CRM 4.0, the import fails with a message "generic SQL error". Digging a bit deeper the error message is really that a timeout has occured. The same error occurs when trying to create a new entity.

I increased the timeout as suggested in the link below, but the timeout occured anyway - it just took longer time to happen.

Increasing the timeout:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM\OLEDBTimeout

This value does not exist by default, and if it does not exist then the default value will be 30 seconds. To change it, add the registry value (of type DWORD), and put in the value you want (measured in seconds). Then you have to recycle the CrmAppPool application pool for the change to take effect

https://community.dynamics.com/product/crm/crmtechnical/b/crmdavidjennaway/archive/2008/09/04/sql-timeouts-in-crm-generic-sql-error.aspx

The SQL profiler displays a set of inserts and updates related to the metadata in CRM, and then a call to the stored procedure

exec p_RecreateIndexes

This call is apparently the culprit and never completes in a timely fashion (30+ minutes now and no开发者_开发知识库t completed yet). This is an existing test instance of CRM and is quite extensively used and filled with lots of data. Creating new entities has never before taken this long. Just in case, I have run the asyncoperation cleaning scripts from MS. It did not have any visible effect.

Is there any way to find the reason for the delay in this procedure, or some other solution I can try?

Try splitting up your import into chunks. For example, import the first 20 entities, then the second 20 entities, and so on until you've imported all of them. Then publish. Then go back and try importing the entire customizations file at the same time and republish. Following this method exactly has been the only way we've found to import some customization files in particularly stubborn environments.

It sounds like the re-index operation is taking some time - which would be as expected if the data size is large, and the fragmentation is high. It also depends on exactly what that stored procedure does, and how many cores/CPUs you have - and how many SQL Server is allowed to use.

Does the app allow you to defer that operation? You'd be able to run it manually yourself through Management Studio - if that doesn't break the application.

You could be cheeky, and rename that procedure, and replace it with one of your own that does nothing.... and then rename back, and run. Again, it might break something.

Or just keep increasing the timeout until you get past this issue. Some of my re-index jobs on databases generally take hours....

Or contact the vendor if you have support?

If you ran that query through Management Studio, it would complete.... doing that would give you the approximate time required to put (temporarily) into the timeout setting.

I just experienced the exact same problem and I was only importing 2 entities.

I found your questions when I was googling for p_RecreateIndexes after seeing it in the Trace files.

I ended up running exec p_RecreateIndexes in SQL Server. After it completed - about 2 minutes - I reran the Entity import and it worked.