operations in SDM are hanging / tickets show up as Locked

Products

CA Service Desk Manager CA Service Management - Service Desk Manager

Issue/Introduction

Customer's users are seen locks on SDM tickets (they are unable to update status of the ticket, due to lock).

Administration-> System-> Current locks, shows a bunch of tickets are locked too

Environment

Release : 17.1 and higher

Component : SERVICE DESK MANAGER

Cause

Analysis of the logs show below errors (entries of interest highlighted):

12/26 02:31:03.44 SDMServerName web:local 9204 SIGNIFICANT session.c 7067 Web Statistics - Cumulative Sessions (362) Most Sessions (46) Current Sessions (44)

12/26 02:31:05.33 SDMServerName sqlagt:select369 832 ERROR sqlclass.c 473 Failed to logon to SQL Server (SQLServerName) Reason: [Microsoft SQL Server Native Client 11.0] [ SQL Code=0 SQL State=HYT00] Login timeout expired; [Microsoft SQL Server Native Client 11.0] [ SQL Code=53 SQL State=08001] A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=53 SQL State=08001] Named Pipes Provider: Could not open a connection to SQL Server [53].

12/26 02:31:05.33 SDMServerName bpvirtdb_srvr 11388 ERROR vdbagent.c 897 Detected database connection problem during SELECT_FULL. Requeuing request for agent select369.

we see database connection got lost, mostly because of a network hiccup or an outage of some sort on SQL Server.

This resulted in several locks on tickets (and other objects too) which continued for a long time

12/26 02:33:32.83 SDMServerName domsrvr 1348 ERROR attr.c 5970 No response to trigger cr.status::check_tasks (2 time) after 60 seconds; waiting another 120 seconds
12/26 02:33:32.83 SDMServerName domsrvr 1348 ERROR attr.c 5970 No response to trigger cr.status::check_tasks (2 time) after 60 seconds; waiting another 120 seconds
12/26 02:33:33.72 SDMServerName domsrvr 1348 ERROR attr.c 5970 No response to trigger cr.status::check_tasks (3 time) after 120 seconds; waiting another 240 seconds

Resolution

Usually when a SQL Server connection outage happens, SDM may need to be recycled to reestablish DB connections. The error from SQL Server CLIENT protocol to SQL Server Server, will generally require involvement with the local network, server and DB admin teams to diagnose further.

Additional Information

Broadcom Support is unable to provide assistance to diagnose or troubleshoot a customer's environment. Service Desk product logging is not a recommended resource to diagnose or troubleshoot network or database reliability. Turning up logging on Service Desk is not an effective method to obtain further information regarding any such database or connectivity issues; this is because the product uses the native OS and database resources to establish connections and interact with the database.

In some cases, a root cause for the loss was due to network snapshots, replication, or other activity which was occupying the database resources.