Fix STATUS_TRANSACTION_FREEZE_IN_PROGRESS (0XC0190046) in Minutes

The 30-Second Fix: Check if a Freeze is Already Holding

This error pops up when a transaction freeze is already running and a second freeze request collides with it. It's like trying to lock a door that's already locked — SQL Server just says "no."

First, run this query in SQL Server Management Studio:

SELECT session_id, request_id, start_time, status, command, blocking_session_id
FROM sys.dm_exec_requests
WHERE command LIKE '%FREEZE%' OR command LIKE '%THAW%';

If you see a row with a status of running or suspended, that's your freeze process. Had a client last month whose nightly backup script triggered a freeze twice because of a cron misconfiguration. This query found it instantly.

If the session is stuck for more than a minute, kill it. Use the session_id from the query above:

KILL <session_id>;

That's it. No restart needed. But if this doesn't resolve the error, move to the moderate fix.

The 5-Minute Fix: Identify the Blocking Freeze Transaction

Sometimes the freeze process itself is waiting on another transaction. You need to find the root blocker.

Run this:

SELECT
    t.session_id,
    t.transaction_id,
    t.name,
    t.transaction_begin_time,
    CASE t.transaction_type
        WHEN 1 THEN 'Read/write'
        WHEN 2 THEN 'Read-only'
        WHEN 3 THEN 'System'
        WHEN 4 THEN 'Distributed'
    END AS transaction_type,
    CASE t.transaction_state
        WHEN 0 THEN 'Initializing'
        WHEN 1 THEN 'Active'
        WHEN 2 THEN 'Active (prepared)'
        WHEN 3 THEN 'Prepared'
        WHEN 4 THEN 'Committed'
        WHEN 5 THEN 'Rolling back'
        WHEN 6 THEN 'Rolled back'
        ELSE 'Unknown'
    END AS transaction_state
FROM sys.dm_tran_active_transactions t
INNER JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
WHERE t.transaction_state IN (1, 2, 5) -- active or rolling back
ORDER BY t.transaction_begin_time;

Look for transactions that have been running longer than 10 minutes. Those are your suspects. If you see a transaction in rolling back state that's been there for 30+ minutes, that's your problem. SQL Server takes forever to roll back large transactions.

I once had a freeze get stuck because a long-running SELECT INTO was holding locks. The freeze waited for it, then timed out. Killed the SELECT INTO session, and the freeze cleared in seconds.

If you find a blocking session, note its session_id and check what it's doing:

SELECT
    blocking_session_id,
    wait_type,
    wait_time,
    wait_resource,
    last_wait_type
FROM sys.dm_exec_requests
WHERE session_id = <blocking_session_id>;

Then kill it:

KILL <blocking_session_id>;

After killing the blocker, the freeze process should complete on its own. Re-run the first query to confirm the freeze is gone.

The 15+ Minute Fix: Deep Clean the Transaction Log

If the quick kills don't work, the freeze might be stuck due to a corrupted or bloated transaction log. This happens when a DBCC FREEZECACHE or DBCC THAW command hits a log that's full or has internal errors.

Start by checking the log status:

DBCC OPENTRAN;

If you see an oldest active transaction, that's your smoking gun. It's holding the freeze hostage. You can force it to commit or roll back:

-- If you know the SPID from DBCC OPENTRAN output
KILL <spid> WITH STATUSONLY;

If the transaction won't die, you need to take the database offline and back online. This forces a rollback of all open transactions. Do this during a maintenance window:

ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE [YourDatabaseName] SET ONLINE;
GO

This worked for a client whose freeze got stuck after a power outage. The log had a phantom transaction that wouldn't clear.

If the database won't come offline because the freeze is still active, you may need to restart the SQL Server service. Yes, it's drastic, but sometimes it's the only way:

-- From command prompt (run as admin)
net stop MSSQLSERVER
net start MSSQLSERVER

After restart, immediately run the first query to verify the freeze is gone. Then check the log size:

DBCC SQLPERF(LOGSPACE);

If the log is over 90% used, shrink it:

DBCC SHRINKFILE (YourDatabaseName_Log, 100);

But don't make a habit of shrinking logs — it fragments them. The real fix is to set the log file to a reasonable size and schedule regular log backups.

Pro tip: Once you're back up, configure MAXDOP and cost threshold for parallelism to prevent long-running queries that trigger freeze collisions. Set MAXDOP to 4 for OLTP workloads, and keep cost threshold at 50 or higher.

If none of this works, you're dealing with a corruption issue. Run DBCC CHECKDB on the database to find corrupt pages. That's a whole different article, but start there.