Unsupported SQL Server Features and Troubleshooting#

Critical Manufacturing MES clean and upgrade installations require exclusive access to the database schema. Features that maintain background connections or hold metadata locks cause lock contention, transaction log exhaustion, or installation failure.

All checks and operations must be performed against each of the three Critical Manufacturing MES databases:

Table: SQL Server databases used by Critical Manufacturing MES

Pre-Installation Checklist#

Before initiating any installation, run the following script against each database to confirm CDC and SQL Server Audit are disabled and that no active session can block the process:

DECLARE @SystemName NVARCHAR(128) = 'YOUR_SYSTEM_NAME';

-- 1. Check CDC status (must be 0 on every database)
SELECT name AS DatabaseName, is_cdc_enabled
FROM sys.databases
WHERE name IN (@SystemName + 'Online', @SystemName + 'ODS', @SystemName + 'DWH');

-- 2. Check for active Audit Specifications (must return no rows; run per database)
SELECT name AS AuditSpecName, is_state_enabled
FROM sys.database_audit_specifications
WHERE is_state_enabled = 1;

-- 3. Check for active connections
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    DB_NAME(s.database_id)          AS connected_database,
    'KILL ' + CAST(s.session_id AS NVARCHAR(10)) + ';' AS kill_command
FROM sys.dm_exec_sessions s
WHERE s.database_id IN (
    DB_ID(@SystemName + 'Online'),
    DB_ID(@SystemName + 'ODS'),
    DB_ID(@SystemName + 'DWH')
)
AND s.session_id <> @@SPID
AND s.is_user_process = 1;

If the checklist reports CDC as enabled (is_cdc_enabled = 1), the client is running an unsupported configuration. CDC is not supported on Critical Manufacturing MES databases and is enabled at the client's own risk. Involve the client's DBA to fully disable it before the installation proceeds.

If the checklist returns any active Audit Specification, SQL Server Audit must be disabled before the installation proceeds. Involve the client's DBA to disable the active specifications for the duration of the installation.

Mitigating Active Connections#

Installation scripts execute DDL operations (ALTER USER, ALTER TABLE, DROP) that require exclusive schema locks. If a session holds a conflicting lock, the installation script waits indefinitely. Common sources are application services with pooled connections and SQL Server Agent jobs, particularly the scheduled sync jobs between Online, ODS, and DWH.

Before the Installation#

1. Disable Critical Manufacturing MES Database Jobs on the instance so automated sync jobs cannot acquire background locks.

2. Terminate active sessions. Use the connection output from the Pre-Installation Checklist to identify and close any remaining application or user connections.

During the Installation#

If the installation stalls on a DDL step, identify the blocking session:

SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_resource
FROM sys.dm_exec_requests r
WHERE r.blocking_session_id > 0;

If the session is a stale or orphaned connection, use KILL {session_id} to release the lock. If the wait_resource column contains ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS, the cause is a leftover Audit Specification, not an active session - see Diagnosing Installation Blocking.

After the Installation#

1. Re-enable the SQL Server Agent jobs that were disabled before the installation.

2. Confirm the system is operational.

Diagnosing Installation Blocking#

If the installation stalls with no error in the log, a blocking lock is the likely cause. Use the steps below to identify the root cause.

Run sp_who2 to get a quick session overview. Look for any row where the BlkBy column is non-zero. That row is the blocked session; the value in BlkBy is the session holding the lock.

With the blocked session ID, query sys.dm_exec_requests to get the wait_type and wait_resource:

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_resource,
    r.wait_time / 1000 AS wait_seconds,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.blocking_session_id > 0;

Use the wait_resource value to identify the conflict type:

Table: Wait resource patterns and corresponding conflict type

To identify what the blocking session is running, use sys.dm_exec_sql_text:

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    t.text AS current_sql
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <blocking_session_id>;

Replace <blocking_session_id> with the value from the previous query to confirm whether it is a sync job, a reporting query, or a stale application connection.