Error Message

Examples of errors that may be seen in the Accelerator server Event Logs.

Log Name: Veritas Enterprise Vault
Source: Enterprise Vault
Event ID: 41606
Task Category: None
Level: Information
Description:
A database error was resolved after ...ms and 1 retries.
Error: Error Code: 1205 / 80131904 - Severity 13 - Transaction (Process ID 153) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Reference: EVSqlDataAdapter: Select: EVSqlCommand: DB: server=...;Initial Catalog=...;Integrated Security=true;Connection TimeOut=5000;Connection LifeTime=2000;Max Pool Size=500;Application Name=... Accelerator - Command: [usp_VaultSearches_sel] | Update: EVSqlCommand: DB: server=...;Initial Catalog=...;Integrated Security=true;Connection TimeOut=5000;Connection LifeTime=2000;Max Pool Size=500;Application Name=... Accelerator - Command: [usp_VaultSearch_AddToQueue] | Delete: Insert:

V-437-41606

Log Name: Veritas Enterprise Vault
Source: Enterprise Vault
Event ID: 41619
Task Category: None
Level: Information
Description:
A transient error is preventing the execution of a SQL command. Enterprise Vault will try to run the command again.
Command: 'EVSqlDataAdapter: Select: EVSqlCommand: DB: server=...;Initial Catalog=...;Integrated Security=true;Connection TimeOut=5000;Connection LifeTime=2000;Max Pool Size=500;Application Name=... Accelerator - Command: [usp_VaultSearches_sel] | Update: EVSqlCommand: DB: server=...;Initial Catalog=...;Integrated Security=true;Connection TimeOut=5000;Connection LifeTime=2000;Max Pool Size=500;Application Name=... Accelerator - Command: [usp_VaultSearch_AddToQueue] | Delete: Insert: '
Attempt: 1 of 40.
Last Error: Transaction (Process ID 233) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

V-437-41619

