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

by

Why This Error Happens

Error 172 occurs because the HOLDLOCK hint (or its equivalent SERIALIZABLE/REPEATABLE READ isolation level) is fundamentally incompatible with FOR BROWSE mode queries in SQL Server 2025. Browse mode relies on a specific row-versioning and locking mechanism to support client-side cursors, and it cannot coexist with the persistent shared locks that HOLDLOCK requests. This is typically triggered by legacy application code, ORM-generated queries, or ODBC/OLE DB drivers that append both clauses to the same SELECT statement without validation.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Enable SQL Server Profiler or Extended Events (sqlserver.error_reported) filtered on Error Number = 172.
  • Capture the exact T-SQL batch text, including the SELECT ... FOR BROWSE clause.
  • Check application logs in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the corresponding timestamp.

Step 2: Remove or Replace the HOLDLOCK Hint

  • Open the query in SQL Server Management Studio (SSMS) 21 and locate the WITH (HOLDLOCK) table hint.
  • Delete HOLDLOCK from the query if FOR BROWSE is required for cursor-based client access.
  • If transactional consistency is needed instead, replace FOR BROWSE with a standard cursor-based approach using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE at the session level rather than an inline hint.

Step 3: Update Application/Driver Configuration

  • Check the connection string or ODBC Data Source settings in Control Panel > ODBC Data Sources (64-bit) on Windows Server 2025 / Windows 11.
  • Ensure legacy DB-Library or ODBC drivers are not auto-injecting HOLDLOCK hints when browse mode is enabled (common in older client applications).
  • Update the driver to the latest Microsoft ODBC Driver 18+ for SQL Server, downloadable via winget install Microsoft.msodbcsql.

Step 4: Validate Query Execution Plan

  • Re-run the corrected query in SSMS with Include Actual Execution Plan enabled.
  • Confirm no locking hint conflicts appear in the Messages tab.
  • Deploy the fix to a staging environment before pushing to production servers.

Alternative or Verification Steps

  • Re-execute the original failing stored procedure or application call and confirm Error 172 no longer appears in ERRORLOG or the SSMS Messages pane.
  • If the application cannot be modified, consider migrating away from FOR BROWSE entirely in favor of API Server cursors (SQLSetStmtAttr with SQL_ATTR_CURSOR_TYPE) which support isolation-level locking without this restriction.