We have a large database (30G) with another (20G).
We are encountering following error when Users have used either Advanced find or Views to search:
Transaction (Process ID 77) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
We have also seen:
Data Access> JDBC error 8645: [ASHB012Q02PR\OVO]A time out occurred while waiting for memory resources to execute the query. Rerun the query., SQL state: 01000 for query.
Above error occured due to following query via OVSD Client:
SELECT T1.per_searchcode AS C0 ,T1.per_name AS C1 ,T1.per_firstname AS C2 ,T1.per_lastname AS C3 ,T1.per_email AS C4 ,T1.per_loc_oid AS C5 ,T2.org_searchcode AS C6 ,T1.per_sta_oid AS C7 ,T2.org_name1 AS C8 ,T1.per_oid AS C9 ,T3.rcd_ordering AS C10 ,T1.per_lockseq AS C11 FROM (( itsm_persons T1 LEFT OUTER JOIN rep_codes T3 ON (T3.rcd_oid = T1.per_sta_oid)) LEFT OUTER JOIN itsm_organizations T2 ON (T2.org_oid = T1.per_org_oid)) WHERE T1.per_searchcode LIKE ? Values: LINDOMAR.S%com.inet.tds.ao: Msg 8645, Level 17, State 1, Line 1, Sqlstate 01000
Have other folks experienced this?
I have archived the records to shrink the database further, but the issue remains.
Issue 1: We had 64K fields enabled for "Information Log" (used as a diary field) for Change, Service Call, Incident, and Problem. When a user queried this field via either a View or Advanced Find, the database deadlocked. This is a known error as conveyed by HP. SP27 allows OVSD Admin to disable the User's ability to query this field. But this is too restrictive. I copied the data in the existing 64K field in a new 4K field and disabled the 64K field in Change, SC, INC, PR modules
Issue 2: Microsoft and HP also suggested to use: The Option is to use New Read COMMITTED ISOLATION LEVEL which is introduced in SQL 2005.
READ COMMITTED SNAPSHOT ISOLATION LEVEL.
In order to enable Read committed isolation level we need to use the following command
Stop all the web servers Make sure there no users connecting to the ServiceDesk application using Activity Monitor alter database servicedesk set single_user; alter database servicedesk set read_committed_snapshot on; alter database servicedesk set multi_user; Start all the web servers
In case you want to revert the behavior you can use the following command Stop all the web servers Make sure there no users connecting to the ServiceDesk application using Activity Monitor alter database servicedesk set single_user; alter database servicedesk set read_committed_snapshot off; alter database servicedesk set multi_user; Start all the web servers
CONCLUSION: Above works. SQL2005 Database no longer experiences deadlocks.