Log Name: Veritas Enterprise Vault
Source: Accelerator Service Processor
Event ID: 100
Task Category: None
Level: Error
Description:
APP AT - Customer ID: 12 - An error has occured when initializing the Cases. System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at System.Collections.Generic.List`1.set_Capacity(Int32 value)
at System.Collections.Generic.List`1.EnsureCapacity(Int32 min)
at System.Collections.Generic.List`1.Add(T item)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase..ctor()
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlConnection..ctor(SqlConnection connection)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlCommand..ctor(SqlCommand cmd)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlDataAdapter.get_SelectCommand()
at KVS.Accelerator.Case.Case.GetDS(Int32 CaseID, CaseType aCaseType, String StatusID, Boolean GetVaultStored, Boolean GetSecurity, String PrincipalLogin, Boolean GetEmailAddress, Boolean Gethistory, Boolean SearchableCase, Boolean CanbeADeletedCase, Boolean GetCaseLearning)

V-437-100

The active SQL processes can be viewed by executing the following query on the SQL server servicing the Customer database:

USE master;
GO
sp_who2;

The output may show one or more stored procedures associated with the Export process are blocking other stored procedures from executing. The wait type for these blocking stored procedures shows as SLEEP_TASK.

The following explanation involves advanced SQL concepts.

SQL SLEEP_TASK waits are typically caused by a task waiting on SQL to complete a process or perform a task to allow the current task to complete. In the majority of occurrences, SLEEP_TASK waits will self clear. In the event they do not self clear, there are 2 prime culprits: TempDB issues or stale statistics.

TempDB

TempDB issues can manifest themselves by not having tempdb configured per Microsoft's recommendations (see see https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver15#optimizing-tempdb-performance-in-sql-server) or by the TempDB database and transaction log file locations being scanned by antivirus (see https://www.veritas.com/support/en_US/article.100007613).

Contention latency may be seen when a large number of processes occur simultaneously, causing multiple concurrent latches to be queued, resulting in latency, and is usually recognised by the presence of PAGELATCH locks. Latches are locks on three types of special pages: Global Allocation Map (GAM), Shared Global Allocation Map (SGAM), and Page Free Space(PFS). A full description of these is out of the scope of this writing. At least one of each of these pages exists in all database files and are responsible for helping to identify where incoming data can be written to in the physical file (s). Updating these pages requires a latch lock, which is a short-duration lock used during the upgrade. The most common latch locks are update (UP) and exclusive (EX).

Stale Statistics

Lack of SQL Maintenance can cause the statistics to become stale. An upgrade can further add to the statistics being stale, especially if there are database schema changes involved. Severely stale statistics can cause cached execution plans to become outdated. The actual execution plans then rely more on parameter sniffing which, if excessive, can lead to hash spills whereby the data being processed is 'spilled' into TempDB if there is inadequate memory to execute the SQL query being processed. This may result in a large amount of space being consumed by TempDB resulting in slow performance.

Viewing hash spills depends on how the query for the process in question is run. There are typically 2 types: queries run in SQL Management Studio or stored procedures run by the application.

For queries run within the SQL Management Studio:

1. Prepare the SQL query to be executed in a new Query window.
2. Go to the Query menu and click on 'Include Actual Execution Plan'.
3. Go to the Query menu and click on 'Display Estimated Execution Plan'. This will execute the query.
4. Review the Execution Plan tab for the plan.

For stored procedures, run a SQL Profiler Trace or an Extended Events Session. To run a SQL Profiler:

1. Start SQL Profiler from Start | Microsoft SQL Server 20XX | Performance Tools | SQL Server Profiler | File | New Trace.
2. Verify Server Type is Database Engine and authentication type is Windows Authentication | click Connect.
3. Enter a trace name.
4. Select 'Blank' under 'Use the template:'.
5. Click the 'Save to file:' option and configure as follows:
5.1. Select a location and a filename (be sure to add the .trc extension to the filename). As the log file can be quite large, please verify the target drive has adequate available free space.
5.2. Select 'Save as type:' of 'SQL Server Profiler trace files (*.trc)'.
5.3. Click Save.
5.4 Set the 'Set maximum file size (MB):' value to 250.
5.5. Leave the 'Enable file rollover' option selected.
6. Click on the 'Events Selection' Tab. Select 'Show all events' and 'Show all columns'. There should not be any events selected - if selected, deselect all existing events in the left column.
7. Select Errors and Warnings: Hash Warning.
8. Click on the Run button to start tracing and then reproduce the issue in order to capture information in the Trace Log.
9. After the issue has been reproduced successfully, click on the Stop Selected Trace button, close the Trace Log window and close SQL Server Profiler.

To run an Extended Events Session:

1. Go to Management | Extended Events.
2. Right-click on Sessions | New Session Wizard.
3. Introduction page: Click Next.
4. Set Session Properties page: Enter a name for the session, then click Next.
5. Choose Template page: Do not use a template, then click Next.
6. Select Events To Capture page: Verify the 'Event library' is set to search in 'Event names only', search for and select all entries for 'hash', search for and select all entries for 'spill', then click Next.
7. Capture Global Fields page: Select the following, then click Next.
client_app_name
collect_cpu_cycle_time
database_name
event_sequence
query_hash
query_hash_signed
query_plan_hash
query_plan_hash_signed
request_id
session_id
sql_text
task_time
transaction_id
transaction_sequence
8. Set Session Event Filters page: Click Next.
9. Specify Session Data Storage page: Select 'Save data to a file for later analysis (event_file_target), enter the file name and location, leave the 'Maximum file size' and 'Enable file rollover' at the default values, set the 'Maximum number of files' to 10, then click Next.
10. Summary page: Review the Session settings and click Finish is correct to run the Session. Can also click on 'Script' to save the Session script prior to clicking on Finish.

Review of the SQL Profiler Trace/Extended Events Session should show hash spills as a yellow warning on SORT operators. Hovering the cursor over the symbol should display more information.

TempDB

The solution is to add more TempDB data files, per article https://support.microsoft.com/en-us/help/2154845/recommendations-to-reduce-allocation-contention-in-sql-server-tempdb-d.

The recommendations are:
1. Start with a number of files equal to half the number of processors but not to exceed eight (8) total data files (1 MDF + 7 NDF).
2. All data files are exactly the same size with the same autogrowth to accommodate SQL's proportional fill algorithm (if one file is larger, SQL will use that file more often, causing it to grow more often, causing it to be chosen more often again).
3. All eight files are on different fast IO drives and not on the TempDB transaction log (LDF) file(s) drive(s).

Additionally, verify the TempDB locations are excluded from antivirus scanning, as listed in https://www.veritas.com/support/en_US/article.100007613.

Stale Statistics

It is recommended to run SQL Maintenance immediately following an upgrade, as these upgrades often create temporary tables that are removed as part of the upgrade completion processing. If hash spills are occurring, the recommendation is to update the statistics. However, it is best to perform SQL Maintenance, per article https://www.veritas.com/support/en_US/article.100022023. In the rare event running SQL Maintenance does not alleviate the hash spills, it may be the execution plan is not a candidate for recompilation post statistics update. If this is the case, a recompile may need to be force executed. Please contact technical support for assistance with forcing a recompile.