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

by

Why This Error Happens

Error 609 occurs when SQL Server’s storage engine detects that a BTree structure associated with a RowsetBulk operation still contains data when it expects to be empty during a wake-up/resume phase of a bulk insert or index rebuild transaction. This typically stems from corrupted database pages, an interrupted bulk-load operation (such as a crashed BCP or SSIS job), or inconsistent internal metadata caused by improper shutdowns. In some cases, outdated storage drivers or antivirus software locking I/O operations on the data files can also trigger this internal consistency check failure.

Step-by-Step Resolution Guide

Step 1: Verify Database Consistency

  • Open SQL Server Management Studio (SSMS) and connect to the affected instance.
  • Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORS; to scan for corruption.
  • Review the output for allocation or B-tree page errors tied to the RowsetBulk operation.

Step 2: Terminate and Roll Back the Offending Transaction

  • Identify the session ID using sp_who2 or query sys.dm_exec_requests for long-running bulk transactions.
  • Execute KILL <SPID> on the stalled bulk insert/rebuild session.
  • Confirm rollback completion by checking sys.dm_tran_active_transactions.

Step 3: Rebuild Affected Indexes

  • Navigate to the specific table flagged in the error log (found in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
  • Run ALTER INDEX ALL ON [SchemaName].[TableName] REBUILD; to force regeneration of the B-tree structure.
  • Ensure sufficient tempdb space is allocated on Windows Server 2025 before executing.

Step 4: Repair Data Files if Corruption Persists

  • If DBCC CHECKDB reports irrecoverable errors, run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS) in single-user mode as a last resort.
  • Restore from the most recent verified backup if repair is unsuccessful.

Alternative or Verification Steps

  • Re-run the original bulk insert or index operation and monitor the SQL Server Error Log for recurrence of Error 609.
  • If the issue persists, update the storage/RAID controller drivers on Windows Server 2025 and disable any antivirus real-time scanning on the .mdf/.ldf file paths, then retry the operation.