Troubleshooting: Microsoft SQL Server Deadlocks Occur During IGEL UMS 12.13 Server Startup

Problem

In a Distributed UMS or High Availability environment, the IGEL Universal Management Suite (UMS) may become slow or unresponsive during server startup. Devices may also be unable to connect to the UMS.

The Microsoft SQL Server logs show database deadlocks involving the TC_CONNECTION_STATE table. The problem can be more noticeable when multiple UMS Servers start simultaneously or many devices connect during startup.

Environment

The issue occurs under the following conditions:

UMS version 12.13.100, 12.13.110, 12.13.120
Distributed UMS or High Availability environment
Microsoft SQL Server database

Cause

The TC_CONNECTION_STATE table does not have the required primary key. During UMS Server startup, device information is indexed. In a multi-server environment, concurrent database operations during this process can cause deadlocks. The missing clustered primary key can increase the duration of the indexing process and the likelihood of deadlocks.

Solution

Solution 1: Update to Higher UMS Version

Update to IGEL UMS 12.14.100 or later. The update automatically adds the missing primary keys.

Solution 2: Manually Add the Primary Key to `TC_CONNECTION_STATE`

The following procedure modifies the UMS database schema. Create a current database backup and schedule a maintenance window before you continue. Have a qualified database administrator perform or supervise the procedure.

The SQL statements must be executed by the database user used by the UMS. Creating the clustered primary key can take some time in environments with many devices.

Execute the following statement in Microsoft SQL Server:

ALTER TABLE [TC_CONNECTION_STATE] ADD PRIMARY KEY CLUSTERED
(
    [UNIT_ID] ASC,
    [TYPE] ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY]
GO

Optional: Add Primary Key to `TC_CLIENT_CERTIFICATE`

The TC_CLIENT_CERTIFICATE table is also missing a primary key. This table is not critical to the deadlock problem, so this step is optional.

ALTER TABLE [TC_CLIENT_CERTIFICATE] ADD PRIMARY KEY CLUSTERED
(
    [UNIT_ID] ASC,
    [TYPE] ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
) ON [PRIMARY]
GO

Verify the Solution

Start the UMS Servers.
Verify that the UMS interface remains responsive.
Verify that devices can connect to the UMS.
Monitor the Microsoft SQL Server logs during startup.

Deadlocks involving update operations on TC_CONNECTION_STATE should be significantly reduced.