Event ID 99 and Event ID 160 entries occur every minute referencing FK_tblRuleEngineTask_CaseId with error code 0x80131904
Article ID: 100006118
Updated On:
Description
Error Message
Log Name: Veritas Enterprise Vault
Source: Accelerator Service Processor
Event ID: 99
Task Category: None
Level: Error
Description:
APP AT - Customer ID: X - An error occured deleting folders. System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_tblRuleEngineTask_CaseId". The conflict occurred in database "
The statement has been terminated.
0 Folders set to status 24 (deleted) are now deleted
No Case to delete
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlCommand.ExecuteNonQuery()
at KVS.Accelerator.Case.Case.DeleteCases()
ClientConnectionId:. . .
Error Number:547,State:0,Class:16
V-437-99
Log Name: Veritas Enterprise Vault
Source: Accelerator Service Processor
Event ID: 160
Task Category: None
Level: Error
APP AT - Customer ID: X - No delegate! System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_tblRuleEngineTask_CaseId". The conflict occurred in database "
The statement has been terminated.
0 Folders set to status 24 (deleted) are now deleted
No Case to delete
at KVS.Accelerator.Case.Case.DeleteCases()
at KVS.Accelerator.Common.ScheduledThread.WorkerThread()
ClientConnectionId:. . .
Error Number:547,State:0,Class:16
V-437-160
Additional errors may include:
Log Name: Veritas Enterprise Vault
Source: Accelerator Service Processor
Event ID: 99
Task Category: None
Level: Error
Description:
APP AT - Customer ID: X - An error occured deleting folders. System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_tblRules_CaseId". The conflict occurred in database "
The statement has been terminated.
0 Folders set to status 24 (deleted) are now deleted
No Case to delete
V-437-99
Log Name: Veritas Enterprise Vault
Source: Accelerator Service Processor
Event ID: 160
Task Category: None
Level: Error
Description:
APP AT - Customer ID: X - No delegate! System.Data.SqlClient.SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_tblRules_CaseId". The conflict occurred in database "
The statement has been terminated.
0 Folders set to status 24 (deleted) are now deleted
No Case to delete
at KVS.Accelerator.Case.Case.DeleteCases()
V-437-160
Cause
A REFERENCE constraint refers to a foreign key in a database's table that is implemented to control the addition, update and removal of data in the table. A foreign key is typically a column in a child table that refers to the same column in a parent or referenced table, usually the parent or referenced table's primary key. A foreign key constraint in the child table is used to prevent actions that might adversely affect data operations between the parent and child tables, such as preventing any rows from the parent table from being deleted until the corresponding rows from the child's table have been deleted first. This is part of the Consistency component of SQL concurrency (Atomicity, Consistency, Isolation, Durability), which states that a database should not be left in an inconsistent state after a transaction completes and the data on which the transaction is applied must be logically correct.
The SQL error The DELETE statement conflicted with the REFERENCE constraint "FK_ indicates a row is attempting to be deleted from a parent table where the column value from the parent table still exists in the child table. In this case, the child table is tblRuleEngineTask and the foreign key constraint is on column CaseID in parent table tblCase. The error indicates the entry in tblCase that corresponds to the deleted Research Folder via its CaseID is attempting to be deleted from the table, but the deletion attempt fails as the Research Folder's CaseID still exists in tblRuleEngineTask.
Background tasks run periodically to determine if there are any Research folders that have been deleted by their owners more than 30 days ago and to delete such Research Folder entries from the database. Under rare circumstances, the deletion process can fail if the Research Folders' corresponding entries are not deleted in the proper sequence from the DA Customer database tables, thus resulting in the errors above. The cause for these circumstances is not yet known; however, one or more of the following actions are believed to play a role:
- Deleting the Research Folder without first disabling Analytics.
- TCP Offload Engine (TOE) or one of its variants affecting data processing on the DA and/or SQL server.
- Networking issues disrupting data/packet flow between the DA and SQL servers.
- Log shipping issues on the SQL servers when log shipping or mirroring are in use.
A DTrace can be run to gather more data on the background processes logging the events:
1. Prepare a DTrace as follows:
- Log on to the Accelerator Server as the Vault Service Account.
- Open a Command Prompt and navigate to the EV installation folder.
- Type: DTrace 1000000, then press the Enter key.
- At the DT> prompt, type: v, then press the Enter key.
- Note the ID number beside the process(es) to be traced. In this case, the AcceleratorService and AnalyticsServerApp processes.
- At the DT> prompt, type: set
- Repeat the set command for the additional process required.
- Enable DTrace logging by typing at the DT> prompt log c:\dtrace.log but replacing c:\dtrace.log with an existing location and new filename (the log must have a .log extension) for the log, but do not press the Enter key yet.
2. Stop the EV Accelerator Manager Service (EVAMS) on the Accelerator Server, wait until all Accelerator processes have been removed from Task Manager on the Accelerator server, and only then start logging. Be sure the next steps can be run immediately as the DTrace will capture large amounts of data - a delay between this and the next steps will result in un-needed data being captured which will un-necessarily increase the log file size and capture extraneous information which will have to be parsed through during review.
- Stop the Enterprise Vault Accelerator Manager Service on the Accelerator Server and wait for the Service to stop and all Accelerator processes have been removed from Task Manager on the Accelerator server.
- In the DTrace window, press Enter at the DT> log.c:\dtrace.log line. Leave the command line window open. The DTrace is now enabled and will start logging to the specified file. Let DTrace run as needed to capture the issue.
- Start the EVAMS.
3. Monitor the Event Logs on the DA server until the Event ID 99 and Event ID 160 entries are seen. Wait 1-2 minutes after these events are seen before continuing with the next step.
4. Once the errors have been generated, stop the DTrace:
- Go to the DTrace command line window.
- At the DT> prompt, type: log and press the Enter key twice to stop the logging.
- At the DT> prompt, type: exit and press the Enter key to exit DTrace.
5. Upload the following for analysis:
- DTrace log file in Zipped format.
- EV Event Log from the Accelerator Server in Zipped format.
Resolution
Please contact Technical Support for assistance in alleviating these errors.
The following preparatory steps would assist Technical Support in reviewing the issue and preparing for the remediation steps:
1. Run SQL backups of the DA Configuration and Customer databases.
2. To determine the CaseIDs causing the issue, execute the following queries against the DA Customer database and upload the results.
How to execute the queries
1. Use SQL Server Management Studio to connect to the Database Engine that services the DA databases and the EV Vault Store databases.
2. Execute the following queries one at a time and against the applicable databases as indicated.
3. Copy the query output to a spreadsheet for easier analysis:
3.1. Execute each query one at a time against the applicable database as indicated.
3.2. Right-click in the results of each output section.
3.3. Select All.
3.4. Right-click in the results again.
3.4. Copy With Headers.
3.6. Paste into a new spreadsheet.
3.7. Label each sheet with the DA Customer database name or Vault Store database name and the query number as indicated below.
Note: The DA databases may be on a different SQL server\instance than the EV databases.
Queries:
1. List all deleted Analytics-enabled CaseIDs:
SELECT
[Script] = '1a - Deleted CaseIDs'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL)
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
SELECT
[Script] = '1b - Analytics Deleted CaseIDs'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL) AND tc.LastAnalyticsStatusID != tc.AnalyticsStatusID
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
SELECT
[Script] = '1c - Deleted Analytics-enabled CaseIDs'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL)
AND (tc.LastAnalyticsStatusID = 855 /*Disabling Analytics*/ AND tc.AnalyticsStatusID = 856 /*Analytics Disabled*/)
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
SELECT
[Script] = '1d - Probable incorrectly deleted Analytics-enabled CaseIDs'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL)
AND (tc.LastAnalyticsStatusID != 855 /*Disabling Analytics*/ OR tc.AnalyticsStatusID != 856 /*Analytics Disabled*/)
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
SELECT
[Script] = '1e - Incorrectly deleted Analytics-enabled CaseIDs'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL)
AND (tc.LastAnalyticsStatusID != 855 /*Disabling Analytics*/ AND tc.AnalyticsStatusID != 856 /*Analytics Disabled*/)
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
2. Output of tblRuleEngineTask:
SELECT
[Script] = '2 - tblRuleEngineTask'
, *
FROM tblRuleEngineTask
ORDER BY CaseID;
3. List deleted Analytics-enabled CaseIDs that exist in tblRuleEngineTask:
SELECT
[Script] = '3 - tblRuleEngineTask deleted Analytics-enabled CaseIDs'
, *
FROM tblRuleEngineTask
WHERE CaseID IN (
SELECT CaseID FROM tblCase
WHERE (Type = 101 AND (MarkedForDeletion = 1 OR DeletedByPrincipalID IS NOT NULL))
AND LastAnalyticsStatusID != AnalyticsStatusID)
ORDER BY CaseID;
4. List deleted Analytics-enabled CaseIDs that exist in tblRules and tblIntRuleActions:
SELECT
[Script] = '4a - tblRules deleted Analytics-enabled CaseIDs'
, tr.*
, [RuleType] = ts.name
FROM tblRules AS tr
JOIN tblStatus AS ts ON tr.RuleType = ts.StatusID
WHERE tr.CaseID IN (
SELECT CaseID FROM tblCase
WHERE (Type = 101 AND (MarkedForDeletion = 1 OR DeletedByPrincipalID IS NOT NULL))
AND LastAnalyticsStatusID != AnalyticsStatusID)
ORDER BY tr.CaseID, tr.ID;
SELECT
[Script] = '4b - tblIntRuleActions from deleted Analytics-enabled CaseIDs'
, tira.*
, [Action] = ts.Name
, [Mark] = tm.Name
, [Mark_Description] = tm.Description
, [MarkGroup_Name] = tmg.Name
, [MarkGroup_Description] = tmg.Description
, [MarkGroup_Type] = ts1.Name
, [MarkGroup+Behaviour] = ts2.Name
FROM tblIntRuleActions AS tira
JOIN tblStatus AS ts ON tira.ActionId = ts.StatusID
JOIN tblMarking AS tm ON tira.ActionTargetId = tm.MarkingID
JOIN tblMarkGroup AS tmg ON tira.MarkGroupId = tmg.MarkGroupID
JOIN tblStatus AS ts1 ON tmg.TypeID = ts1.StatusID
JOIN tblStatus AS ts2 ON tmg.BehaviorID = ts2.StatusID
WHERE tira.RuleId IN (
SELECT RuleID
FROM tblRules
WHERE CaseID IN (
SELECT CaseID FROM tblCase
WHERE (Type = 101 AND (MarkedForDeletion = 1 OR DeletedByPrincipalID IS NOT NULL))
AND LastAnalyticsStatusID != AnalyticsStatusID))
ORDER BY tira.RuleId;
5. List CaseIDs in tblRuleEngineTask that are not in tblCase (should return no rows):
SELECT
[Script] = '5 - tblRuleEngineTask CaseIDs not in tblCase'
, *
FROM tblRuleEngineTask
WHERE CaseID NOT IN (
SELECT CaseID FROM tblCase)
ORDER BY CaseID;
6. List the CaseIDs that meet the conditions for deletion:
SELECT
[Script] = '6 - CaseIDs for usp_Case_DoDel'
, tc.CaseID, tc.ParentCaseID, tc.Name
, tc.CreateDate, tc.ModifiedDate, DATEDIFF(day, ModifiedDate, GetUtcDate()) AS [RetentionPeriod]
, tc.Type, tc.FolderType, tc.StatusID, tc.OwnerPrincipalID, tc.MarkedForDeletion, tc.DeletedByPrincipalID
, tc.LastAnalyticsStatusID, ts1.Name AS [LastAnalyticsStatus], tc.AnalyticsStatusID, ts2.Name AS [AnalyticsStatus], tc.AnalyticsStatusText
, tc.LegalHoldState, tc.LegalHoldStatus
FROM tblCase AS tc (NOLOCK)
JOIN tblStatus AS ts1 ON tc.LastAnalyticsStatusID = ts1.StatusID
JOIN tblStatus AS ts2 ON tc.AnalyticsStatusID = ts2.StatusID
WHERE (tc.Type = 101 AND (tc.MarkedForDeletion = 1 OR tc.DeletedByPrincipalID IS NOT NULL))
AND tc.LastAnalyticsStatusID != tc.AnalyticsStatusID
AND DATEDIFF(day, tc.ModifiedDate, GetUtcDate()) >= 30
ORDER BY [RetentionPeriod] DESC, tc.CaseID;
Issue/Introduction
