---
alias: kb-sql-server-installation-conflicts
description: "Identify and mitigate SQL Server features that can conflict with Critical Manufacturing MES clean or upgrade installation"
---

# 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:

| Database | Role |
| :--- | :--- |
| **{SystemName}Online** | Transactional database. Primary source of transactional data changes. |
| **{SystemName}ODS** | Operational Data Store. Aggregates and processes data from the Online database. |
| **{SystemName}DWH** | Analytical database. Downstream consumer of ODS; used for reporting and BI. |

Table: SQL Server databases used by Critical Manufacturing MES

## Known Conflict Features

| Feature | Primary Risk | Restoration Difficulty | Who to Involve |
| :--- | :--- | :--- | :--- |
| [**CDC**](#change-data-capture-cdc) | Disk space exhaustion / Performance degradation | **High** - requires metadata backup and downstream integration re-sync | DBA, teams owning any CDC-dependent integrations |
| [**SQL Auditing**](#sql-server-database-auditing) | Installation crashes / DDL bottlenecks / audit metadata lock blocking | **Low** - simple state toggle | Security Officer, DBA |
| [**Active Connections**](#active-database-connections-and-session-blocking) | DDL lock contention / installation scripts blocked indefinitely | **Low** - identify and close sessions | DBA |

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:

``` sql
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.

1. 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)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/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)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-disable-db-transact-sql)

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

1. Monitor log space throughout the installation using `DBCC SQLPERF(LOGSPACE)`. See: [Manage the Size of the Transaction Log File](https://learn.microsoft.com/en-us/sql/relational-databases/logs/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](https://learn.microsoft.com/en-us/sql/ssms/agent/sql-server-agent)

1. Run `sys.sp_cdc_enable_db` on each database where CDC was previously active. See: [sys.sp_cdc_enable_db (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transact-sql)

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

1. Run `sys.sp_cdc_help_jobs` and confirm both the capture and cleanup jobs are active. See: [sys.sp_cdc_help_jobs (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-help-jobs-transact-sql)

1. 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)](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-audit-specifications-transact-sql)

1. 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)](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-audits-transact-sql)

1. 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)](https://learn.microsoft.com/en-us/sql/t-sql/statements/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.

1. Query `sys.dm_server_audit_status` to confirm the audit engine has resumed. See: [sys.dm_server_audit_status (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-server-audit-status-transact-sql)

1. 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](https://learn.microsoft.com/en-us/sql/ssms/agent/disable-or-enable-a-job)

1. 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.

``` sql
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)](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/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](#how-to-diagnose-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.

1. 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`:

``` sql
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:

| wait_resource pattern | Cause | Conflict Type |
| :--- | :--- | :--- |
| `METADATA: ... ALL_AUDIT_SPECIFICATIONS_AND_ACTIONS` | Active Audit Specification intercepting DDL | **SQL Auditing** - disable Audit Specifications |
| `LCK_M_SCH_M` on a user or table object | Active session holding a schema modification lock | **Active Connections** - identify and kill blocking session |
| `LCK_M_X` / `LCK_M_U` on a table | Active connection with an open transaction | **Active Connections** - identify and kill blocking session |
| Log space / `WRITELOG` | Transaction log full or CDC capture job lagging | **CDC** - check log space and CDC status |

Table: Wait resource patterns and corresponding conflict type

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

``` sql
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

| Topic | Link |
| :--- | :--- |
| About Change Data Capture | [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server) |
| SQL Server Audit (Database Engine) | [learn.microsoft.com](https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine) |

Table: Related Microsoft documentation references