CODEWITHSUNDEEP
pluginsdynamics-crm-2011
Abhishek gupta
Abhishek gupta

Reputation: 463

Sql Timeout Expired/ Operation timeout expired from SQL Server

The sql timeout expired and operation timeout expired, these 2 error message are mostly pop up in crm 2011.

I have written a plugin which access the NAV webservice and the update the order and order product entity.

The Database size is around 240 gb and around 1000 times the plugin written above process within 2 hours.

Kindly suggest the solution.

Upvotes: 0

Views: 972

Answers (1)

Zach Mast
Zach Mast

Reputation: 1718

Like Nick says we need more details, but this appears that your database operations might be failing under load. A 340 GB database probably has several tables with tens of millions of records, and 500 plugins firing an hour could be a significant amount of concurrency depending on the complexity of what the plugin is doing. In general the solution is to optimize your server infrastructure.

More specifically I would take a look at several potential actions, loosely in order of bang for buck:

Index Maintenance: Microsoft recommends that indexes with greater than 30% fragmentation be rebuilt, and those with greater than 10% be reorganized. Blog about CRM Index Maintenance

Indexing: Creating indexes for your large tables that experience a high level of concurrent access can greatly increase performance as well as reduce table locking. Indexes for CRM must be created in SQL server and this is supported by Microsoft.

Analyze the efficiency of your plugins: Are you only writing delta data? Are you limiting database reads to only bring back columns you require? Are you caching information that will not change within the scope of the plugin or application?

Database Isolation Level: Microsoft recommends an isolation level of "Read Committed with Row Versioning" for CRM databases that operate under a high level of concurrency. Here is a related article.

Upgrade Hardware More hardware power never hurts. Also having your SQL server and CRM application on separate machines is recommended, as is having your database log file on its own physical hard drive.

Upvotes: 1

Related Questions