June 24, 2019 at 12:49 pm
Hi All,
We are facing a roadblock in production server, hope experts can give some solution here.
we are having a 900GB MS-CRM on premise database with replication in place. In every 6-8 hours interval DB is declining all updates from application, unable to find the exact reason. any updates to any table is giving timeout error. On doing failover system is working again, but after some time facing the same problem.
CPU usage is around 80%, memory is 256 GB usage is around 80%.
I kept a trace on DB for deadlocks but not many locks are coming in that.
what would be the probable reason. From application max time out error is 2 mins, updates from CRM are record wise i.e one record at a time.
clueless to find a resolution, any help will be highly appreciated
June 24, 2019 at 8:17 pm
Instead of deadlocks, this sounds like regular lock contention. Have a look at the output of sp_who2 when this happens, and check the BlkBy (Blocked By) column. If there are no Session_IDs listed in this column, it is something else.
June 24, 2019 at 10:39 pm
Might also look at sp_Blitz. These scripts are sometimes more helpful in debugging things: https://www.brentozar.com/first-aid/
June 24, 2019 at 11:30 pm
Small apart - being a MS CRM db replication is not supported by Microsoft - be careful with that. Having said it I have implemented it myself with no major hassle.
Regarding your server a few questions
When the issue happens did you check the status of replication ? issue could be on this being blocked/slow and be messing up with your tlog. or eventually a snapshot being done which could also have this quite negative impact.
and what are the most active processes / queries when it happens. both within SQL Server and at OS level.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply