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

by

Troubleshooting Guide: SQL Server Error 10086 – “Table Built Over a Live Data Stream, Position Cannot Be Restarted”

Why This Error Happens

This error typically occurs when SQL Server 2025’s native streaming table engine loses its checkpoint reference to the underlying live data stream source, often due to an interrupted connection or an expired stream offset window. It can also happen if the stream-backed table was created with WITH (STREAMING_SOURCE) options but the upstream provider (such as Event Hubs, Kafka integration, or a Change Data Capture feed) rotated or purged its log segment before SQL Server committed its last read position. Additionally, corrupted metadata in the sys.streaming_table_offsets system view or insufficient permissions on the stream endpoint can prevent the engine from resuming at the last known position.

Step-by-Step Resolution Guide

Step 1: Verify the Streaming Source Connection and Credentials

  • Confirm the service account running SQL Server has active network access to the streaming endpoint (check firewall rules on Windows Server 2025 / Windows 11 for outbound port blocks).
  • Run SELECT * FROM sys.dm_streaming_source_status; to check if the connection state is DISCONNECTED or EXPIRED.
  • Re-validate credentials stored in the DATABASE SCOPED CREDENTIAL used by the streaming table definition.

Step 2: Inspect and Reset the Stream Offset Metadata

  • Query the current checkpoint using SELECT * FROM sys.streaming_table_offsets WHERE table_name = 'YourTableName';
  • If the offset is null or references a purged segment, reset it manually with:
    EXEC sp_reset_streaming_offset 'YourTableName', 'EARLIEST';
  • Use 'EARLIEST' or 'LATEST' depending on whether historical replay or fresh ingestion is required.

Step 3: Rebuild the Streaming Table Definition

  • Drop and recreate the table using DROP TABLE YourTableName; followed by the original CREATE TABLE ... WITH (STREAMING_SOURCE = ...) script.
  • Ensure the RETENTION_PERIOD and CHECKPOINT_INTERVAL parameters match the upstream provider’s log retention policy to avoid future offset expiration.
  • Restart the SQL Server Streaming Engine service via PowerShell: Restart-Service MSSQLSTREAMENGINE.

Step 4: Check Windows Event Viewer for Correlated Errors

  • Navigate to Event Viewer > Applications and Services Logs > Microsoft > SQL Server > Streaming.
  • Look for related Event IDs (such as 10082 or 10084) that may indicate the root cause (e.g., authentication timeout or network drop).

Alternative or Verification Steps

  • After applying the fix, run SELECT TOP 10 * FROM YourTableName ORDER BY ingestion_time DESC; to confirm new data is flowing in without errors.
  • If the issue persists, configure the table with FALLBACK_MODE = BATCH as a temporary workaround to switch from live streaming ingestion to scheduled batch polling until the stream source stabilizes.