Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
francism
Starting Member
22 Posts |
 Posted - 2009-03-08 : 19:55:34
|
| I think I'm way over my head with this problem.I have a gigantic view that reads several tables and views in MS CRM. I sometimes get the following error:SQLCODE IS 1205 (HEX: 000004B5) XOPEN: 40001 Microsoft OLE DB Provider for SQL Server: [40001] Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.I wrongly assumed that having WITH (nolock) would resolve the deadlock.I've read [url]http://support.microsoft.com/kb/169960[/url] and I'm not sure how this applies to a view that is used for retrieval only, though I do think I understand that a table is locked while getting updated - I thought the read process would just wait for the update to complete.I would appreciate any ideas. I am not the MS CRM database administrator so I can't start SQl Server with trace options, I'm hoping to solve the problem by correcting the view if possible.Thank you.FrancisMicrosoft CRM 3 - SQL Server 2000 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-09 : 09:40:29
|
| If it's a CRM view, remember that if you touch it it's a non supported fix. When you figure out what is dead locking I would encourage you to look at using Plan Guides (2005 and above) they may provide you some relief since you can't change much of what CRM does from the front end or in the database. I've always found CRM's database to be questionable at best. Take a look at the indexes they create by default. They're crazy.Mike"oh, that monkey is going to pay" |
 |
|
|
francism
Starting Member
22 Posts |
Posted - 2009-03-09 : 10:11:35
|
| I created the view based on existing CRM views, I didn't modify any of the supplied views. It's a crazy view of a crazy db. It works quite well most of the time. It reads Activity data, which is constantly being updated by users and by background tasks.I will have to verify if my id has permission to run DBCC TRACEON.Does "WITH (nolock)" actually have any impact at all?Thanks.Microsoft CRM 3 - SQL Server 2000 |
|
|
|
francism
Starting Member
22 Posts |
Posted - 2009-03-09 : 11:17:53
|
| This Hotfix from Microsoft appears to be related to the problem I'm having:[url]http://support.microsoft.com/kb/940496[/url]Microsoft CRM 3 - SQL Server 2000 |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-03-09 : 16:00:52
|
| Just don't be running the Chinese or Japanese versions....WTF is up with that? They don't experience the same issues because of language??Terry-- Procrastinate now! |
|
|
|
|
|
|
Deadlock with a view used for retrieval only