How to fix Error 3923 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: Error 3923 — Transaction Marks Cannot Be Used with Unbacked Bulk-Logged Operations

Why This Error Happens

This error occurs when a named transaction mark (via WITH MARK) is used inside a transaction that also performs minimally logged (bulk-logged) operations, such as BULK INSERT, SELECT INTO, or index rebuilds, while the database is in BULK_LOGGED recovery model. SQL Server cannot guarantee point-in-time recoverability for marked transactions when bulk-logged extents exist in the transaction log that haven’t yet been captured by a log backup. As a safety mechanism, SQL Server ignores the mark rather than risk an unrecoverable restore sequence, since marked recovery relies on precise log sequence number (LSN) tracking that bulk-logged operations disrupt.

Step-by-Step Resolution Guide

Step 1: Identify the Database Recovery Model

  • Open SQL Server Management Studio (SSMS) and connect to your Microsoft SQL Server 2025 instance.
  • Run the following query to confirm the current recovery model:
  • SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';
  • If the result shows BULK_LOGGED, this confirms the root cause of Error 3923.

Step 2: Take a Transaction Log Backup Before Using Marks

  • Ensure any prior bulk-logged operations are backed up before attempting a marked transaction.
  • Run: BACKUP LOG [YourDatabaseName] TO DISK = 'E:\SQLBackups\YourDatabaseName_Log.trn';
  • This clears the bulk-logged extents from the active log, allowing transaction marks to be honored on subsequent operations.

Step 3: Switch to FULL Recovery Model for Mark-Dependent Workloads

  • If your application relies on WITH MARK for point-in-time restores, switch the database out of BULK_LOGGED mode.
  • Run: ALTER DATABASE [YourDatabaseName] SET RECOVERY FULL;
  • Re-run a full backup afterward to reset the log chain: BACKUP DATABASE [YourDatabaseName] TO DISK = 'E:\SQLBackups\YourDatabaseName_Full.bak';

Step 4: Separate Bulk Operations from Marked Transactions

  • Avoid combining BULK INSERT, SELECT INTO, or CREATE INDEX with BEGIN TRANSACTION ... WITH MARK in the same batch.
  • Schedule bulk-logged maintenance tasks (via SQL Server Agent jobs) outside the window where application-level transaction marks are issued.
  • On Windows Server 2025, verify SQL Server Agent service (SQLSERVERAGENT) is running under Services (services.msc) to confirm scheduled jobs execute as expected.

Step 5: Validate Log Backup Chain Integrity

  • Use RESTORE HEADERONLY against recent .trn files to confirm no gaps exist in the LSN chain.
  • RESTORE HEADERONLY FROM DISK = 'E:\SQLBackups\YourDatabaseName_Log.trn';
  • Confirm FirstLSN and LastLSN values are contiguous across your backup history.

Alternative or Verification Steps

  • After applying fixes, re-run the marked transaction and check the SQL Server Error Log (via EXEC sp_readerrorlog or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm Error 3923 no longer appears.
  • If switching to FULL recovery isn’t feasible for performance reasons, consider using BACKUP LOG immediately after each bulk-logged operation to keep the log chain current and allow marks to succeed on subsequent transactions.