SQL Server Installation Conflicts#

During a Critical Manufacturing MES clean or upgrade installation, the installation scripts may require high-performance, exclusive access to the database schema. Certain background features can conflict with this process, leading to lock contention, transaction log exhaustion, or installation failure.

All pre-flight checks, disable, and re-enable operations described below must be performed against each of the three Critical Manufacturing MES databases individually:

Table: SQL Server databases used by Critical Manufacturing MES

Known Conflict Features#

Table: Known SQL Server features that conflict with the installation process

Before initiating any installation, run the following script against each of the three databases to identify active conflict features:

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

-- Check CDC status
SELECT name AS DatabaseName, is_cdc_enabled
FROM sys.databases
WHERE name IN (
    @SystemName + 'Online',
    @SystemName + 'ODS',
    @SystemName + 'DWH'
);

-- Check for active Audit Specifications (run in the context of each database)
SELECT name AS AuditSpecName, is_state_enabled
FROM sys.database_audit_specifications
WHERE is_state_enabled = 1;

Change Data Capture (CDC)#

CDC continuously reads the transaction log to track data changes. During an installation that modifies millions of rows, CDC prevents the transaction log from being truncated until the capture job has processed every change. This causes the log file to grow 2x–5x, potentially filling the disk and crashing the installation.

Disabling CDC is irreversible for the period it is off. SQL Server does not catch up on missed changes. When re-enabled, CDC starts a new capture instance with a fresh Log Sequence Number (LSN). Any external system consuming the CDC stream will have a data gap and must be re-synchronized.

Before the Installation#

1. Notify the teams responsible for any external systems consuming the CDC stream that integrations will have a data gap during the maintenance window.

2. For each CDC-enabled database, query sys.tables, sys.schemas, and cdc.change_tables to generate a sys.sp_cdc_enable_table call for every tracked table. Save this output to a secure file. See: sys.sp_cdc_enable_table (Transact-SQL) ⧉

During the Installation#

1. Run sys.sp_cdc_disable_db in the context of each database where CDC is active. See: sys.sp_cdc_disable_db (Transact-SQL) ⧉

2. Confirm is_cdc_enabled = 0 for each affected database in sys.databases before proceeding.

3. Monitor log space throughout the installation using DBCC SQLPERF(LOGSPACE). See: Manage the Size of the Transaction Log File ⧉

After the Installation#

1. Verify SQL Server Agent is running. CDC requires it for the capture and cleanup jobs. See: SQL Server Agent ⧉

2. Run sys.sp_cdc_enable_db on each database where CDC was previously active. See: sys.sp_cdc_enable_db (Transact-SQL) ⧉

3. Run the sys.sp_cdc_enable_table commands saved before the installation to restore per-table tracking.

4. Run sys.sp_cdc_help_jobs and confirm both the capture and cleanup jobs are active. See: sys.sp_cdc_help_jobs (Transact-SQL) ⧉

5. Notify downstream integration teams that CDC has been re-enabled with a new LSN and that re-synchronization is required.

SQL Server Database Auditing#

SQL Auditing intercepts DDL commands (ALTER, DROP, CREATE). Every schema change must be written to the audit log, adding latency. If the audit is configured with ON_FAILURE = SHUTDOWN, any disk latency or log-writing issue will stop the entire SQL Server instance.

SQL Auditing also causes audit metadata locking. When an active Audit Specification is present, DDL statements such as ALTER USER acquire a metadata lock on ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS. If the audit engine is busy, this lock blocks the DDL statement indefinitely and the installation silently stalls.

Disabling auditing creates a gap in the audit trail. For clients in regulated industries (SOC2, HIPAA, GDPR), this gap may require formal sign-off from a Security Officer.

Before the Installation#

1. Query sys.database_audit_specifications filtering by is_state_enabled = 1 in the context of each database (Online, ODS, DWH). Record which specifications are active per database. See: sys.database_audit_specifications (Transact-SQL) ⧉

2. Check sys.server_audits for any audits with on_failure_desc set to SHUTDOWN SERVER or FAIL OPERATION. These must be disabled before the installation. See: sys.server_audits (Transact-SQL) ⧉

3. Obtain compliance sign-off from the client's security team for the audit gap during the maintenance window.

During the Installation#

1. For each active specification, toggle its state to OFF using ALTER DATABASE AUDIT SPECIFICATION ... WITH (STATE = OFF) in the context of each database. See: ALTER DATABASE AUDIT SPECIFICATION (Transact-SQL) ⧉

After the Installation#

1. Toggle each specification back to ON using ALTER DATABASE AUDIT SPECIFICATION ... WITH (STATE = ON) in the context of each database.

2. Query sys.dm_server_audit_status to confirm the audit engine has resumed. See: sys.dm_server_audit_status (Transact-SQL) ⧉

3. Perform a manual security review on newly created tables, stored procedures, and permissions to fill the gap left by the disabled audit.

Active Database Connections and Session Blocking#

Critical Manufacturing MES installation scripts execute DDL operations (such as ALTER USER, ALTER TABLE, DROP) that require exclusive schema locks. If any session holds a conflicting lock, the installation script will wait indefinitely. Common sources of blocking sessions include 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 all SQL Server Agent jobs on the instance. See: Disable or Enable a Job ⧉

2. Run the session and lock pre-flight check below. If any sessions are returned, investigate the origin and determine if they should be terminated before starting the installer.

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

-- Check for active user sessions on the Critical Manufacturing MES databases
SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    s.last_request_start_time,
    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;

During the Installation#

If the installation stalls on a DDL step, query sys.dm_exec_requests filtering by blocking_session_id > 0 to identify the blocking session. If it is a stale or orphaned connection, use KILL {session_id} to release the lock. See: sys.dm_exec_requests (Transact-SQL) ⧉

Not all DDL stalls are caused by active connections. If the wait_resource column contains ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS, the cause is SQL Auditing, not an active session. Refer to the Diagnosing Installation Blocking section to identify the root cause.

After the Installation#

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

2. Confirm the system is operational.

How to: Diagnose 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.

Related Documentation#

Table: Related Microsoft documentation references