Troubleshooting Guide: SQL Server Error 966 — Assembly Name Conflict with System Assembly

Why This Error Happens

This warning occurs when a CLR assembly stored in a user database has the same name as one of the reserved system assemblies shipped with SQL Server 2025 (such as internal framework or Microsoft.SqlServer.* assemblies). During database upgrade or attach/restore operations, SQL Server detects this naming collision and automatically renames the user assembly by appending a suffix to avoid overwriting or conflicting with the system assembly. This typically surfaces after migrating databases from older SQL Server versions where CLR assembly naming conventions were less restrictive, or when custom SQLCLR assemblies were deliberately named after internal components.

Step-by-Step Resolution Guide

Step 1: Identify the Renamed Assembly and Its New Name

• Run the following query to locate affected assemblies in the database referenced by the error:
• SELECT name, assembly_id, clr_name FROM sys.assemblies WHERE is_user_defined = 1;
• Cross-reference the original name reported in the Error 966 message with the new name now present in sys.assemblies.
• Check SQL Server Error Log (ERRORLOG) or Event Viewer under Windows Logs > Application for the full text of the Event ID 966 entry, including both old and new assembly names.

Step 2: Update Dependent Objects and References

• Any stored procedures, functions, triggers, or application connection strings that explicitly reference the old assembly name by exact name will break — update these references.
• Run this query to find dependent CLR objects:
• SELECT o.name, o.type_desc, a.name AS assembly_name FROM sys.objects o JOIN sys.assembly_modules am ON o.object_id = am.object_id JOIN sys.assemblies a ON am.assembly_id = a.assembly_id;
• Update any deployment scripts, SSDT/DACPAC projects, or CI/CD pipelines that reference the assembly by its original name.

Step 3: Rename the Assembly Back to a Non-Conflicting Custom Name (Optional but Recommended)

• Instead of leaving the auto-generated suffixed name, explicitly rename it to something clearly distinct from any system assembly:
• ALTER ASSEMBLY [OldConflictingName_1] WITH NAME = N'YourCompany_CustomAssemblyName';
• Choose a naming convention (e.g., prefixing with your company or project name) to prevent future collisions during upgrades to later SQL Server versions.
• If the assembly needs to be fully redeployed, drop and re-add it using CREATE ASSEMBLY with the new safe name and re-link it to its original DLL/.dll binary or CREATE ASSEMBLY FROM 0x... binary stream.

Step 4: Validate CLR Integration Settings

• Confirm CLR integration is still enabled post-upgrade, as renamed assemblies can sometimes coincide with configuration resets:
• EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
• Verify clr strict security settings in sp_configure, since SQL Server 2025 enforces stricter assembly trust validation by default.

Alternative or Verification Steps

• Verify the fix by querying sys.assemblies again and confirming the assembly name no longer triggers a warning on database restart or re-attach; also re-check the SQL Server Error Log for recurrence of Event ID 966.
• If application connectivity still fails after renaming, consider dropping and recreating the assembly entirely using DROP ASSEMBLY followed by a fresh CREATE ASSEMBLY under a guaranteed unique name, then re-link all dependent CLR stored procedures/functions.

Troubleshooting Guide: SQL Server Error 967 – Disabled XML Index Due to Negative Date Values

Why This Error Happens

This error occurs because SQL Server 2025 has deprecated support for negative xs:date and xs:dateTime values (dates representing years before 0000) within XML data types. When the XML Indexing engine encounters these legacy negative date formats during an index rebuild or validation pass, it automatically disables the index to prevent data corruption or query miscalculation. This is typically triggered immediately following an in-place upgrade from an older SQL Server version (such as 2016 or 2019) where these legacy date formats were previously permitted and stored in the database.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Columns and Indexes

• Run a diagnostic query against sys.indexes and sys.xml_indexes to isolate the specific disabled index mentioned in the Error 967 message.
• Use the following T-SQL snippet to locate the disabled status:

  SELECT name, is_disabled FROM sys.indexes WHERE is_disabled = 1;

• Cross-reference the table name provided in the error string (%.*ls) to locate the specific XML column causing the conflict.

Step 2: Scan and Sanitize the XML Data

• Before rebuilding the index, you must find the “poison” rows containing the invalid negative dates.
• Write a SELECT query using .exist() or .value() XQuery methods to search for date patterns matching a negative year signature (e.g., values preceding 0000-01-01).
• Update these specific rows to use a NULL value or a valid minimum date (e.g., 0001-01-01T00:00:00) to comply with the new ISO 8601 standard enforced in SQL Server 2025.

Step 3: Drop and Rebuild the XML Index

• Once the underlying data is sanitized, the old disabled index metadata must be cleared.
• Navigate to SQL Server Management Studio (SSMS) or your query terminal and execute the drop command:

  DROP INDEX [IndexName] ON [dbo].[YourTable];

• Recreate the index using the standard CREATE PRIMARY XML INDEX syntax, ensuring the ONLINE = ON option is used if running on Enterprise Edition to minimize downtime.

Step 4: Check Database Compatibility Level

• Verify that the database is not incorrectly flagged for an older compatibility level that might cause conflicting date parsing logic.
• Navigate to Database Properties in SSMS, or run ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170; to ensure full alignment with SQL Server 2025 engine standards.

Alternative or Verification Steps

• Verification: After rebuilding, run DBCC CHECKDB or simply query sys.indexes again to confirm is_disabled now reads 0 for the target index.
• Alternative: If the data cannot be modified due to compliance/archival reasons, consider migrating the specific table to a VARCHAR(MAX) storage format instead of native XML type to bypass the native XML indexing date validation entirely.

Troubleshooting Guide: SQL Server Error 964 — Missing System User Auto-Restored

Why This Error Happens

Error 964 occurs when SQL Server’s startup validation detects that a required system user (such as sys, INFORMATION_SCHEMA, or guest) is missing from a database’s metadata, typically due to an interrupted upgrade, a corrupted system catalog, or manual tampering with system tables. SQL Server 2025 automatically repairs this by recreating the missing user, but the warning indicates underlying database consistency issues that may stem from improper restores, failed in-place upgrades from SQL Server 2022, or abrupt shutdowns during schema modification. If left unaddressed, repeated occurrences may signal deeper corruption in the system catalog views that requires further investigation.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Warning

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log via EXEC sp_readerrorlog; to capture the full context of Error 964.
• Note the exact database name and user referenced in the message (e.g., %.*ls) to determine if it’s a system database (master, msdb, model) or a user database.
• Check Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for correlating Event ID 964 entries.

Step 2: Run Database Consistency Checks

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for additional corruption beyond the missing user.
• If errors are found in system tables, run DBCC CHECKDB ('YourDatabaseName') WITH DATA_PURITY; to validate column-level data integrity.
• Review output for any REPAIR_ALLOW_DATA_LOSS recommendations — do not apply this immediately without a backup.

Step 3: Validate System Database Integrity

• Restart the SQL Server service via services.msc or PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
• Confirm the automatic restoration completed by querying SELECT * FROM sys.database_principals WHERE name = 'affected_user_name'; to verify the user now exists.
• If the issue persists on restart, rebuild the system databases using setup.exe /ACTION=REBUILDDATABASE from the SQL Server 2025 installation media, pointing to the correct instance.

Step 4: Restore from a Known-Good Backup (If Corruption Persists)

• Identify the last clean full backup using RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourDB.bak';.
• Perform a restore to a test instance first: RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;.
• Validate the restored database passes DBCC CHECKDB before promoting it to production.

Alternative or Verification Steps

• Run SELECT name, type_desc FROM sys.database_principals; in the affected database to confirm all system users (dbo, guest, sys, INFORMATION_SCHEMA) are present and correctly mapped.
• If the warning recurs after a clean restart, open a support case with Microsoft and provide the full Error Log export, as this may indicate a deeper metadata corruption requiring an offline repair via DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option as a last resort.

Troubleshooting Guide: SQL Server Error 965 — Column Nullability Inconsistency

Why This Error Happens

Error 965 occurs when SQL Server detects a mismatch between the nullability metadata recorded in the index structure and the actual nullability defined on the underlying column in the table schema. This is typically caused by metadata corruption resulting from an abrupt server shutdown, storage-level I/O failures, or a bug triggered during online schema changes (such as ALTER TABLE ALTER COLUMN) that modify nullability while indexes are being rebuilt concurrently. It can also surface after restoring a database from a backup that was taken during an in-flight DDL operation, leaving the system catalog and index metadata out of sync.

Step-by-Step Resolution Guide

Step 1: Isolate the Affected Database and Run Diagnostics

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance reporting the error.
• Run DBCC CHECKTABLE('YourTableName') WITH ALL_ERRORMSGS, NO_INFOMSGS; to confirm the scope of the corruption.
• Cross-reference the object ID and index_id from the error message using:

SELECT OBJECT_NAME(object_id), name FROM sys.indexes WHERE object_id = <object_id> AND index_id = <index_id>;

Step 2: Take the Database Offline for Safe Repair

• Place the database in single-user mode to prevent further writes during repair:

ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Run a full consistency check to determine repair scope:

DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output carefully — repairallowdata_loss should only be used as a last resort.

Step 3: Rebuild the Affected Index

• In most cases, a targeted index rebuild resolves the nullability mismatch without data loss:

ALTER INDEX [IndexName] ON [YourTableName] REBUILD WITH (ONLINE = OFF, MAXDOP = 1);

• If the index is on a system table or the rebuild fails, escalate to:

DBCC CHECKDB('YourDB', REPAIR_REBUILD);

• Return the database to multi-user mode once repairs complete:

ALTER DATABASE YourDB SET MULTI_USER;

Step 4: Validate Underlying Storage and Windows Event Logs

• Check Windows Server 2025 / Windows 11 Event Viewer under Windows Logs > System and Application for Disk, NTFS, or Storport errors around the time corruption began.
• Run chkdsk C: /f /r (adjust drive letter to your SQL data volume) on the next reboot to rule out physical disk issues.
• Verify your storage subsystem (SAN, NVMe, or virtual disk) isn’t silently dropping write-cache flushes, which can cause torn metadata pages.

Alternative or Verification Steps

• After rebuild, re-run DBCC CHECKTABLE('YourTableName') WITH NO_INFOMSGS; and confirm no errors are returned — a clean pass confirms the fix.
• If corruption recurs frequently, restore the database from the last known-good backup predating the inconsistency, then apply transaction log backups up to the point just before the corrupting event.

Troubleshooting Guide: SQL Server Error 961 – Duplicate Index Name Auto-Renamed

Why This Error Happens

This warning occurs when SQL Server detects two or more indexes on the same object sharing an identical name, typically as a result of a restore, replication, upgrade migration, or a manual schema change that bypassed naming validation. Rather than failing the operation, SQL Server 2025 automatically renames the conflicting index by appending a system-generated suffix to preserve database integrity. While not fatal, this indicates an underlying metadata inconsistency—often introduced by scripts, third-party ORM tools, or partial restores—that should be corrected to avoid confusion in execution plans, maintenance jobs, or index-specific T-SQL scripts that reference the original name.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Object and Index Names

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to the instance reporting the error.
• Run the following query to locate the renamed index and its duplicate sibling:

SELECT object_id, name, index_id 
FROM sys.indexes 
WHERE object_id = OBJECT_ID('YourSchema.YourTable')
ORDER BY index_id;

• Cross-reference the object ID and index_id values from the Error 961 message in the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG by default on Windows Server 2025).

Step 2: Rename the Index to a Meaningful, Unique Name

• Use sp_rename to manually assign a clear, non-conflicting name to the auto-renamed index:

EXEC sp_rename 'YourSchema.YourTable.index_old_autoname', 'IX_YourTable_NewUniqueName', 'INDEX';

• Ensure the new name follows your organization’s naming convention (e.g., IX_TableName_ColumnName) to prevent future collisions.

Step 3: Audit Deployment and Migration Scripts

• Review any DACPAC, SSDT project, or Flyway/Liquibase migration scripts that may have introduced the duplicate index definition.
• Search your source control repository for CREATE INDEX statements referencing the same index name across different migration versions.
• Correct the script to use CREATE INDEX ... WITH (DROP_EXISTING = ON) or add explicit IF NOT EXISTS checks to prevent silent duplication on re-deployment.

Step 4: Rebuild and Validate Index Metadata

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no deeper corruption triggered the naming conflict.
• Rebuild statistics on the affected table to ensure the query optimizer recognizes the corrected index name:

UPDATE STATISTICS YourSchema.YourTable;

Alternative or Verification Steps

• Re-query sys.indexes for the affected object_id to confirm only one index per name exists and that naming matches your intended convention.
• If the duplicate recurs after redeployment, inspect your CI/CD pipeline (e.g., Azure DevOps or GitHub Actions) for cached or stale DACPAC artifacts being applied out of sequence, and clear the build cache before re-running the deployment.

Troubleshooting Guide: SQL Server Error 962 — Constraint Renamed Due to Index Rename

Why This Error Happens

This is an informational warning, not a critical failure, raised by SQL Server’s system catalog maintenance when a PRIMARY KEY or UNIQUE constraint’s underlying index is renamed using sp_rename. Because SQL Server enforces a strict naming dependency between a constraint and its backing index, the engine automatically renames the constraint to match the new index name to maintain catalog consistency. This typically occurs when a DBA, a migration script, or a third-party schema comparison/deployment tool (such as a sync tool or ORM migration) directly renames an index object instead of properly dropping and recreating the constraint.

Step-by-Step Resolution Guide

Step 1: Confirm the Scope of the Rename

• Query sys.indexes and sys.key_constraints to identify the affected object:

  SELECT kc.name AS constraint_name, i.name AS index_name, kc.object_id
  FROM sys.key_constraints kc
  JOIN sys.indexes i ON kc.parent_object_id = i.object_id AND kc.unique_index_id = i.index_id
  WHERE kc.object_id = <object_id_from_error>;

• Confirm this was an intentional rename (e.g., from a deployment script) rather than an accidental schema drift.

Step 2: Decide Whether the Auto-Rename Is Acceptable

• If the new constraint name is acceptable, no action is needed — this is expected engine behavior and the warning can be safely ignored.
• If a specific constraint name is required (e.g., for naming conventions or downstream scripts referencing it), proceed to Step 3.

Step 3: Explicitly Rename the Constraint to the Desired Name

• Use sp_rename again, but target the constraint object type explicitly:

  EXEC sp_rename 'dbo.YourTable.OldOrAutoRenamedConstraintName', 'YourDesiredConstraintName', 'OBJECT';

• Avoid renaming indexes tied to constraints directly in the future; instead, drop and recreate the constraint with the desired name:

  ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_OldName;
  ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_NewName PRIMARY KEY (YourColumn);

Step 4: Update Deployment Scripts and Tooling

• If a schema migration tool (DACPAC, Flyway, Liquibase, EF Core Migrations) caused this, update the migration script to use ALTER TABLE ... DROP/ADD CONSTRAINT syntax instead of sp_rename on the index.
• On Windows Server 2025/Windows 11, check SQL Server Agent job history (via SSMS → Management → SQL Server Logs) to confirm which job or script triggered the rename, using Event Viewer (Applications and Services Logs > MSSQLSERVER) cross-referenced with the timestamp of Error 962.

Alternative or Verification Steps

• Re-run SELECT name FROM sys.key_constraints WHERE object_id = <object_id>; to confirm the constraint now reflects your intended naming convention.
• If constraint names continue to drift unexpectedly, audit any automated index maintenance scripts or third-party tools (e.g., Redgate, ApexSQL) for logic that calls sp_rename on indexes backing constraints, and reconfigure them to operate at the constraint level instead.

Troubleshooting Guide: SQL Server Error 963 – Database Marked Suspect During Upgrade

Why This Error Happens

This error occurs when SQL Server 2025 encounters an unrecoverable problem while running upgrade scripts against a database during the recovery phase, often due to corrupted system catalog metadata, an incomplete transaction log, or insufficient disk space halting mid-upgrade. Because the upgrade process could not complete all required schema or version changes, SQL Server flags the database as suspect to prevent further data integrity issues. The database is still accessible in a limited capacity (restricted_user state) so administrators can diagnose and repair it before reopening it to all users.

Step-by-Step Resolution Guide

Step 1: Review the Error Log for Root Cause

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (adjust instance folder name as needed for SQL Server 2025).
• Search for entries immediately preceding Error 963, such as Error 5171 (invalid file header), Error 823/824 (I/O errors), or specific upgrade script failures (e.g., “Could not create clustered index”).
• Cross-reference with Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for disk or permission-related warnings.

Step 2: Verify Disk Space and File Integrity

• Confirm the drive hosting .mdf, .ndf, and .ldf files has sufficient free space; upgrades can fail silently if log files cannot autogrow.
• Run the following to check database file status:

SELECT name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');

• If files show as RECOVERY_PENDING or SUSPECT, proceed to the next step for repair.

Step 3: Attempt to Bring the Database Online via ALTER DATABASE

• Connect to the instance using SQLCMD or SSMS with an account holding sysadmin privileges.
• Execute:

ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
GO
ALTER DATABASE [YourDatabaseName] SET SINGLE_USER;
GO
DBCC CHECKDB ([YourDatabaseName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO

• Review DBCC CHECKDB output carefully for the extent of corruption before proceeding, as REPAIR_ALLOW_DATA_LOSS may remove corrupted pages/rows.

Step 4: Restore From Backup if Repair Fails

• If DBCC CHECKDB cannot resolve the corruption, restore the most recent full backup plus differential/log backups taken before the upgrade began.
• Use:

RESTORE DATABASE [YourDatabaseName] FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH NORECOVERY;
RESTORE LOG [YourDatabaseName] FROM DISK = 'D:\Backups\YourDatabaseName_log.trn' WITH RECOVERY;

• Re-run the SQL Server 2025 upgrade process only after confirming the database is in a healthy, consistent state.

Step 5: Set Database Back to Multi-User Mode

• Once repair or restore completes successfully, return the database to normal operation:

ALTER DATABASE [YourDatabaseName] SET MULTI_USER;
GO

• Confirm the state_desc column in sys.databases now reads ONLINE rather than SUSPECT or RESTRICTED_USER.

Alternative or Verification Steps

• Run SELECT name, state_desc FROM sys.databases; to confirm the target database shows ONLINE status and is reachable by standard application logins.
• If the database repeatedly reverts to suspect mode after upgrade attempts, detach it, copy the .mdf/.ldf files to a test instance running the prior SQL Server version, and attempt the upgrade there first to isolate whether the issue is environment-specific (e.g., permissions on the NT SERVICE\MSSQLSERVER account) or data-specific corruption.

Troubleshooting Guide: SQL Server Error 959 – Resource Database Version Mismatch

Why This Error Happens

Error 959 occurs when the mssqlsystemresource.mdf and .ldf files do not match the version expected by the installed SQL Server 2025 binaries. This typically happens after an incomplete or failed SQL Server upgrade, a manual restoration of resource database files from a different version, or file corruption caused by improper shutdowns. Because the resource database is read-only and contains all system objects, any version mismatch prevents the Database Engine from starting correctly.

Step-by-Step Resolution Guide

Step 1: Verify the Current Resource Database Files

• Navigate to the default binaries path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\
• Confirm the presence and timestamps of mssqlsystemresource.mdf and mssqlsystemresource.ldf.
• Check the SQL Server Error Log (located in ...\MSSQL\Log\ERRORLOG) for the exact version numbers reported in the error message (%d values).

Step 2: Restore the Correct Resource Database Version

• Locate a backup of the correct resource database version, typically found in the SQL Server installation media under \x64\.
• Stop the SQL Server service using services.msc or run: NET STOP MSSQLSERVER
• Copy the correct mssqlsystemresource.mdf and .ldf files into the Binn directory, overwriting the mismatched versions.
• Restart the service: NET START MSSQLSERVER

Step 3: Run a Repair Installation via Setup

• If no valid backup copy exists, launch SQL Server 2025 Setup from the installation media or ISO.
• Select Maintenance > Repair from the SQL Server Installation Center.
• Choose the affected instance and allow Setup to restore the resource database files to the matching build version.
• Ensure Windows Update or any pending Cumulative Updates (CUs) are not partially applied during this process.

Step 4: Rebuild System Databases (If Repair Fails)

• Run Setup again and select the Rebuild Database option from the Advanced tab, or use the command line: Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators
• Reapply the latest Cumulative Update (CU) immediately after rebuilding to realign all system database versions.

Alternative or Verification Steps

• After restarting the service, check the ERRORLOG file for the line “SQL Server is now ready for client connections,” confirming the resource database loaded successfully.
• Run SELECT SERVERPROPERTY('ResourceVersion'); in a query window to confirm the version now matches your installed SQL Server build.

Troubleshooting Guide: SQL Server Error 960 – “sys” User Renamed Warning

Why This Error Happens

This warning occurs when a database is restored or upgraded from an older version of SQL Server where a user or schema was explicitly named “sys,” which conflicts with the reserved system name in SQL Server 2025. During the upgrade process, SQL Server automatically detects this naming collision and renames the legacy “sys” principal to prevent catalog corruption, appending a system-generated suffix to the name. This is not a failure state but a compatibility safeguard; however, it can break applications, stored procedures, or linked logins that explicitly reference the old “sys” username.

Step-by-Step Resolution Guide

Step 1: Identify the Renamed Principal

• Open SQL Server Management Studio (SSMS) and connect to the affected instance on your Windows Server 2025 or Windows 11 machine.
• Run the following query to locate the renamed user and its new system-generated name:

  SELECT name, principal_id, type_desc 
  FROM sys.database_principals 
  WHERE name LIKE '%sys%';

• Note the principal_id and the new auto-generated name (e.g., sys1, sys_old, etc.) for reference in later steps.

Step 2: Update Application and Script Dependencies

• Search your application connection strings, stored procedures, and SSIS packages for hardcoded references to the sys user.
• Use the following command to cross-reference object dependencies tied to the renamed principal:

  SELECT * FROM sys.sql_expression_dependencies 
  WHERE referenced_entity_name = 'sys';

• Update any EXECUTE AS clauses or explicit GRANT/DENY statements that reference the old username.

Step 3: Rename the Principal to a Compliant Name

• Since “sys” cannot be reused, rename the principal to a clear, non-reserved identifier using ALTER USER:

  ALTER USER [sys_1] WITH NAME = [legacy_sys_user];

• Ensure the new name does not conflict with any other reserved keywords in SQL Server 2025 (e.g., guest, public, INFORMATION_SCHEMA).
• Re-apply any schema ownership or permissions that were lost during the rename using ALTER AUTHORIZATION.

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 and restart the “SQL Server (MSSQLSERVER)” service to clear any cached principal metadata.
• Alternatively, use PowerShell to restart the service:

  Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Verify the fix by querying sys.database_principals again to confirm the user now has a distinct, non-conflicting name and that principal_id permissions remain intact.
• If the warning persists after a restart, check the SQL Server Error Log (via EXEC sp_readerrorlog) for Event ID 960 recurrence, which may indicate the database needs to be set to Compatibility Level 170 (SQL Server 2025) explicitly via ALTER DATABASE ... SET COMPATIBILITY_LEVEL = 170.

Troubleshooting Guide: SQL Server Error 958 — Resource Database Build Version

Why This Error Happens

Error 958 is not a failure condition; it is an informational message that SQL Server 2025 writes to the error log during startup to record the internal build version of the mssqlsystemresource (Resource) database. This message appears every time the SQL Server service starts or after a patch/upgrade, confirming that the Resource database’s version matches the installed SQL Server build. It typically draws attention only because administrators scanning Event Viewer or the SQL error log mistake it for an actionable warning when, in reality, no corruption, permission issue, or configuration conflict is implied.

Step-by-Step Resolution Guide

Step 1: Confirm the Message Is Informational Only

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Run EXEC xp_readerrorlog 0, 1, N'958'; to view the full context of the message.
• Confirm the text explicitly states “This is an informational message only. No user action is required.” — this confirms normal behavior.

Step 2: Validate Resource Database Version Alignment

• Run the following query to check the Resource database version against your installed SQL Server build:

  SELECT SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
         SERVERPROPERTY('ProductVersion') AS ProductVersion;

• Compare the output with the expected build number listed in the official Microsoft SQL Server 2025 build reference documentation.
• If both versions match (which they should after a clean install or patch), no further action is required.

Step 3: Review Windows Event Viewer and SQL Error Log for Co-Occurring Errors

• Open Event Viewer > Windows Logs > Application on Windows Server 2025 / Windows 11 and filter by Source: MSSQLSERVER.
• Check whether Error 958 appears alone or alongside other critical errors (e.g., Error 3414, 5173, or 824) which would indicate actual corruption.
• If Error 958 is the only entry near the startup timestamp, it confirms the message is purely cosmetic logging.

Step 4: Suppress Unnecessary Log Noise (Optional)

• If the repeated appearance of Error 958 is cluttering your monitoring/alerting pipeline, adjust your log parsing rules (e.g., in SQL Server Agent Alerts, SCOM, or a SIEM tool) to explicitly exclude Error Number 958 from triggering notifications.
• Example SQL Agent Alert exclusion: set the alert’s Error number filter to ignore 958 while still capturing severity-based alerts (Severity 16+).

Step 5: Re-run Setup Repair Only If Mismatch Is Found

• If Step 2 revealed a genuine mismatch between ResourceVersion and ProductVersion, launch SQL Server Installation Center > Maintenance > Repair from the SQL Server 2025 setup media.
• Follow the repair wizard to restore the correct Resource database files (mssqlsystemresource.mdf and .ldf) located by default in:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\
• Restart the SQL Server service afterward via SQL Server Configuration Manager.

Alternative or Verification Steps

• Verify the fix by restarting the SQL Server service and confirming Error 958 logs the same, consistent build version on every startup with no accompanying severity 16+ errors.
• If mismatches persist after repair, perform a full uninstall and clean reinstall of the SQL Server 2025 instance to guarantee Resource database integrity, then restore databases from a verified backup.

Troubleshooting Guide: Error 955 — “Database %.*ls is enabled for Database Mirroring”

Why This Error Happens

This error occurs when SQL Server attempts to perform an operation—such as ALTER DATABASE, dropping a file, or changing recovery settings—on a database that currently has Database Mirroring enabled. Because the database is in a mirrored state, certain structural changes are blocked to maintain transactional consistency between the principal and mirror servers. This typically happens when an administrator forgets to pause or remove mirroring before attempting schema-altering tasks, or when a failover event leaves the database in an unexpected mirroring state.

Step-by-Step Resolution Guide

Step 1: Verify the Current Mirroring State

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to check mirroring status:

  SELECT name, mirroring_state_desc 
  FROM sys.database_mirroring 
  WHERE database_id = DB_ID('YourDatabaseName');

• Confirm whether the database is SYNCHRONIZED, SUSPENDED, or in another mirroring state.

Step 2: Remove or Pause Database Mirroring

• If mirroring must be temporarily disabled to perform the operation, run:

  ALTER DATABASE YourDatabaseName SET PARTNER OFF;

• This breaks the mirroring session (principal side) and allows structural changes.
• If you only need to pause (not fully remove) mirroring, use:

  ALTER DATABASE YourDatabaseName SET PARTNER SUSPEND;

• Note: Running this on Windows Server 2025 requires sysadmin or db_owner privileges on the instance.

Step 3: Perform the Required Database Operation

• Once mirroring is disabled or suspended, retry the original operation that triggered Error 955 (e.g., ALTER DATABASE, file removal, or recovery model change).
• Example:

  ALTER DATABASE YourDatabaseName MODIFY FILE 
  (NAME = YourLogicalFileName, SIZE = 500MB);

• Confirm the command completes without triggering Error 955 again.

Step 4: Re-Establish Mirroring (If Required)

• If mirroring was removed, reconfigure the mirroring partnership using the Database Mirroring Wizard in SSMS or via T-SQL:

  ALTER DATABASE YourDatabaseName SET PARTNER = 'TCP://MirrorServer:5022';

• Ensure the mirror server’s endpoint is listening and the certificates/service accounts are correctly configured on Windows Server 2025.

Alternative or Verification Steps

• After completing the fix, re-run the sys.database_mirroring query to confirm the mirroring state matches your intended configuration (e.g., SYNCHRONIZED if mirroring was restored, or NULL if mirroring is fully removed).
• If mirroring cannot be safely paused in a production environment, consider migrating to Always On Availability Groups (the recommended replacement for Database Mirroring in SQL Server 2025) to avoid this legacy limitation entirely.

Troubleshooting Guide: Error 956 — Database Mirroring State Change Notification

Why This Error Happens

Error 956 is an informational/state-change message indicating that a database enabled for Database Mirroring has transitioned state (for example, from synchronizing to synchronized, or suspended to resumed). This is typically triggered by network latency between the principal and mirror servers, a mismatch in endpoint configurations, or an interruption in the mirroring session caused by insufficient permissions on the mirroring endpoint. In SQL Server 2025 running on Windows Server 2025 or Windows 11, this can also surface when the Database Mirroring endpoint certificate has expired or when the service account lacks CONNECT permissions on the endpoint.

Step-by-Step Resolution Guide

Step 1: Verify Database Mirroring Endpoint Status

• Open SQL Server Management Studio (SSMS) and connect to both the principal and mirror instances.
• Run the following query on each instance to confirm the endpoint is started:

  SELECT name, state_desc, role_desc FROM sys.database_mirroring_endpoints;

• If the state is STOPPED, start it manually:

  ALTER ENDPOINT [Mirroring] STATE = STARTED;

Step 2: Check Network Connectivity and Firewall Rules

• Confirm that the mirroring port (default 5022) is open on both servers.
• On Windows Server 2025 / Windows 11, open Windows Defender Firewall with Advanced Security and verify an inbound rule exists for TCP port 5022.
• Test connectivity using PowerShell:

  Test-NetConnection -ComputerName <MirrorServerName> -Port 5022

Step 3: Validate Certificates and Permissions

• Navigate to SQL Server Configuration Manager > SQL Server Services and confirm the service account used by the Database Engine has not changed.
• Re-issue or renew the mirroring certificate if it has expired:

  CREATE CERTIFICATE Mirror_Cert
  FROM FILE = 'C:\Certs\mirror_cert.cer';

• Grant CONNECT permission on the endpoint to the correct login:

  GRANT CONNECT ON ENDPOINT::[Mirroring] TO [DOMAIN\SQLServiceAccount];

Step 4: Review SQL Server Error Logs and Extended Events

• Open SQL Server Management Studio > Management > SQL Server Logs and look for additional mirroring-related messages (Error 1479, 1443, or 1440) surrounding Error 956.
• Cross-reference timestamps with Windows Event Viewer (Application log) on both Windows Server 2025 instances to identify any correlating network or authentication failures.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_db_mirroring_status; on both principal and mirror servers to confirm the mirroringstatedesc reads SYNCHRONIZED.
• If the issue persists, remove and reconfigure mirroring using ALTER DATABASE [YourDB] SET PARTNER OFF; followed by re-establishing the mirroring session from scratch via the Configure Database Mirroring Security Wizard in SSMS.

Troubleshooting Guide: Error 957 – Database Name Cannot Be Changed (Mirroring/AG Enabled)

Why This Error Happens

This error occurs because SQL Server 2025 locks the database name when it is actively participating in a Database Mirroring session or is a member of an Availability Group (AG). The rename operation is blocked by design to prevent inconsistencies between the metadata on the primary/principal replica and the secondary/mirror replicas, since the partner server(s) would not automatically be aware of the name change. Essentially, the engine prevents a naming mismatch that would break the synchronization or failover listener configuration.

Step-by-Step Resolution Guide

Step 1: Identify the High Availability Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance via the Object Explorer.
• Run the following query to check the status of the database in question:

SELECT name, is_mirroring_enabled, replica_id, group_database_id 
FROM sys.databases 
WHERE name = 'YourDatabaseName';

• If is_mirroring_enabled returns 1 or group_database_id is populated, the database is locked for renaming.

Step 2: Remove the Database from the Availability Group (If Applicable)

• Navigate to Object Explorer > Always On High Availability > Availability Groups in SSMS.
• Right-click the specific Availability Group and select Remove Database from Availability Group.
• Alternatively, run the T-SQL command on the Primary Replica:

ALTER AVAILABILITY GROUP [YourAGName] REMOVE DATABASE [YourDatabaseName];

• Note: This will not delete the data, but it will stop synchronization to the secondary replicas until re-added.

Step 3: Remove Database Mirroring (If Applicable)

• If the database uses legacy Database Mirroring instead of an AG, you must break the mirroring partnership first.
• Execute the following command on the Principal server:

ALTER DATABASE [YourDatabaseName] SET PARTNER OFF;

• This severs the connection to the mirror server, releasing the name lock.

Step 4: Rename the Database

• With the database removed from the AG or mirroring session, execute the rename command:

ALTER DATABASE [OldDatabaseName] MODIFY NAME = [NewDatabaseName];

• Ensure no active connections are using the database; if the command hangs, check sys.dm_exec_sessions for blocking locks.

Step 5: Re-Join the Availability Group or Mirror

• Once the rename is successful, re-add the database back into the High Availability group using the renamed identifier.
• For AGs, use the Add Database wizard in SSMS or run ALTER AVAILABILITY GROUP [YourAGName] ADD DATABASE [NewDatabaseName];.
• Re-establish mirroring via the ALTER DATABASE... SET PARTNER syntax if using legacy mirroring.

Alternative or Verification Steps

• Verification: After the rename, run SELECT name FROM sys.databases; to confirm the new name is reflected, and check the Always On Dashboard in SSMS to ensure the “Synchronized” state is restored on secondary replicas.
• Alternative: If removing the database from the AG is not feasible due to production constraints, consider performing the rename during a scheduled maintenance window using a temporary failover to a secondary replica to minimize downtime impact on the listener.

Troubleshooting Guide: SQL Server Error 952 — Database in Transition

Why This Error Happens

Error 952 occurs when SQL Server has placed a database into a transitional state (such as during a ALTER DATABASE SET ONLINE/OFFLINE, recovery, mirroring failover, or Availability Group role change) and the database engine has not yet finished applying that state change. Any query or connection attempt that arrives during this brief window is rejected because the database metadata is locked for the transition operation. This is commonly triggered by long-running recovery processes, a stuck mirroring/AG failover, or overlapping administrative commands issued against the same database in rapid succession.

Step-by-Step Resolution Guide

Step 1: Confirm the Current Database State

• Open SQL Server Management Studio (SSMS) or run a query via sqlcmd against the instance.
• Execute: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Look for states such as RECOVERY_PENDING, RESTORING, or OFFLINE instead of ONLINE.

Step 2: Check for Blocking Administrative Operations

• Query active sessions to ensure no other ALTER DATABASE or failover command is still executing.
• Run: SELECT session_id, command, status, wait_type FROM sys.dm_exec_requests WHERE command LIKE '%DB%';
• If an Availability Group failover is in progress, check its status in SQL Server Management Studio > Always On High Availability > Dashboard.

Step 3: Wait for Recovery or Forcefully Complete the Transition

• For simple recovery delays, allow SQL Server time to complete the pending operation (check the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG).
• If the database is stuck, run: ALTER DATABASE [YourDatabaseName] SET ONLINE;
• If mirroring/AG-related, manually resume the role using: ALTER DATABASE [YourDatabaseName] SET HADR RESUME;

Step 4: Restart the SQL Server Service (If Still Stuck)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to force the database state machine to reinitialize cleanly.

Alternative or Verification Steps

• Re-run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm it now reads ONLINE.
• If the issue persists after a restart, check for antivirus or backup software locking the database files in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\, as file-level locks can prevent transition completion.

Troubleshooting Guide: SQL Server Error 954 – “The database cannot be opened. It is acting as a mirror database.”

Why This Error Happens

This error occurs because the database is currently configured as a mirror (or secondary) copy within a Database Mirroring or Always On configuration, which makes it inaccessible for direct read/write queries by design. SQL Server 2025 restricts connections to mirror databases because they are continuously receiving transaction log updates from the principal server and must remain in a restoring state to stay synchronized. This typically surfaces when an application or user attempts to connect directly to the mirror instance instead of the principal, or when a failover has occurred without updating the client connection string.

Step-by-Step Resolution Guide

Step 1: Identify the Current Role of the Database

• Open SQL Server Management Studio (SSMS) and connect to the suspected mirror instance.
• Run the following query to check the mirroring state:

  SELECT DB_NAME(database_id) AS DatabaseName, mirroring_role_desc, mirroring_state_desc 
  FROM sys.database_mirroring 
  WHERE database_id = DB_ID('YourDatabaseName');

• Confirm if the mirroring_role_desc returns MIRROR rather than PRINCIPAL.

Step 2: Redirect the Connection String to the Principal Server

• Update the application’s connection string to point to the Principal server name/IP rather than the Mirror.
• If using Database Mirroring (not Always On), ensure the Failover Partner property is set correctly in the ADO.NET or ODBC connection string:

  Server=PrincipalServer;Failover Partner=MirrorServer;Database=YourDB;

• Restart the application pool or service consuming the connection to force a refresh of the connection pool.

Step 3: Verify the Mirroring Endpoint and Network Configuration

• Navigate to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols.
• Ensure the TCP/IP endpoint used for mirroring (default port 5022) is not blocked by the Windows Defender Firewall on Windows Server 2025.
• Check the SQL Server Error Log (usually located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for “Database Mirroring” state change events to ensure the partners are communicating (SYNCHRONIZED).

Step 4: Force a Manual Failover (If Principal is Unresponsive)

• If the Principal server is down and you need the Mirror to become writable immediately, execute the following on the Mirror instance:

  ALTER DATABASE YourDatabaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

• Note: This should only be used in disaster recovery scenarios as it may result in data loss for transactions not yet sent to the mirror.

Alternative or Verification Steps

• Verify Fix: Re-run the sys.database_mirroring query from Step 1; the mirroring_role_desc should now show PRINCIPAL on the server you are trying to query, and the application should connect without Error 954.
• Alternative: If mirroring is no longer required, consider breaking the mirroring session via ALTER DATABASE YourDatabaseName SET PARTNER OFF; on the Principal to restore the database to a standard, directly accessible ONLINE state.

Troubleshooting Guide: SQL Server Error 950

Why This Error Happens

Error 950 occurs when a database’s internal version number marks it as a non-release (pre-release/beta) build of SQL Server, which is incompatible with the production release of the engine attempting to mount it. This commonly happens when a database was created or last written to by a Community Technology Preview (CTP), Release Candidate (RC), or private internal build of SQL Server 2025, and is then attached to a General Availability (GA) or Cumulative Update instance. SQL Server intentionally blocks this upgrade path because internal storage formats in non-release builds are unstable and not guaranteed to be forward-compatible, risking data corruption if mounted.

Step-by-Step Resolution Guide

Step 1: Identify the Source Build of the Database

• Check the SQL Server error log (located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for the exact internal version number reported alongside Error 950.
• Cross-reference this version number against Microsoft’s build documentation to confirm it originated from a CTP/RC build rather than a GA release.
• Run SELECT @@VERSION; on the original server (if still accessible) to confirm the exact build that created the database.

Step 2: Restore From a Supported Backup

• Locate the most recent .bak file created before the non-release build was installed, typically stored in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\.
• Use SQL Server Management Studio (SSMS) or Azure Data Studio to run a restore:

  RESTORE DATABASE [YourDB] 
  FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Backup\YourDB.bak' 
  WITH REPLACE, RECOVERY;

• If no backup exists, check for an Always On Availability Group replica or a log-shipped secondary that was built from a release version.

Step 3: Re-create the Database and Migrate Data

• If no valid backup exists, create a fresh database on the GA instance:

  CREATE DATABASE [YourDB_New];

• Use the Import/Export Wizard in SSMS, or BCP.exe, to extract data from any accessible non-corrupted sources (such as replication subscribers or ETL staging tables) into the new database.
• Re-apply schema objects (stored procedures, views, indexes) from source control or a schema-only script if available.

Step 4: Upgrade Server Build Consistency Going Forward

• Uninstall any residual CTP/RC bits using Add or Remove Programs on Windows Server 2025 / Windows 11 to prevent future mismatched attach attempts.
• Install only GA release media or the latest Cumulative Update (CU) from the official Microsoft Download Center.
• Confirm your production environment policy blocks pre-release SQL builds from touching production storage paths (e.g., shared SAN/NAS mount points).

Alternative or Verification Steps

• Verify the fix by running sp_helpdb YourDB_New; or checking SSMS Object Explorer to confirm the database mounts cleanly with status ONLINE.
• If a backup is unavailable and data loss is unacceptable, open a Microsoft Support ticket referencing Error 950 — in rare cases, Microsoft CSS has internal tools to inspect non-release database pages, but this is not guaranteed to succeed and is not a substitute for proper backups.

Troubleshooting Guide: SQL Server Error 951 – Database Upgrade Step Failure

Why This Error Happens

Error 951 occurs when SQL Server 2025 attempts to run an internal database upgrade script during startup but the process is interrupted or blocked mid-execution. This is typically caused by the database being left in a transitional state after a failed or forcibly terminated upgrade, insufficient permissions for the SQL Server service account to modify system catalog objects, or corruption in the model/system databases that prevents the version-stepping logic from completing. It can also surface when a database is restored from an incompatible or newer internal version that the current SQL Server build does not fully support.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service is running under an account with full rights to the data directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Right-click the data folder, go to Properties > Security, and ensure NT SERVICE\MSSQLSERVER has Full Control.
• Restart the SQL Server service after applying permission changes.

Step 2: Check SQL Server Error Logs for the Failed Upgrade Step

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for the specific version numbers referenced in the Error 951 message (e.g., “version 904 to 905”) to identify exactly which internal script failed.
• Cross-reference the failing step with Microsoft’s KB articles for your specific Cumulative Update build to check for known issues.

Step 3: Start SQL Server in Single-User Mode and Rerun Upgrade

• Open Command Prompt as Administrator and run:

  NET STOP MSSQLSERVER
  NET START MSSQLSERVER /m

• Connect via sqlcmd -S localhost -E and manually run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect corruption blocking the upgrade.
• If corruption is found, restore the affected database from the last known good backup before retrying the upgrade.

Step 4: Rebuild or Restore the Master Database (If System DB Is Affected)

• If the error references master or model, boot SQL Server using the -f (minimal configuration) startup flag via SQL Server Configuration Manager under Service > Startup Parameters.
• Run the SQL Server setup media with the /ACTION=REBUILDDATABASE switch to rebuild system databases if corruption is confirmed.
• Reapply the latest Cumulative Update immediately after rebuilding to realign internal version numbers.

Alternative or Verification Steps

• After applying fixes, restart the SQL Server service normally and query SELECT compatibility_level, state_desc FROM sys.databases; to confirm the database shows an ONLINE state with no pending upgrade flags.
• If the issue persists, restore the entire instance from a verified pre-upgrade backup and reapply the Cumulative Update in a controlled maintenance window, ensuring no concurrent processes lock the database files during the upgrade sequence.

Troubleshooting Guide: SQL Server Error 947

Why This Error Happens

Error 947 occurs when SQL Server encounters an issue during the shutdown or closing phase of a database, typically triggered by an underlying I/O failure, a corrupted transaction log, or insufficient disk space preventing the engine from flushing final pages to disk. This error is rarely a root cause itself—it is almost always a secondary message masking a prior critical error (such as a 823, 824, or 9002) that occurred during the checkpoint process. In some cases, abrupt termination of a SPID or forced service stop while the database is mid-transaction can also trigger this message.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Preceding Errors

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for errors immediately preceding Error 947, specifically Error 823 (I/O hardware failure) or Error 9002 (transaction log full).
• Alternatively, check the raw log file located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Verify Disk Space and Volume Health

• Check the volume hosting your .mdf and .ldf files for available free space using Get-Volume in PowerShell.
• Ensure the disk is not nearing capacity, as SQL Server cannot write the final checkpoint during shutdown if the log or data drive is full.
• Run chkdsk C:\ /f (replace with the relevant drive letter) during a maintenance window to rule out underlying file system corruption.

Step 3: Check for Orphaned Processes or Locks

• Run the following query to identify blocking or orphaned sessions tied to the database attempting to close:

  SELECT session_id, status, blocking_session_id, wait_type 
  FROM sys.dm_exec_requests 
  WHERE database_id = DB_ID('YourDatabaseName');

• Kill any zombie SPIDs using KILL <session_id>; if they are holding the database in a transitional state.

Step 4: Run DBCC CHECKDB for Corruption

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to scan for physical consistency errors.
• If corruption is found in the transaction log specifically, you may need to set the database to EMERGENCY mode and perform a log rebuild as a last resort.

Step 5: Restart the SQL Server Service

• Open the Services console (services.msc) on Windows Server 2025.
• Restart the SQL Server (MSSQLSERVER) service to clear any hung threads associated with the previous closing attempt.
• Monitor the error log immediately upon restart to confirm the database transitions cleanly to an ONLINE state.

Alternative or Verification Steps

• Verify the Fix: Run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the state reads ONLINE rather than RECOVERY_PENDING or SUSPECT.
• Alternative Solution: If the database remains stuck, detach and re-attach the database files via sp_detach_db and CREATE DATABASE... FOR ATTACH, ensuring you have a verified backup beforehand in case the log file requires rebuilding.

Troubleshooting Guide: Error 948 – Database Version Downgrade Not Supported

Why This Error Happens

This error occurs when you attempt to attach or restore a database that was created or last written to by a newer version of SQL Server (such as SQL Server 2025) onto an older or mismatched instance that does not recognize that internal database version number. SQL Server embeds a specific internal version ID into the database file header each time the engine upgrades the schema catalog, and this ID is strictly forward-compatible only. Because Microsoft does not support downgrading database files, the destination instance rejects the mount operation entirely rather than risking data corruption.

Step-by-Step Resolution Guide

Step 1: Verify the Source and Target SQL Server Versions

• Run SELECT @@VERSION; on both the original server and the destination server to confirm build numbers.
• Check the specific internal database version by reviewing the error message details (e.g., “version 957” vs “version 904”).
• Confirm whether the destination server is actually running SQL Server 2025 or an earlier release (2022, 2019, etc.) by checking Help > About in SSMS or querying SELECT SERVERPROPERTY('ProductVersion');.

Step 2: Upgrade the Destination Instance (Primary Fix)

• Since downgrading is not supported, the only direct fix is to upgrade the destination SQL Server instance to match or exceed the source version.
• Download the matching SQL Server 2025 Setup media and run Setup.exe from an elevated Command Prompt on Windows Server 2025 / Windows 11.
• Select Upgrade during the installation wizard and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog to ensure the internal database version aligns.
• Reboot the server if prompted, then attempt the RESTORE DATABASE or sp_attach_db operation again.

Step 3: Export Data Using a Version-Agnostic Method (If Upgrade Isn’t Possible)

• If you cannot upgrade the destination server, you cannot physically attach the .mdf/.ldf files; instead, extract the data logically.
• On the newer (source) server, right-click the database in SSMS and select Tasks > Generate Scripts to export schema and data as a .sql file compatible with older engines.
• Alternatively, use the Export Data-tier Application wizard to create a .bacpac file, then import it into the older instance via Import Data-tier Application.
• For large databases, use the bcp utility to bulk copy table data into flat files, then re-import them on the target server.

Step 4: Validate File Paths and Permissions Post-Upgrade

• After upgrading, ensure the SQL Server service account has Full Control permissions on the folder containing the .mdf and .ldf files (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Re-attempt the attach operation using SSMS Object Explorer > Databases > Attach, browsing to the correct file path.

Alternative or Verification Steps

• Verify the fix: Run RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourDB.bak'; followed by a full RESTORE DATABASE command; successful completion without Error 948 confirms version compatibility.
• Alternative workaround: If upgrading is not feasible, spin up a temporary SQL Server 2025 instance (even a free Developer Edition) solely to export the data via scripts or BACPAC, then decommission it once migration to the older server is complete.

Troubleshooting Guide: SQL Server Error 949 – tempdb Skipped

Why This Error Happens

Error 949 occurs when SQL Server 2025 cannot initialize tempdb during startup, causing the database engine to skip it while still allowing the instance to come online in a limited state. This is typically caused by insufficient disk space on the volume hosting tempdb’s data or log files, incorrect NTFS permissions for the SQL Server service account, or tempdb file paths that no longer exist due to a moved or renamed drive. In some cases, corrupted tempdb data/log files from an unclean shutdown can also prevent proper initialization.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File Paths

• Open File Explorer or run dir on the drive hosting tempdb (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Confirm the volume has at least 10-15% free space; tempdb will fail to autogrow if the disk is full.
• Check SQL Server Configuration Manager or the registry to confirm the configured tempdb file paths match actual file locations.

Step 2: Check NTFS Permissions for the SQL Service Account

• Right-click the tempdb data/log directory, select Properties > Security.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Full Control or at least Modify permissions.
• Apply changes and restart the SQL Server service via services.msc or PowerShell:
• Restart-Service MSSQLSERVER

Step 3: Start SQL Server in Minimal Configuration Mode

• Open an elevated Command Prompt and start the instance with the -f flag to bypass normal tempdb initialization:
• NET START MSSQLSERVER /f
• Connect via SQLCMD and manually relocate tempdb files using ALTER DATABASE tempdb MODIFY FILE to point to a valid, writable path with sufficient space.
• Stop the instance and restart it normally to confirm tempdb initializes correctly.

Step 4: Rebuild tempdb Files if Corrupted

• If tempdb files are corrupted beyond repair, delete the existing tempdb.mdf and templog.ldf files while the instance is stopped.
• Use the -T3608 trace flag temporarily to allow SQL Server to start without autoloading tempdb, then manually recreate it using CREATE DATABASE tempdb ON (...) with explicit file paths.
• Remove the trace flag once tempdb has been successfully rebuilt.

Alternative or Verification Steps

• After restarting the service, query SELECT name, state_desc FROM sys.databases WHERE name = 'tempdb'; to confirm the state is ONLINE.
• If the issue persists, check the SQL Server Error Log (ERRORLOG file in the LOG directory) for additional context around the 949 error and cross-reference with Windows Event Viewer under Application logs for disk or permission-related entries.

Troubleshooting Guide: SQL Server Error 945

Why This Error Happens

Error 945 occurs when SQL Server 2025 attempts to bring a database online but cannot access one or more of its underlying data (.mdf/.ndf) or log (.ldf) files. This is typically caused by the SQL Server service account lacking NTFS permissions on the file path, the files being corrupted or moved, or the host volume running out of disk space during an autogrowth operation. In some cases, this also surfaces when the database was marked suspect due to an abrupt shutdown or storage-level I/O failure on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify File Existence and Disk Space

• Open File Explorer or PowerShell and confirm the .mdf, .ndf, and .ldf files exist at the path specified in SQL Server Management Studio (SSMS) under Database Properties > Files.
• Check available disk space on the volume using Get-PSDrive in PowerShell; ensure at least 15-20% free space remains.
• Review the SQL Server ERRORLOG located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact file path causing the failure.

Step 2: Correct File and Folder Permissions

• Right-click the database folder (e.g., D:\SQLData\) and select Properties > Security.
• Ensure the SQL Server service account (commonly NT SERVICE\MSSQLSERVER or a custom domain account) has Full Control permissions.
• Apply changes recursively using PowerShell: icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER.

Step 3: Bring the Database Back Online

• Open SSMS and attempt to set the database back online: ALTER DATABASE [YourDB] SET ONLINE;.
• If the database remains in a “Recovery Pending” or “Suspect” state, run DBCC CHECKDB('YourDB') to assess corruption.
• If files were moved or renamed, detach and reattach the database using sp_detach_db and CREATE DATABASE ... FOR ATTACH, pointing to the correct file paths.

Step 4: Restore from Backup if Files Are Unrecoverable

• If data files are corrupted beyond repair, restore the most recent full backup using RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB.bak' WITH RECOVERY.
• Apply any subsequent differential or transaction log backups in sequence to minimize data loss.

Alternative or Verification Steps

• Verify the fix by querying sys.databases and confirming the state_desc column shows ONLINE for the affected database.
• If the issue persists, check Windows Event Viewer (Application and System logs) for underlying disk or storage controller errors that may indicate a hardware-level fault requiring a storage administrator’s intervention.

Troubleshooting Guide: SQL Server Error 946 — Database Version Mismatch

Why This Error Happens

Error 946 occurs when SQL Server 2025 attempts to attach or open a database file (.mdf/.ldf) that was created or last used by a newer version of SQL Server than the instance currently running, or when the internal database version number is incompatible with the engine’s supported version range. This commonly happens after restoring a backup from a newer SQL Server build onto an older instance, attaching detached files across mismatched versions, or incomplete upgrade processes that left the database in a pre-upgrade state. The engine refuses to mount the database because it cannot guarantee metadata and storage format compatibility, which could otherwise lead to corruption.

Step-by-Step Resolution Guide

Step 1: Verify the SQL Server Build and Database Version

• Open SQL Server Management Studio (SSMS) and run SELECT @@VERSION; to confirm the exact build number and edition of your instance.
• Check the database’s internal version by reviewing SQL Server error logs for the specific version number referenced in Error 946 (e.g., “version 950”).
• Cross-reference the version number against Microsoft’s official SQL Server internal database version chart to identify which SQL Server release created the file.

Step 2: Confirm Source and Target SQL Server Versions Match

• Identify whether the database was backed up, detached, or copied from a newer instance (e.g., SQL Server 2025 CTP/RC build vs. your current RTM or older Cumulative Update).
• If the database originated from a newer build, you must restore it onto an instance of equal or higher version — downgrading is not supported.
• Navigate to Help > About in SSMS or run SELECT SERVERPROPERTY('ProductVersion'); to compare build numbers precisely.

Step 3: Apply the Latest Cumulative Update or Service Pack

• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog to ensure your instance matches or exceeds the source database version.
• Run the installer as Administrator and select the correct instance during the update wizard.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER after the update completes.

Step 4: Restore or Attach the Database on a Compatible Instance

• If the version gap cannot be resolved via updates, provision a new SQL Server 2025 instance using the same or newer build as the source environment.
• Use RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\YourDB.bak' WITH MOVE 'YourDB_Data' TO 'C:\SQLData\YourDB.mdf', MOVE 'YourDB_Log' TO 'C:\SQLData\YourDB_log.ldf'; to properly restore onto the compatible instance.
• For detached files, use sp_attach_db or the SSMS “Attach” GUI only after confirming version compatibility, pointing to the correct .mdf and .ldf file paths (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).

Step 5: Validate Database Compatibility Level Post-Upgrade

• Once attached successfully, run SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDB'; to confirm the compatibility level matches SQL Server 2025 standards.
• If needed, update it using ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170; to align with the current engine version.

Alternative or Verification Steps

• Confirm the fix by running DBCC CHECKDB('YourDB') WITH NO_INFOMSGS; to ensure the database is fully accessible and free of corruption after the version alignment.
• If upgrading the instance is not feasible, contact the database owner to request a backup generated specifically in a format compatible with your current SQL Server build, or use the Generate Scripts wizard to recreate schema and data on your existing version.

Troubleshooting Guide: Error 943 – Database Version Mismatch in SQL Server 2025

Why This Error Happens

Error 943 occurs when SQL Server attempts to attach or restore a database that was created or last written to by a newer version of SQL Server than the instance currently running. Each SQL Server release stamps databases with an internal version number, and older or mismatched server builds cannot interpret data structures, system catalogs, or metadata formats introduced in later versions. This typically happens after moving a database file (.mdf/.ldf) from a server running a newer SQL Server build, a newer Cumulative Update (CU), or a different major release, to one running an older or unpatched instance.

Step-by-Step Resolution Guide

Step 1: Identify the Source and Target SQL Server Versions

• Run SELECT @@VERSION; on both the source and target servers to compare build numbers.
• Check the database’s internal version using DBCC CHECKDB logs or by reviewing the error message, which lists both the database version number and the current server version number.
• Confirm whether the source server is running a newer Cumulative Update (CU) or a different major release (e.g., SQL Server 2025 vs. SQL Server 2022).

Step 2: Update the Target SQL Server Instance

• Navigate to the Microsoft Download Center or use SQL Server Setup to check for the latest Cumulative Update matching the source version.
• Download and install the required CU or Service Pack on the target server (e.g., SQLServer2025-KB5XXXXXX-x64.exe).
• Restart the SQL Server service via services.msc or run NET STOP MSSQLSERVER followed by NET START MSSQLSERVER after the update completes.
• Re-run SELECT @@VERSION; to confirm the target server build now matches or exceeds the source database version.

Step 3: Retry the Attach or Restore Operation

• Attempt the original operation again, e.g., RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE;
• For attach operations, use CREATE DATABASE [YourDB] ON (FILENAME = 'C:\SQLData\YourDB.mdf') FOR ATTACH;
• Monitor the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any residual compatibility warnings.

Step 4: Use Export/Import as a Fallback (If Upgrade Isn’t Feasible)

• If upgrading the target instance is not possible, use the SQL Server Import and Export Wizard to transfer data at the schema/table level instead of attaching the raw files.
• Alternatively, generate a BACPAC file from the source server using SQL Server Management Studio (SSMS) via Tasks > Export Data-tier Application, then import it on the target server using Tasks > Import Data-tier Application.

Alternative or Verification Steps

• Verify the fix by successfully running sp_helpdb YourDB; on the target instance without errors, and confirm the database status shows “ONLINE” in SSMS Object Explorer.
• If patching the target server is restricted by organizational policy, provision a temporary SQL Server 2025 instance matching the source build solely to export data via BACPAC or bcp utility, then import it into the older target environment.

Why This Error Happens

Error 944 occurs when SQL Server 2025 detects that a database’s internal version number is lower than the current engine version and attempts an automatic upgrade during the startup or attach process. This typically happens after an in-place upgrade from an older SQL Server version, a restore from an older backup, or when the database was left in an inconsistent state due to insufficient disk space, interrupted shutdown, or corrupted system catalog pages. Because this conversion process requires exclusive write access to system tables, it can fail or stall if permissions, disk I/O, or file integrity issues prevent SQL Server from completing the version upgrade.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File Accessibility

• Check that the drive hosting the .mdf and .ldf files has at least 20% free space, since version conversion requires temporary write operations.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ (adjust instance folder name as needed) and confirm the database files are not marked Read-Only.
• Run Get-Acl in PowerShell against the data files to confirm the SQL Server service account has Full Control permissions.

Step 2: Check SQL Server Error Logs for Conversion Status

• Open SQL Server Management Studio (SSMS) and connect to the instance, or review logs directly at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for subsequent messages following Error 944, such as “Converting database… to the current version” completing successfully or failing with a secondary error (e.g., 5172 or 823, indicating file corruption).
• If the log shows the conversion is stuck, restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to retrigger the upgrade attempt cleanly.

Step 3: Run Database Consistency Checks

• Set the database to single-user mode using: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Execute DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect and report any corruption blocking the version upgrade.
• If corruption is found, review repair options (REPAIR_REBUILD first, REPAIR_ALLOW_DATA_LOSS only as a last resort).

Step 4: Restore From a Known-Good Backup (If Conversion Fails)

• If the database remains stuck in “In Recovery” or “Suspect” mode after conversion attempts, restore the last verified full backup using SSMS or RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB.bak' WITH REPLACE;
• Ensure the backup originates from a supported source version (SQL Server 2016 or later) since SQL Server 2025 cannot upgrade databases from versions older than two major releases.

Alternative or Verification Steps

• After remediation, run SELECT DATABASEPROPERTYEX('YourDB', 'Version'); and confirm it matches the expected internal version for SQL Server 2025 (904 or higher).
• If the issue persists, detach the database, copy files to a fresh SQL Server 2025 instance on a test server, and re-attach using CREATE DATABASE [YourDB] ON (FILENAME = '...') FOR ATTACH; to isolate whether the problem is instance-specific or data-specific.

Troubleshooting Guide: Error 939 – Persistent Version Store Initialization Failed

Why This Error Happens

Error 939 occurs when SQL Server 2025 cannot initialize the Persistent Version Store (PVS) for a database using Accelerated Database Recovery (ADR), typically because the underlying filegroup lacks sufficient disk space or the storage volume has I/O restrictions. This can also happen if the PVS filegroup files are corrupted, offline, or if the service account lacks the necessary NTFS permissions to read/write to the file path. Additionally, pre-existing errors in the transaction log (such as a prior unclean shutdown) can prevent the version store from reconstructing its internal mapping tables.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for errors immediately preceding Event ID 939, such as Error 5120 (file access denied) or Error 1101/1105 (out of disk space).
• Alternatively, review the raw log file located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.

Step 2: Verify Disk Space and Volume Health

• Check the drive hosting your database files (e.g., D:\SQLData) to ensure at least 20% free space is available for PVS growth.
• Run chkdsk D: /f via an elevated Command Prompt on Windows Server 2025 to rule out underlying volume corruption.
• Resize the volume or move the PVS filegroup to a drive with more capacity using ALTER DATABASE [YourDB] MODIFY FILE.

Step 3: Validate Service Account Permissions

• Open File Explorer, navigate to the SQL Data directory, right-click the folder, and select Properties > Security.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control over the data and log directories.
• Restart the SQL Server service via services.msc after applying permission changes.

Step 4: Disable and Re-enable Accelerated Database Recovery (ADR)

• Connect via SSMS or sqlcmd and run the following T-SQL command to turn off ADR temporarily:
• ALTER DATABASE [YourDB] SET ACCELERATED_DATABASE_RECOVERY = OFF;
• Bring the database back online, then re-enable ADR to force a clean re-initialization of the version store:
• ALTER DATABASE [YourDB] SET ACCELERATED_DATABASE_RECOVERY = ON;

Step 5: Restore from Backup if Corruption Persists

• If the PVS remains corrupted after re-initialization, take the database offline.
• Restore the most recent clean full backup using RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB.bak' WITH RECOVERY;.

Alternative or Verification Steps

• Verify the Fix: Query the DMV sys.dm_tran_persistent_version_store_stats to confirm the database is actively tracking and aging out versions without error.
• Alternative Solution: If ADR continues to fail due to a systemic storage issue, migrate the database files to a new volume entirely and update the file paths using ALTER DATABASE... MODIFY FILE (NAME = ..., FILENAME = ...) before restarting the SQL Server service.

Troubleshooting Guide: SQL Server Error 941

Why This Error Happens

Error 941 occurs when SQL Server has registered a database in its metadata but has not completed the recovery process required to bring it online, often because the database is stuck in a transitional state (e.g., RESTORING, RECOVERY_PENDING, or SUSPECT). This typically happens after an unexpected shutdown, a failed or interrupted restore operation, or corruption in the primary data file (.mdf) that prevents the Storage Engine from finishing crash recovery. It can also occur if the SQL Server service account lacks the necessary file system permissions to access the database files during startup.

Step-by-Step Resolution Guide

Step 1: Check the Current Database State

• Open SQL Server Management Studio (SSMS) or connect via sqlcmd.
• Run the following query to check the state description:

  SELECT name, state_desc, recovery_model_desc 
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';

• Look specifically for states like RECOVERY_PENDING, SUSPECT, or OFFLINE.

Step 2: Review the SQL Server Error Log

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries corresponding to the specific database ID mentioned in the Error 941 message.
• Look for underlying I/O errors (such as Error 823 or 824) that indicate physical corruption on the disk, or permission-denied messages regarding the .mdf/.ldf files.

Step 3: Verify File System Permissions

• Right-click the folder containing your database files (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) and select Properties > Security.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control over this directory.
• If permissions were recently changed (e.g., due to a Windows Server 2025 security policy update), reset them to the default inherited permissions and restart the SQL Server service via services.msc.

Step 4: Force Recovery or Restore the Database

• If the database remains in RECOVERY_PENDING due to a missing transaction log, attempt to bring it online using emergency mode:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  DBCC CHECKDB('YourDatabaseName');
  ALTER DATABASE YourDatabaseName SET ONLINE;

• If the database is marked SUSPECT due to corruption, restore the most recent clean backup using the Restore Database wizard in SSMS.

Alternative or Verification Steps

• Verify the Fix: Re-run the SELECT name, state_desc FROM sys.databases query to confirm the state_desc column now reads ONLINE.
• Alternative Solution: If EMERGENCY mode fails to resolve the corruption, detach the database (sp_detach_db) and attempt to re-attach the .mdf file manually using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG to regenerate a corrupted transaction log file.

Troubleshooting Guide: SQL Server Error 942 — Database Cannot Be Opened Because It Is Offline

Why This Error Happens

Error 942 occurs when SQL Server attempts to access a database whose state is explicitly set to OFFLINE, either manually by an administrator, through a failed restore/detach operation, or due to an automated maintenance script. This can also happen if the database was taken offline prior to a server restart and never brought back online, or if the underlying data/log files were moved or became inaccessible while the database was offline, preventing a clean reactivation. Since SQL Server will not auto-recover an offline database, manual intervention is required to change its state back to ONLINE.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or connect via sqlcmd on Windows Server 2025 / Windows 11.
• Run the following query to confirm the offline status:

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Confirm state_desc returns OFFLINE rather than ONLINE, RESTORING, or SUSPECT.

Step 2: Check File System Accessibility

• Navigate to the default data directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Confirm the .mdf and .ldf files for the database still exist and have not been moved, renamed, or deleted.
• Verify that the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read/Write/Modify permissions on these files via File Properties > Security.
• If files are missing, restore them from backup before proceeding.

Step 3: Bring the Database Back Online

• Execute the following T-SQL command to attempt reactivation:

  ALTER DATABASE YourDatabaseName SET ONLINE;

• If the command hangs or fails, check the SQL Server Error Log (ERRORLOG file in the LOG folder) for underlying I/O or corruption errors.
• If files were inaccessible, correct permissions or restore the files, then retry the command.

Step 4: Repair Corrupted Files if Database Remains Offline

• If Step 3 fails due to corruption, set the database to EMERGENCY mode first:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;

• Run a consistency check:

  DBCC CHECKDB (YourDatabaseName);

• If errors are found, perform a repair (as a last resort, since this may cause data loss):

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB (YourDatabaseName, REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 5: Restart SQL Server Service (If Needed)

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Re-run the state verification query from Step 1 to confirm the database is now ONLINE.

Alternative or Verification Steps

• Confirm the fix by querying sys.databases again — state_desc should now show ONLINE, and the database should be accessible via SSMS Object Explorer without errors.
• If the database still won’t come online due to severe corruption, restore it from the most recent valid backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath' WITH RECOVERY; as an alternative resolution path.

Troubleshooting Guide: Error 935 – Script Level Downgrade Conflict

Why This Error Happens

This error occurs when SQL Server attempts to attach or restore a database that was previously upgraded to a higher internal “script level” (a version marker tied to system object schema changes) by a newer build of SQL Server, and the current instance running is an older or different build that does not recognize that elevated script level. Essentially, the database metadata indicates it has already processed upgrade scripts that the connecting instance has never applied, creating a version mismatch that SQL Server blocks to prevent system catalog corruption. This commonly happens after a failed Cumulative Update (CU) rollback, a side-by-side instance migration, or when moving a database file from a newer SQL Server 2025 build to an older one.

Step-by-Step Resolution Guide

Step 1: Verify the Source and Target Build Versions

• Open SQL Server Management Studio (SSMS) or a query window and run SELECT @@VERSION; on both the source and destination servers.
• Compare the specific build numbers (e.g., 16.0.x.x) to confirm the destination instance is indeed older than the source.
• Check the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER for the exact Event ID 935 timestamp to correlate with the attach/restore attempt.

Step 2: Update the SQL Server 2025 Instance to Match or Exceed the Source Build

• Navigate to the Microsoft Update Catalog or use SQL Server Setup to download the latest Cumulative Update (CU) matching or exceeding the source server’s build.
• Run the update executable as Administrator: SQLServer2025-KB XXXXXXX-x64.exe /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /InstanceName=MSSQLSERVER.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER once the patch completes.

Step 3: Re-Attempt the Database Attach or Restore Operation

• In SSMS, right-click Databases > Attach, and browse to the original .mdf and .ldf file paths (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Alternatively, run RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\YourDB.bak' WITH RECOVERY; in a new query window.
• Confirm no error 935 appears in the Messages tab after execution.

Step 4: Export Data Instead of Attaching (If Patching Is Not Feasible)

• If upgrading the instance is not an option, use the Generate Scripts wizard in SSMS to export schema and data as T-SQL INSERT statements.
• Alternatively, use the Import/Export Wizard to transfer data to a new, compatible database on the older instance without relying on binary file attachment.

Alternative or Verification Steps

• Run SELECT compatibility_level, is_cleanly_shutdown FROM sys.databases WHERE name = 'YourDB'; to confirm the database is online and its compatibility level aligns with the host instance.
• If the error persists after patching, check SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%Setup%'; to validate the installed build matches the required internal script level, and consider rebuilding the database from a native backup taken on a compatible build instead of file-level attachment.

Troubleshooting Guide: SQL Server Error 938 – Unsupported Database Version

Why This Error Happens

Error 938 occurs when a database’s internal version number is higher than what the currently installed SQL Server engine can recognize and process. This typically happens when a database created or upgraded by a newer version of SQL Server (or a newer Cumulative Update) is attached, restored, or accessed by an older SQL Server binary. It can also occur if a downgrade was attempted after an in-place upgrade, leaving the database schema at a version level incompatible with the rolled-back engine.

Step-by-Step Resolution Guide

Step 1: Identify the Source and Target Version Mismatch

• Check the SQL Server Error Log for the exact line containing “target database version %d is not supported by the current code version %d.”
• Cross-reference the reported version number against Microsoft’s internal database version chart (e.g., version 904+ corresponds to SQL Server 2022/2025 builds).
• Run SELECT @@VERSION; on the instance attempting to read the database to confirm the current engine build.
• Verify whether the database was recently restored, attached, or migrated from a different server with a newer SQL Server build.

Step 2: Update SQL Server 2025 to the Latest Cumulative Update

• Navigate to the Microsoft Update Catalog or SQL Server Update Center and download the latest Cumulative Update (CU) for SQL Server 2025.
• Run the installer as Administrator: SQLServer2025-KB xxxxxxx-x64.exe /quiet /IAcceptSQLServerLicenseTerms.
• After installation, restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER.
• Re-run SELECT SERVERPROPERTY('ProductVersion'); to confirm the build number has incremented to match or exceed the source database’s requirement.

Step 3: Restore or Reattach the Database Correctly

• If the database was copied from a newer instance, do not attempt to force-attach it to an older build; instead, restore a .bak file taken from a compatible version.
• Use RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backups\YourDB.bak' WITH REPLACE; only after confirming version compatibility.
• If using sp_attach_db or CREATE DATABASE ... FOR ATTACH, ensure the .mdf/.ldf files originated from a SQL Server build equal to or lower than the target instance.

Step 4: Verify Windows Server 2025 / Windows 11 Patch Level

• Open Settings > Windows Update and ensure all pending OS-level updates are installed, as some SQL Server CUs depend on updated OS components (e.g., .NET runtime, VC++ Redistributables).
• Run sconfig (on Server Core) or check winver to confirm the OS build aligns with SQL Server 2025 system requirements.
• Reboot the server fully after applying updates: shutdown /r /t 0.

Alternative or Verification Steps

• After applying the CU, attempt to bring the database online via SQL Server Management Studio (SSMS) and confirm no Error 938 appears in Object Explorer > Databases.
• If the issue persists, restore the database to a SQL Server instance matching the original source version, then use the Generate Scripts wizard or BACPAC export/import to migrate data manually to the older instance instead of a direct file-level restore.

Troubleshooting Guide: Error 933 – Database Cannot Be Started Due to Edition Incompatibility

Why This Error Happens

This error occurs when a database that was created or last running on a higher edition of SQL Server (such as Enterprise or Developer) uses features unavailable in the current edition (such as Standard or Web). Common culprits include Enterprise-only features like Transparent Data Encryption (TDE), table/index partitioning, columnstore enhancements, or in-memory OLTP objects. When the database is attached, restored, or started on a downgraded or differently licensed instance, SQL Server 2025 detects these incompatible objects during recovery and refuses to bring the database online.

Step-by-Step Resolution Guide

Step 1: Identify the SQL Server Edition and the Database’s Required Features

• Run SELECT SERVERPROPERTY('Edition'); on the target instance to confirm the current edition.
• Check the error log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the specific feature name flagged as unsupported.
• Use sys.dm_db_persisted_sku_features (if the database can be queried from a compatible instance) to list Enterprise-only features in use.

Step 2: Upgrade the Edition or Remove Incompatible Features

• If upgrading is feasible, run Setup.exe /ACTION=EditionUpgrade /INSTANCEID=MSSQLSERVER /PID=<your-enterprise-product-key> from the SQL Server 2025 installation media to move to Enterprise or Developer edition.
• If upgrading is not an option, restore the database on a compatible edition instance first, then disable the offending feature (e.g., run ALTER DATABASE [YourDB] SET ENCRYPTION OFF; for TDE, or rebuild partitioned tables as standard heaps/clustered indexes).
• Re-script and remove any In-Memory OLTP filegroups or memory-optimized tables, as these are edition-restricted.

Step 3: Re-Export and Migrate the Database Cleanly

• On the compatible edition instance, generate a clean backup using BACKUP DATABASE [YourDB] TO DISK = 'D:\Backups\YourDB_Clean.bak'; after removing incompatible features.
• Copy the .bak file to the target Windows Server 2025 machine and restore it using RESTORE DATABASE [YourDB] FROM DISK = 'D:\Backups\YourDB_Clean.bak' WITH REPLACE;.
• Alternatively, use the Generate Scripts wizard in SSMS to script out schema and data, excluding Enterprise-specific options, then execute the script against the target instance.

Step 4: Validate Instance Licensing and Product Key

• Open SQL Server Installation Center and verify the Product Key tab reflects the intended edition (Standard, Enterprise, etc.).
• If the server was provisioned with an evaluation edition that expired and reverted to a restricted tier, reapply a valid license key via Setup.exe in maintenance mode.

Alternative or Verification Steps

• After applying fixes, attempt to bring the database online with ALTER DATABASE [YourDB] SET ONLINE; and confirm no Error 933 appears in the SQL Server Error Log.
• As a quick verification, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS; to ensure the database is structurally healthy and fully accessible after the edition correction.

Troubleshooting Guide: SQL Server Error 934 – Change Data Capture Edition Mismatch

Why This Error Happens

This error occurs because the database being restored or attached has Change Data Capture (CDC) enabled, but the target instance of SQL Server 2025 is running an edition (such as Express, Web, or certain Standard builds) that does not support CDC. SQL Server performs an edition-compatibility check during database load, and since CDC metadata exists in the source database, the engine refuses to bring the database online to prevent inconsistent feature states. This commonly happens when migrating a database from Enterprise or Developer edition down to a lower edition that lacks CDC licensing support.

Step-by-Step Resolution Guide

Step 1: Verify the Target Edition’s CDC Support

• Run SELECT SERVERPROPERTY('Edition'); on the destination SQL Server 2025 instance.
• Confirm whether the edition is Express, Web, or Standard (pre-2016 SP1 equivalent policies); only Enterprise, Developer, and Standard (2025 licensing tier with CDC enabled) support CDC.
• If the edition does not support CDC, you must either upgrade the edition or strip CDC from the backup before restoring.

Step 2: Restore the Database Without the KEEP_CDC Option

• Open SQL Server Management Studio (SSMS) or a sqlcmd session connected to the target instance.
• Run the restore command explicitly omitting KEEP_CDC:

  RESTORE DATABASE [YourDatabaseName]
  FROM DISK = 'C:\Backups\YourDatabaseName.bak'
  WITH MOVE 'YourDatabaseName_Data' TO 'D:\SQLData\YourDatabaseName.mdf',
  MOVE 'YourDatabaseName_Log' TO 'D:\SQLLogs\YourDatabaseName.ldf',
  REPLACE;

• This forces SQL Server to disable and remove CDC metadata during the restore process instead of preserving it.

Step 3: Disable CDC on the Source Database (If Restore Still Fails)

• If the restore still errors out, disable CDC on the source database before taking a new backup:

  USE [YourDatabaseName];
  EXEC sys.sp_cdc_disable_db;

• Take a fresh full backup after disabling CDC:

  BACKUP DATABASE [YourDatabaseName]
  TO DISK = 'C:\Backups\YourDatabaseName_NoCDC.bak';

• Transfer this new backup file to the target server and re-attempt the restore.

Step 4: Upgrade the Edition (If CDC Functionality Is Required)

• If your application depends on CDC, upgrade the target instance to an edition that supports it (Enterprise, Developer, or licensed Standard with CDC enablement in SQL Server 2025).
• Use the SQL Server Installation Center > Maintenance > Edition Upgrade wizard on Windows Server 2025 to perform an in-place edition upgrade without reinstalling.

Step 5: Validate File and Service Permissions

• Ensure the SQL Server service account has read/write access to the backup and data directories (C:\Backups, D:\SQLData, D:\SQLLogs).
• Check Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) for any correlated permission-denied entries that might compound the restore failure.

Alternative or Verification Steps

• After the restore completes, run SELECT is_cdc_enabled FROM sys.databases WHERE name = 'YourDatabaseName'; — a value of 0 confirms CDC was successfully removed and the database loaded correctly.
• If CDC is required long-term, consider provisioning the destination server with SQL Server 2025 Enterprise or Developer edition in a test environment first to validate compatibility before production cutover.

Troubleshooting Guide: SQL Server Error 931 – Database Fragment Reference Failure

Why This Error Happens

Error 931 occurs when SQL Server’s internal storage engine attempts to access a database fragment (a logical file allocation unit) that is missing from the database’s metadata or filegroup mapping. This is typically caused by database corruption, an incomplete or interrupted file growth/shrink operation, or a mismatch between the database’s system catalog and the actual physical file structure on disk. In rare cases, this can also result from improperly restoring a database backup that was taken mid-transaction or from a storage-level failure (SAN/disk corruption) that affected one or more secondary data files.

Step-by-Step Resolution Guide

Step 1: Verify Database and File Integrity

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption.
• Check SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related 5172 or 823/824 I/O errors occurring around the same timestamp.
• Confirm all .mdf, .ndf, and .ldf files listed in sys.master_files physically exist in their expected directories.

Step 2: Take the Database Offline and Attempt Repair

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); only if a recent backup is unavailable, as this may discard corrupted fragment data.
• Switch back to multi-user mode once repair completes: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Restore From a Known-Good Backup

• Navigate to your backup directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\) and locate the last verified full backup.
• Execute RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Path\To\Backup.bak' WITH REPLACE, RECOVERY;
• Apply any subsequent transaction log backups in sequence using WITH NORECOVERY until the final log file, which should use WITH RECOVERY.

Step 4: Check Underlying Storage Health

• Open Event Viewer on Windows Server 2025/Windows 11 and inspect the System log for Disk or Ntfs warnings (Event ID 7, 51, or 153) coinciding with the SQL error.
• Run chkdsk C: /f /r (or the appropriate drive letter hosting your SQL data files) during a scheduled maintenance window to rule out physical disk-level corruption.

Alternative or Verification Steps

• After remediation, re-run DBCC CHECKDB with no repair options to confirm zero allocation or consistency errors are reported.
• If the error persists post-restore, consider migrating the database to a new filegroup/file structure via CREATE DATABASE ... FOR ATTACH with freshly exported data, bypassing the corrupted fragment metadata entirely.

Troubleshooting Guide: Error 932 – Change Tracking Not Supported by SQL Server Edition

Why This Error Happens

This error occurs because the database being attached or restored has Change Tracking enabled, but the current instance of SQL Server 2025 is running on an edition (such as Express or Web Edition) that does not support this feature. SQL Server performs an edition-compatibility check during database load, and since Change Tracking metadata exists in the database schema, the engine blocks the load to prevent corruption or unsupported feature usage. This typically happens after migrating a database from Standard or Enterprise Edition down to a lower-tier edition.

Step-by-Step Resolution Guide

Step 1: Verify the Current SQL Server Edition

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check the installed edition:

  SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('ProductVersion') AS Version;

• Confirm whether the edition is Express, Web, or another edition that lacks Change Tracking support (only Standard, Enterprise, and Developer editions support it).

Step 2: Disable Change Tracking on the Source Database (Before Migration)

• If you still have access to the original higher-edition instance, disable Change Tracking before moving the database.
• Run these commands on the source server:

  ALTER DATABASE [YourDatabaseName] SET CHANGE_TRACKING = OFF;

• If tracking is enabled on specific tables, disable it there first:

  ALTER TABLE [YourTable] DISABLE CHANGE_TRACKING;

• Re-export or back up the database after disabling tracking.

Step 3: Upgrade to a Supported Edition (If Source Access Is Unavailable)

• If you cannot modify the source database, install or upgrade to SQL Server 2025 Standard, Enterprise, or Developer Edition on Windows Server 2025 / Windows 11.
• Use the SQL Server Installation Center to perform an Edition Upgrade:

  Setup.exe /ACTION=EditionUpgrade /INSTANCENAME=MSSQLSERVER /PID=<NewProductKey>

• Restart the SQL Server service via services.msc or:

  Restart-Service -Name 'MSSQLSERVER'

Step 4: Reattach or Restore the Database

• After confirming the edition supports Change Tracking, reattach or restore the database:

  RESTORE DATABASE [YourDatabaseName] FROM DISK = 'C:\Backups\YourDatabase.bak' WITH REPLACE;

• Monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) to confirm no further Error 932 entries appear.

Alternative or Verification Steps

• Verify the fix: Run SELECT * FROM sys.change_tracking_databases; to confirm Change Tracking status is correctly reflected and the database loads without error.
• Alternative fix: If upgrading the edition is not feasible, script out the database schema and data (excluding Change Tracking objects) using the Generate Scripts Wizard in SSMS, then import it into the lower-edition instance manually.

SQL Server 2025 Upgrade Failure: Error 928

Why This Error Happens

Error 928 occurs when SQL Server marks a database as suspect during the upgrade process, typically because the upgrade script cannot gain exclusive access to complete schema version changes. This is most often caused by corrupted system catalog metadata, insufficient disk space for tempdb growth during the conversion process, or a database that was already in an inconsistent state (due to prior unclean shutdowns) before the upgrade began. Additionally, interrupted upgrades on Windows Server 2025 can leave the database in a “Resource Pending” or “Suspect” state because the upgrade transaction could not be rolled forward or back.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Error Logs

• Open SQL Server Configuration Manager and confirm the service is running under the correct service account with sysadmin rights.
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to review the exact point of failure.
• Run SELECT name, state_desc FROM sys.databases; to identify which specific database triggered the Error 928 flag.

Step 2: Free Up Disk Space and Validate TempDB

• Check the drive hosting tempdb.mdf and templog.ldf (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA) to ensure at least 20% free space is available.
• If space is low, move tempdb files to a secondary drive using: ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLTemp\tempdb.mdf');
• Restart the SQL Server service via services.msc to reinitialize tempdb with available space.

Step 3: Run Emergency Mode Repair

• Set the affected database to emergency mode using: ALTER DATABASE [YourDB] SET EMERGENCY;
• Execute DBCC CHECKDB ([YourDB], REPAIR_ALLOW_DATA_LOSS) to force the catalog back into a consistent state (ensure you have a backup first).
• Set the database back to multi-user mode: ALTER DATABASE [YourDB] SET MULTI_USER;

Step 4: Re-run the Upgrade Setup

• Launch the SQL Server 2025 Setup wizard again from the installation media and select Repair rather than Install.
• Monitor the Summary.txt log located in C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log to ensure the schema version updates complete without rollback.

Alternative or Verification Steps

• Verification: Run SELECT SERVERPROPERTY('ProductVersion'); to confirm the build number matches the expected SQL Server 2025 release, and check sys.databases to ensure the state_desc column reads ONLINE for all user databases.
• Alternative: If REPAIR_ALLOW_DATA_LOSS fails to resolve the suspect flag, restore the database from the most recent pre-upgrade full backup and re-attempt the upgrade on a cloned instance before touching production data.

Troubleshooting Guide: SQL Server Error 929 — “Unable to Close a Database That Is Not Currently Open”

Why This Error Happens

This error typically occurs when a session attempts to close a database context that SQL Server’s internal tracking already considers detached, often due to an out-of-sync connection pool or an abrupt session termination that left stale database handles in memory. It can also surface when multiple concurrent processes issue conflicting USE or connection-close commands against the same database, causing a race condition in SQL Server’s resource management layer. In rarer cases, this points to transient corruption in the system catalog views (like sys.databases) or a mismatch between the client driver’s cached state and the actual server-side session state.

Step-by-Step Resolution Guide

Step 1: Verify Database and Session State

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run SELECT name, state_desc FROM sys.databases; to confirm the database is actually online and not in a RECOVERING, SUSPECT, or OFFLINE state.
• Cross-check active sessions using SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName'); to identify orphaned connections.

Step 2: Reset the Connection Pool and Application Session

• On the client/application server (Windows Server 2025 or Windows 11), restart the application pool or service that maintains the SQL connection (e.g., via IIS Manager or the relevant Windows Service in services.msc).
• If using ADO.NET or ODBC, ensure connection pooling is being cleared: force a SqlConnection.ClearPool() call or restart the application process to flush stale handles.
• Confirm the connection string points to the correct, currently attached database name (check for typos or case-sensitivity issues).

Step 3: Restart the SQL Server Service

• Open Services (services.msc) on Windows Server 2025.
• Locate SQL Server (MSSQLSERVER) or your named instance, right-click, and select Restart.
• Alternatively, run from an elevated PowerShell prompt:
• Restart-Service -Name 'MSSQLSERVER' -Force
• This clears any lingering internal session-to-database mappings causing the mismatch.

Step 4: Check for Corruption in System Catalogs

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out catalog or page-level corruption.
• If corruption is found, follow standard repair procedures (restore from backup is preferred over REPAIR_ALLOW_DATA_LOSS).

Step 5: Update SQL Server Cumulative Updates

• Check Windows Update or the official Microsoft Download Center for the latest Cumulative Update (CU) for SQL Server 2025.
• Install the update, as Error 929 has historically been linked to session-handling bugs patched in subsequent CU releases.

Alternative or Verification Steps

• After applying fixes, reconnect the application and attempt the original operation that triggered Error 929; monitor the SQL Server Error Log (Management > SQL Server Logs in SSMS) for recurrence.
• If the error persists, enable Extended Events to trace database_state_change events and capture the exact session ID and query triggering the close attempt for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 930 – Recovery Unit Reference Failure

Why This Error Happens

Error 930 occurs when SQL Server 2025’s storage engine attempts to access a recovery unit (a logical storage partition within a database file) that is missing, corrupted, or improperly indexed in the database’s internal metadata. This typically happens after an incomplete database restore, an abrupt shutdown during a multi-file operation, or corruption in the filestream/filegroup mapping tables. In most cases, the underlying .mdf, .ndf, or .ldf files are out of sync with what SQL Server’s internal catalog expects to find.

Step-by-Step Resolution Guide

Step 1: Verify Database and File Integrity

• Open SQL Server Management Studio (SSMS) and run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the scope of corruption.
• Check the SQL Server error log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related entries preceding Error 930.
• Confirm all data files referenced in sys.database_files physically exist on disk at their expected paths.

Step 2: Take the Database Offline and Restore from a Known-Good Backup

• Run ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE; to safely detach active connections.
• Restore the most recent verified backup using RESTORE DATABASE [YourDatabaseName] FROM DISK = 'D:\Backups\YourDatabase.bak' WITH REPLACE, RECOVERY;
• If no backup is available, attempt DBCC CHECKDB ('YourDatabaseName') WITH REPAIR_ALLOW_DATA_LOSS; only as a last resort, since this may discard corrupted pages.

Step 3: Rebuild Recovery Unit Metadata

• Place the database in single-user mode: ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKFILEGROUP to isolate whether the issue is confined to a specific filegroup rather than the entire database.
• Re-attach the database using sp_attach_db with explicit file paths to force SQL Server to rebuild its internal file/recovery unit mapping.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to Microsoft’s official SQL Server 2025 update catalog and confirm you’re running the latest Cumulative Update (CU), as early builds had a known metadata-indexing bug affecting multi-file databases.
• Apply the update via Setup.exe /ACTION=Patch /IAcceptSQLServerLicenseTerms /Quiet from an elevated PowerShell/Command Prompt session on Windows Server 2025.
• Restart the SQL Server service after patching: net stop MSSQLSERVER && net start MSSQLSERVER.

Alternative or Verification Steps

• Run SELECT * FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName'); to confirm all recovery units now map correctly to existing physical files.
• If the error persists after restore and patching, export critical data via bcp or the Import/Export Wizard into a newly created database, then decommission the corrupted one entirely.

Troubleshooting Guide: Error 926 – Database Marked SUSPECT by Recovery

Why This Error Happens

This error occurs when SQL Server 2025 attempts to run recovery on a database during startup and fails, typically due to corruption in the data or transaction log files, an abrupt shutdown, or storage-level I/O errors. It can also happen if the underlying disk volume was disconnected, ran out of space, or returned inconsistent data during the redo/undo phase of recovery. Because recovery cannot complete, SQL Server marks the database SUSPECT to prevent further damage or data loss.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs, or locate the physical log file at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for entries immediately preceding Error 926, such as Error 823, 824, or 9001, which indicate I/O or log corruption.
• Note the exact database name and file paths referenced in the log for use in later steps.

Step 2: Attempt to Bring the Database Online via Emergency Mode

• Open a new query window in SSMS and run:

  ALTER DATABASE [YourDBName] SET EMERGENCY;

• Verify read-only access is possible by running:

  DBCC CHECKDB ([YourDBName]) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output to identify the extent of corruption (e.g., allocation errors vs. logical page corruption).

Step 3: Restore from the Most Recent Clean Backup (Preferred Fix)

• Take a tail-log backup if the transaction log is still accessible:

  BACKUP LOG [YourDBName] TO DISK = 'D:\Backups\YourDBName_tail.trn' WITH NORECOVERY;

• Restore the full backup followed by differential and log backups in sequence using RESTORE DATABASE ... WITH NORECOVERY, finishing with WITH RECOVERY on the final file.
• Confirm backup file integrity beforehand using RESTORE VERIFYONLY FROM DISK = 'PathToBackup.bak'.

Step 4: Rebuild the Transaction Log (Only If No Backup Exists)

• As a last resort, switch the database to single-user mode:

  ALTER DATABASE [YourDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Run the repair command, acknowledging potential data loss:

  DBCC CHECKDB ([YourDBName], REPAIR_ALLOW_DATA_LOSS);

• Set the database back to multi-user mode once repair completes:

  ALTER DATABASE [YourDBName] SET MULTI_USER;

Alternative or Verification Steps

• After recovery, run DBCC CHECKDB again with no repair options to confirm zero allocation or consistency errors remain.
• If corruption persists or recurs, run Windows Server 2025 Storage Diagnostics (Get-PhysicalDisk | Get-StorageReliabilityCounter) to rule out failing disk hardware as the underlying cause.

Troubleshooting Guide: SQL Server Error 927 — Database Stuck “In the Middle of a Restore”

Why This Error Happens

This error occurs when a database’s RESTORING flag remains active in the system catalog, typically because a RESTORE DATABASE or RESTORE LOG command was executed WITH NORECOVERY or WITH STANDBY but the restore sequence was never finalized WITH RECOVERY. It can also happen if a restore operation was interrupted mid-process—due to a dropped connection, service crash, or manual cancellation—leaving the database in an inconsistent, non-operational state. SQL Server 2025 will refuse to open the database for any read/write operations until the recovery process is explicitly completed or the restore chain is reset.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your Windows Server 2025 instance.
• Run the following query to confirm the database status:

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Confirm the state_desc column returns RESTORING. This confirms the root cause before proceeding.

Step 2: Complete the Restore Sequence (If More Backups Exist)

• If you have additional differential or transaction log backups to apply, restore them sequentially using WITH NORECOVERY for all but the final file.
• Example command:

  RESTORE LOG [YourDatabaseName] 
  FROM DISK = N'C:\Backups\YourDatabase_Log.trn' 
  WITH NORECOVERY;

• Ensure the backup file path is accessible by the SQL Server service account (check folder permissions on Windows Server 2025 under Properties > Security).

Step 3: Force Recovery to Bring the Database Online

• If no further backup files are available, finalize the restore to bring the database out of limbo.
• Execute the following command to recover the database:

  RESTORE DATABASE [YourDatabaseName] WITH RECOVERY;

• This rolls back any uncommitted transactions and makes the database accessible again.

Step 4: Check for File Corruption or Lock Conflicts

• If Step 3 fails with an I/O error, verify the underlying .mdf/.ldf files are not locked by an antivirus or backup agent running on Windows Server 2025.
• Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for corresponding I/O or permission-denied messages.
• Restart the SQL Server service via services.msc or run NET STOP MSSQLSERVER && NET START MSSQLSERVER in an elevated Command Prompt to clear stale locks.

Alternative or Verification Steps

• Verify the Fix: Re-run the SELECT name, state_desc FROM sys.databases query; the status should now read ONLINE. Attempt a test connection to the database via SSMS to confirm read/write access.
• Alternative Solution: If the restore chain is corrupted or the backup files are unrecoverable, consider restoring from the last known good full backup as a new database instance, then manually migrating any missing transactional data.

Troubleshooting Guide: SQL Server Error 924 – “Database Is Already Open and Can Only Have One User at a Time”

Why This Error Happens

This error occurs because the target database is configured with a SINGLE_USER access restriction, and an existing connection (often a lingering session from SSMS, a failed script, or an orphaned connection pool) is already holding that single available slot. It can also surface when a previous administrative task—such as a restore, restore WITH RECOVERY, or an ALTER DATABASE operation—did not complete cleanly and left the database locked in single-user mode. Additionally, connection pooling on the client side (ADO.NET, ODBC) may reuse a stale session that technically still “owns” the single-user lock even though the application believes the connection was closed.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections to the Database

• Open SQL Server Management Studio (SSMS) and connect using an account with sysadmin privileges.
• Run the following query to find existing sessions locking the database:

SELECT session_id, login_name, host_name, program_name, status
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID('YourDatabaseName');

• Note the session_id values returned; these represent the connections you must terminate.

Step 2: Terminate Blocking Sessions

• For each session_id identified in Step 1, run:

KILL <session_id>;

• If the session does not terminate immediately, wait a few seconds for rollback to complete before proceeding.
• Alternatively, force disconnect everyone at once using:

ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Step 3: Reset the Database Access Mode

• Switch the database back to multi-user mode so future connections are not restricted:

ALTER DATABASE YourDatabaseName SET MULTI_USER;

• Confirm there are no pending transactions by checking:

SELECT * FROM sys.dm_tran_active_transactions
WHERE database_id = DB_ID('YourDatabaseName');

Step 4: Restart the SQL Server Service (If Session Persists)

• On Windows Server 2025 / Windows 11, open Services.msc.
• Locate SQL Server (MSSQLSERVER) or your named instance (e.g., SQL Server (SQLEXPRESS)).
• Right-click and select Restart to forcibly clear any orphaned session locks tied to the single-user mode.
• Re-attempt the connection once the service status shows “Running”.

Step 5: Check for Application-Level Connection Pooling Issues

• In your application’s connection string, verify whether Pooling=True is enabled and review the Min Pool Size / Max Pool Size settings.
• If using IIS or a Windows Service to host the application, recycle the application pool or restart the service to flush stale pooled connections.
• Update the connection string to include Pooling=False temporarily to confirm pooling is the root cause before re-enabling it with adjusted timeout values.

Alternative or Verification Steps

• Verify the fix by running SELECT name, user_access_desc FROM sys.databases WHERE name = 'YourDatabaseName'; and confirming the result shows MULTI_USER.
• If the issue recurs intermittently, use SQL Server Profiler or Extended Events to trace which application or login is repeatedly setting the database to SINGLE_USER, as this may indicate a scheduled job or script incorrectly altering the database state.

Troubleshooting Guide: SQL Server Error 925 – Maximum Number of Databases Exceeded

Why This Error Happens

This error occurs because a single query in SQL Server 2025 has attempted to reference more databases than the engine’s internal limit allows in one execution context. It is typically triggered by cross-database queries, linked server calls, or distributed transactions that span an unusually high number of databases within a single T-SQL batch. This limit is a hard-coded architectural constraint tied to internal locking and metadata structures (DBID tracking), not a configurable server setting, so poorly optimized dynamic SQL or ORM-generated queries are the most common culprits.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Open SQL Server Management Studio (SSMS) and check the SQL Server Error Log (Management > SQL Server Logs) for the exact timestamp of Error 925.
• Use Extended Events or SQL Server Profiler to capture the specific batch/query text that triggered the error, filtering on Error Number = 925.
• Look for dynamic SQL, synonyms, or UNION statements that reference tables across many different databases (e.g., DB1.dbo.Table, DB2.dbo.Table, etc.).

Step 2: Refactor the Query to Reduce Database Scope

• Break the single large query into multiple smaller batches, each referencing fewer databases, and combine results in a temporary table or table variable.
• If the query is auto-generated by an application or ORM (common in multi-tenant architectures), modify the data access layer to loop through database connections individually rather than joining them in one statement.
• Consolidate data using a centralized reporting or staging database (e.g., via SSIS or Replication) instead of querying live databases directly.

Step 3: Check for Unnecessary Database Sprawl

• Navigate to Object Explorer > Databases in SSMS and audit the total count of user databases on the instance.
• If the environment has grown organically, consider consolidating underutilized databases or decommissioning unused ones to reduce the pool of accessible databases for queries.
• On Windows Server 2025, verify disk mount points (Disk Management) to ensure database files aren’t fragmented across unnecessary additional instances that could be merged.

Step 4: Restart the Affected Session or Service

• If the error persists due to a stuck session, identify the session ID (SPID) using sp_who2 and terminate it with KILL <SPID>.
• For persistent engine-level caching issues, restart the SQL Server service via services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Re-run the refactored query in SSMS and confirm it executes without triggering Error 925; check the Messages tab for a clean completion status.
• As an alternative, if business logic requires accessing many databases simultaneously, consider migrating the architecture to use Elastic Query or Linked Servers with distributed partitioned views, which manage connections differently and avoid hitting the single-query database ceiling.

Troubleshooting Guide: Error 921 – “Database Has Not Been Recovered Yet”

Why This Error Happens

Error 921 occurs when SQL Server has marked a database as attached or online, but the actual recovery process (redoing committed transactions and rolling back uncommitted ones) has not yet completed or has stalled entirely. This is common during the startup of Microsoft SQL Server 2025 on large databases, after an unexpected shutdown, or when the underlying storage subsystem on Windows Server 2025 is too slow to service the recovery I/O requests. In rarer cases, the recovery thread may have crashed or become deadlocked due to transaction log corruption, leaving the database permanently in this “pending” state rather than transitioning to “Online.”

Step-by-Step Resolution Guide

Step 1: Verify the Actual Database State

• Open SQL Server Management Studio (SSMS) or a query window and run:
  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Check if the state_desc column shows RECOVERY_PENDING, RECOVERING, or SUSPECT.
• If it shows RECOVERING, the process is likely still active and simply slow; proceed to Step 2 to monitor rather than intervene.

Step 2: Monitor Active Recovery Progress via Error Log

• Navigate to the SQL Server Error Log via SSMS or open the log file directly, typically located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Look for entries indicating “Recovery of database… is X% complete.”
• If progress percentages are actively increasing, allow the process to finish naturally without restarting the service, as restarting will reset the recovery timer to 0%.

Step 3: Check System Resources and Disk I/O Health

• Open Task Manager or Resource Monitor on Windows Server 2025 to check if the sqlservr.exe process is consuming CPU/Disk I/O (indicating it is working) or sitting idle (indicating a stall).
• Verify there is no disk space exhaustion on the drive hosting the .mdf and .ldf files.
• Check the Windows System Event Viewer for underlying disk hardware errors (Event ID 153 or 129) that might be blocking the I/O requests needed for recovery.

Step 4: Restart the SQL Server Service (If Recovery is Stalled)

• If the Error Log shows no progress for an extended period (stalled at 0% or an unchanged percentage for hours), stop and restart the service.
• Use PowerShell (Run as Administrator) to cycle the service:
  Restart-Service -Name 'MSSQLSERVER' -Force
• Immediately re-check the Error Log (Step 2) to confirm the recovery mechanism restarts correctly from the transaction log.

Step 5: Validate Transaction Log Integrity

• If the database enters a SUSPECT state instead of completing recovery, the Transaction Log (LDF) file may be corrupted.
• Attempt to bring the database online manually using Transact-SQL:
  ALTER DATABASE YourDatabaseName SET EMERGENCY;
DBCC CHECKDB (YourDatabaseName);
• Review the DBCC CHECKDB output for specific allocation or consistency errors that need repair.

Alternative or Verification Steps

• Verification: Once the status changes to ONLINE in sys.databases, attempt a simple SELECT TOP 10 * FROM [YourTable] query to confirm the data files are fully accessible and readable.
• Alternative: If recovery continues to fail after a restart, restore the database from the most recent valid backup using RESTORE DATABASE... WITH RECOVERY to bypass the corrupted transaction log state entirely.

Troubleshooting Guide: SQL Server Error 922 – “Database ‘%.*ls’ is being recovered”

Why This Error Happens

This error occurs because SQL Server is actively performing the recovery process on a database, which involves rolling forward committed transactions from the transaction log and rolling back any incomplete ones to maintain ACID compliance. This is a normal state after an unexpected shutdown, a crash, a restore operation, or a long-running transaction that was interrupted. The database remains inaccessible until the recovery phase (analysis, redo, and undo) fully completes, which can take longer on large databases, databases with Very Large Transaction (VLT) files, or systems with slow disk I/O.

Step-by-Step Resolution Guide

Step 1: Confirm the Database Recovery Status

• Open SQL Server Management Studio (SSMS) and run the following query to check the state:

  SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Look for a state of RECOVERING or RECOVERY_PENDING.
• Do not attempt to restart the SQL Server service at this stage, as this will restart the recovery process from the beginning and prolong downtime.

Step 2: Monitor Recovery Progress via Error Log

• Navigate to the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path may vary based on instance name/version folder).
• Look for messages indicating “Recovery of database… is X% complete” to estimate remaining time.
• Alternatively, run this query to check the percentage complete in real-time:

  SELECT session_id, command, percent_complete, estimated_completion_time 
  FROM sys.dm_exec_requests 
  WHERE command IN ('DB STARTUP', 'RECOVERY');

Step 3: Check System Resources and Disk I/O

• Open Task Manager or Performance Monitor (PerfMon) on Windows Server 2025 to verify the disk queue length for the volume hosting the .mdf and .ldf files.
• Ensure there is no antivirus or Windows Defender scan actively locking the .ldf (log) file, as this can significantly slow down the redo/undo phases.
• If the disk I/O is maxed out, consider moving non-essential services off the host temporarily to free up resources for the recovery process.

Step 4: Verify Transaction Log Health

• If recovery appears to be stuck specifically in the “undo” phase for an extended period, check for a large uncommitted transaction.
• Verify there is enough free disk space on the drive hosting tempdb (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\), as insufficient space can halt the recovery sequence.
• If corruption is suspected after recovery fails entirely, prepare to restore from the last known good full backup and transaction log backups.

Alternative or Verification Steps

• Verification: Once the error log shows “Recovery is complete” or the state_desc changes to ONLINE, attempt to connect to the database via SSMS or your application to confirm data accessibility.
• Alternative: If recovery hangs indefinitely (stuck at 0% for hours) due to a corrupted log file, you may need to restart the instance and use ALTER DATABASE [YourDB] SET EMERGENCY followed by DBCC CHECKDB with repair options, though this should only be used as a last resort due to potential data loss.

Troubleshooting Guide: Error 923 – Database in Restricted Mode (SQL Server 2025)

Why This Error Happens

This error occurs because the database’s user access option has been explicitly set to RESTRICTED_USER, which limits connectivity strictly to the database owner (dbo) and members of the dbcreator or sysadmin fixed server roles. This configuration is typically triggered manually during maintenance (such as restoring a database or running single-user diagnostic scripts) or automatically following certain recovery operations. If your current login is mapped to a standard user role without sysadmin privileges, SQL Server will reject the connection attempt and throw Error 923.

Step-by-Step Resolution Guide

Step 1: Verify Current Database State

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect using an account known to have sysadmin privileges.
• Run the following query to check the current access state of the database:

  SELECT name, user_access_desc FROM sys.databases WHERE name = 'YourDatabaseName';

• Confirm that the user_access_desc column returns RESTRICTED_USER.

Step 2: Reset the Database Access Mode

• Execute the ALTER DATABASE command to revert the database to multi-user mode, allowing standard logins to reconnect.
• Run this command in a New Query window:

  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

• If the command hangs, ensure no other blocking transactions are preventing the state change by checking sys.dm_exec_requests.

Step 3: Validate User Roles and Permissions

• If setting the database to MULTI_USER does not resolve the access issue for specific logins, verify that the user attempting to connect is correctly mapped to the database.
• Navigate to Security > Logins in SSMS, right-click the specific login, and select Properties.
• Under the User Mapping tab, ensure the checkbox for the target database is selected and assign the db_owner role if standard access is required moving forward.

Step 4: Restart the SQL Server Service (If Stuck)

• If the ALTER DATABASE command fails due to a stuck session or lingering lock from the previous maintenance window, a service restart may be required.
• Open Services.msc on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Re-attempt the connection via SSMS once the service status returns to “Running.”

Alternative or Verification Steps

• Verification: Attempt to log in using a standard, non-administrative domain account to confirm the Error 923 message no longer appears and the connection succeeds.
• Alternative: If ALTER DATABASE fails due to active connections, use ALTER DATABASE [YourDatabaseName] SET MULTI_USER WITH ROLLBACK IMMEDIATE; to forcefully terminate blocking sessions and apply the change immediately.

Why This Error Happens

This message is typically generated internally by SQL Server 2025 during a database upgrade process, specifically when the engine modifies system catalog views or internal script levels to match the current build version. It is usually informational rather than a critical failure, but it can appear as a blocking error if the upgrade script is interrupted mid-process due to insufficient permissions, a corrupted master database, or an unexpected termination of the SQL Server service during version migration. In rare cases, this occurs because a user context attempting the upgrade (often sa or a service account) lacks the necessary ALTER or CONTROL SERVER permissions required to modify internal metadata structures.

Step-by-Step Resolution Guide

Step 1: Verify the Service Account Permissions

• Open SQL Server Configuration Manager and check which account is running the SQL Server (MSSQLSERVER) service.
• Ensure this account has local Administrator rights on Windows Server 2025/Windows 11, or at minimum, sysadmin fixed server role membership within SQL Server.
• Run the following query in a new query window (connected via DAC if necessary) to confirm role membership:

SELECT IS_SRVROLEMEMBER('sysadmin');

Step 2: Start SQL Server in Single-User Mode to Complete the Upgrade

• Stop the SQL Server service via services.msc or PowerShell:

Stop-Service -Name 'MSSQLSERVER' -Force

• Restart the instance using the -m startup parameter to force single-user mode, allowing the pending script-level upgrade to finish without contention:

NET START MSSQLSERVER /m"SQLCMD"

• Reconnect via sqlcmd and allow the internal system script to re-run to completion.

Step 3: Check for Corruption in the Master Database

• Run DBCC CHECKDB against the master database to rule out corruption that may be blocking the script level update:

DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found, you may need to restore master from a known good backup or rebuild system databases using the SQL Server 2025 installation media (setup.exe /ACTION=REBUILDDATABASE).

Step 4: Review the SQL Server Error Log for Context

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for entries immediately preceding and following Error 919 to determine if it is paired with Error 912 (Script level upgrade failed) or other blocking errors that indicate the true root cause.

Alternative or Verification Steps

• After the service restarts successfully in multi-user mode, run SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'); to confirm the instance reports the expected build and that no further upgrade messages appear in the log.
• If the issue persists after a clean restart, consider applying the latest Cumulative Update (CU) for SQL Server 2025, as this message can sometimes be triggered by a partially applied patch that left the internal script level inconsistent.

Troubleshooting Guide: SQL Server Error 920 — Database Script Level Modification Restricted

Why This Error Happens

Error 920 occurs because the internal database script level (a value SQL Server uses to track catalog/engine upgrade state) is a protected system-level attribute that can only be modified by principals holding the sysadmin fixed server role. This typically surfaces when a login attempting to run upgrade scripts, restore operations, or compatibility-related maintenance tasks lacks sufficient privileges. It can also appear after a failed in-place upgrade on Windows Server 2025 where the service account context used to run post-upgrade scripts was not granted sysadmin rights.

Step-by-Step Resolution Guide

Step 1: Verify the Current Login’s Role Membership

• Open SQL Server Management Studio (SSMS) and connect using an account known to have administrative rights.
• Run the following query to check role membership:

  SELECT IS_SRVROLEMEMBER('sysadmin');

• A return value of 0 confirms the current login is not a sysadmin, which is the direct cause of Error 920.

Step 2: Grant the Sysadmin Role to the Required Login

• Connect to the instance using Windows Authentication via a local administrator account (this often has implicit access if SQL Server was installed with default settings).
• Execute the following T-SQL as a sysadmin-enabled account:

  ALTER SERVER ROLE sysadmin ADD MEMBER [DOMAIN\YourServiceAccount];

• If no account currently has sysadmin access (common after a lockout), restart the SQL Server service in single-user mode:
• Open Services.msc on Windows Server 2025 / Windows 11.
• Stop the SQL Server (MSSQLSERVER) service.
• Restart it manually via Command Prompt with the -m flag:
  
NET START MSSQLSERVER /m"SQLCMD"

• Connect via sqlcmd and add your account to sysadmin.

Step 3: Re-run the Script Level Modification Command

• Once sysadmin rights are confirmed, re-execute the original command or upgrade script that triggered Error 920 (e.g., ALTER DATABASE SET COMPATIBILITY_LEVEL or internal upgrade stored procedures).
• Confirm the script level change by querying:

  SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName';

Step 4: Check for Upgrade Service Account Misconfiguration

• If the error occurred during a Windows Server 2025 in-place OS upgrade or SQL Server 2025 setup, open SQL Server Configuration Manager.
• Verify the SQL Server service account listed under the Log On tab matches the account that has sysadmin privileges.
• Restart the SQL Server service after any account changes to apply the new security context.

Alternative or Verification Steps

• Verify the fix by running SELECT IS_SRVROLEMEMBER('sysadmin') again — it should now return 1, and the original script-level command should execute without Error 920.
• If access remains blocked, boot into single-user mode (sqlservr.exe -m) directly from Command Prompt as a last resort, then use sqlcmd -S . -E to connect and manually re-provision sysadmin rights before restarting the service normally.

Troubleshooting Guide: Error 917 – Upgrade Script Batch Compilation Failure

Why This Error Happens

Error 917 occurs when SQL Server attempts to run an internal upgrade script against a system or user database, but encounters a T-SQL compilation error within that script batch. This is typically caused by database corruption, a mismatched or incomplete prior upgrade that left objects in an inconsistent state, or a damaged master/mssqlsystemresource database preventing the script from resolving object references correctly. It can also occur if a previous failed patch or upgrade attempt was interrupted, leaving system catalog views or metadata out of sync.

Step-by-Step Resolution Guide

Step 1: Review the Full Error Log for the Root Cause

• Open the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Locate the error immediately preceding Error 917 — this will reference the specific line number and object that failed to compile.
• Note the database name and script file (e.g., sqlagent100_msdb_upgrade.sql) mentioned in the message for targeted troubleshooting.

Step 2: Verify Database Consistency and Compatibility Level

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORS; in SQL Server Management Studio (SSMS) to rule out corruption.
• Confirm the database compatibility level matches a supported level for SQL Server 2025 using:

  SELECT name, compatibility_level FROM sys.databases;

• If compatibility level is outdated, update it with ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;

Step 3: Restart Setup in Single-User Mode to Re-run Upgrade Scripts

• Stop the SQL Server service via services.msc or PowerShell: Stop-Service MSSQLSERVER.
• Restart the instance in single-user mode using Command Prompt:

  NET START MSSQLSERVER /m

• Connect via sqlcmd -S . -E and manually inspect/re-run the failing upgrade script segment identified in Step 1.

Step 4: Restore from a Pre-Upgrade Backup if Corruption Persists

• If DBCC CHECKDB reports unrepairable allocation or catalog errors, restore the affected database from the most recent known-good backup taken prior to the upgrade attempt.
• Use SSMS Tasks > Restore > Database, or run:

  RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\PreUpgrade.bak' WITH REPLACE;

• Re-attempt the SQL Server 2025 upgrade/patch installation after confirming database integrity.

Alternative or Verification Steps

• Confirm the fix by re-running Setup or the Cumulative Update installer and checking that the Summary.txt log (in %ProgramFiles%\Microsoft SQL Server\170\Setup Bootstrap\Log\) reports no further 917 errors.
• If the issue persists only on msdb or master, consider rebuilding system databases using setup.exe /ACTION=REBUILDDATABASE as a last resort, followed by reapplying the latest Cumulative Update.

Troubleshooting Guide: SQL Server Error 918 — Engine Script Metadata Load Failure

Why This Error Happens

Error 918 occurs when SQL Server 2025 attempts to load internal engine script DLLs (used for engine metadata, system views, or extensibility modules) but the operating system blocks or fails the load operation. This is typically caused by corrupted or missing DLL files in the SQL Server Binn directory, insufficient NTFS permissions for the SQL Server service account, or interference from antivirus/endpoint protection software quarantining the file. It can also result from an incomplete or interrupted SQL Server installation/update on Windows Server 2025 or Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify the Integrity of the Script DLL

• Locate the DLL referenced in the error message (the %.*ls placeholder in the Event Viewer log will show the exact filename, e.g., sqlscriptm.dll).
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\ and confirm the file exists and is not 0 KB.
• Compare the file hash or version against a known-good installation media using PowerShell: Get-FileHash "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn\sqlscriptm.dll".

Step 2: Check and Correct File Permissions

• Right-click the Binn folder, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read & Execute permissions.
• Run icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn" from an elevated Command Prompt to audit current permissions.
• Re-apply inherited permissions if they were manually altered: icacls "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn" /reset /T.

Step 3: Exclude SQL Server Directories from Antivirus Scanning

• Open Windows Security (or your third-party AV console) and add an exclusion for the entire MSSQL\Binn folder path.
• Check the antivirus quarantine log for any recently moved .dll files matching the SQL Server engine scripts.
• Restore any falsely quarantined files and re-scan to confirm no further detections.

Step 4: Repair the SQL Server 2025 Installation

• Open Control Panel > Programs and Features, select Microsoft SQL Server 2025, and choose Change.
• In the SQL Server Installation Center, select Repair and follow the wizard to restore missing or corrupted engine components.
• Reboot Windows Server 2025 / Windows 11 after the repair completes to clear any locked file handles.

Step 5: Restart the SQL Server Service and Monitor Logs

• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated prompt.
• Open the SQL Server Error Log (ERRORLOG file in the Log subfolder) and confirm Error 918 no longer appears during startup.

Alternative or Verification Steps

• Run sqlservr -c -m from the command line to start SQL Server in single-user/console mode and watch for real-time load errors referencing the script DLL.
• If the issue persists after repair, uninstall and perform a clean reinstall of the Database Engine component, ensuring the installation media is freshly downloaded and not corrupted.

Troubleshooting Guide: Error 914 – Script Level Upgrade Failure in SQL Server 2025

Why This Error Happens

Error 914 occurs when SQL Server 2025 attempts to run internal upgrade scripts against system databases (most commonly master or msdb) during startup, but the process is interrupted before completion. This is typically caused by insufficient permissions on the SQL Server service account, corrupted system database files, a prior failed patch/update that left the database in an inconsistent state, or insufficient disk space preventing the upgrade script from writing changes. Since the master database controls the upgrade orchestration itself, any abort here is especially critical and can prevent the instance from starting cleanly.

Step-by-Step Resolution Guide

Step 1: Review the SQL Server Error Log for Root Cause

• Navigate to the default log path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Open the log in a text editor and search for “Error: 914” and the specific upgrade step name referenced in the message.
• Look for accompanying errors like 5172 (file header corruption), 823/824 (I/O errors), or “insufficient disk space” immediately before the abort.
• Note the exact database and script step name — this determines whether you’re dealing with master, msdb, or a user database.

Step 2: Verify Service Account Permissions and Disk Space

• Open Services.msc, locate “SQL Server (MSSQLSERVER)”, and confirm the service account has full control over C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Check available disk space on the drive hosting system databases; ensure at least 20% free space.
• Right-click the data folder, go to Properties > Security, and grant Full Control to the SQL Server service account if missing.
• Restart the SQL Server service via services.msc or run: net stop MSSQLSERVER && net start MSSQLSERVER

Step 3: Start SQL Server in Single-User/Minimal Configuration Mode to Rerun Upgrade

• Open an elevated Command Prompt and stop the SQL Server service.
• Start the instance manually with trace flags to bypass full startup validation:
• sqlservr.exe -c -m -T3608 -T902
• -T902 skips the upgrade script temporarily, allowing you to connect via SQLCMD and inspect the master database state.
• Once connected, run DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS to check for corruption.

Step 4: Restore Master Database from a Known Good Backup (If Corrupted)

• If corruption is confirmed in Step 3, stop the instance and restore master.mdf and mastlog.ldf from a verified pre-upgrade backup.
• Place the restored files in: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Remove the -T902 trace flag and restart the service normally to allow the upgrade script to rerun against the restored files: net start MSSQLSERVER

Alternative or Verification Steps

• After restarting normally, query SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('EngineEdition') to confirm the instance started and reports the correct upgraded build number.
• If the upgrade still fails, run the SQL Server 2025 setup media with the /ACTION=REPAIR switch from an elevated Command Prompt to force reapplication of the upgrade scripts: Setup.exe /ACTION=REPAIR /INSTANCENAME=MSSQLSERVER /QUIET

Troubleshooting Guide: SQL Server Error 915 – “Unable to Obtain the Current Script Level for Database”

Why This Error Happens

This error typically occurs when SQL Server cannot read or validate the version/script level metadata stored in the sys.sysobjvalues or sys.databases system catalog, often due to a corrupted master database or a failed upgrade process. It can also happen if the SQL Server service account lacks sufficient permissions on the data files, or if the database was left in an inconsistent state after an interrupted patch, service pack, or cumulative update installation. In rare cases, mismatched system table schemas between the SQL Server binaries and the actual database files trigger this failure during startup.

Step-by-Step Resolution Guide

Step 1: Start SQL Server in Single-User/Minimal Configuration Mode

• Open an elevated Command Prompt on Windows Server 2025 / Windows 11.
• Stop the SQL Server service: net stop MSSQLSERVER
• Start the instance manually with trace flags to bypass normal startup:
  sqlservr.exe -c -m -T3608
• The -T3608 flag limits recovery to only the master database, isolating the issue.

Step 2: Check and Repair the Master Database

• Open a new Command Prompt window and connect via sqlcmd -S . -E
• Run DBCC CHECKDB('master') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify corruption.
• If corruption is confirmed, you may need to rebuild the master database using:
  setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators
• Ensure you have a valid backup of master, model, and msdb before proceeding, as this resets system databases.

Step 3: Verify File Permissions and Ownership

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click master.mdf and mastlog.ldf, go to Properties > Security.
• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Reapply permissions if needed using PowerShell:
  icacls "master.mdf" /grant "NT SERVICE\MSSQLSERVER:(F)"

Step 4: Reapply the Latest Cumulative Update

• Download the latest SQL Server 2025 Cumulative Update (CU) from the Microsoft official download center.
• Run the installer and select Repair instead of a fresh install if the setup detects an existing instance.
• This resolves script-level mismatches caused by partially applied patches.

Step 5: Restart the Service Normally

• Stop the manually running sqlservr.exe process via Task Manager or Ctrl+C in the console.
• Restart the service normally: net start MSSQLSERVER
• Monitor the SQL Server Error Log located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG

Alternative or Verification Steps

• Verify the fix by querying SELECT SERVERPROPERTY('ProductVersion'), DATABASEPROPERTYEX('master', 'Version'); to ensure the script level matches the installed build.
• If the rebuild does not resolve the issue, restore master.mdf and mastlog.ldf from a known-good backup taken prior to the last successful startup, then reapply any transaction log backups for user databases.

Troubleshooting Guide: SQL Server Error 916 – “The server principal is not able to access the database under the current security context”

Why This Error Happens

This error occurs when a login’s user mapping is orphaned, meaning the SID associated with the database user does not match the SID of the server-level login attempting to connect. It can also surface when the database is in an inconsistent trustworthy state, when the login lacks a valid user mapping in the target database, or when the database was restored/attached from another server without reconciling security contexts. Additionally, “Contained Database Authentication” misconfigurations in SQL Server 2025 can trigger this when the connection string targets a database the principal was never explicitly granted access to.

Step-by-Step Resolution Guide

Step 1: Verify the Login-to-User Mapping

• Open SQL Server Management Studio (SSMS) and connect to the instance using an account with sysadmin privileges.
• Run the following query to check for orphaned users:

  USE [YourDatabaseName];
  EXEC sp_change_users_login 'Report';

• Confirm whether the SID in sys.database_principals matches the SID in sys.server_principals for the affected login.

Step 2: Remap or Recreate the Orphaned User

• If orphaned, remap the existing database user to the correct server login:

  USE [YourDatabaseName];
  ALTER USER [DatabaseUserName] WITH LOGIN = [ServerLoginName];

• If no database user exists at all, create one explicitly:

  USE [YourDatabaseName];
  CREATE USER [ServerLoginName] FOR LOGIN [ServerLoginName];

• Grant minimum required permissions (e.g., db_datareader, db_datawriter) rather than defaulting to db_owner.

Step 3: Check Database Trustworthy and Contained Authentication Settings

• Verify the TRUSTWORTHY property on the database, especially if cross-database ownership chaining is involved:

  SELECT name, is_trustworthy_on FROM sys.databases WHERE name = 'YourDatabaseName';

• If using Contained Databases (common in SQL Server 2025 Azure Arc-enabled instances), confirm the contained user exists locally:

  SELECT name, type_desc, authentication_type_desc FROM sys.database_principals WHERE name = 'ServerLoginName';

• Enable contained authentication at the instance level if required:

  EXEC sp_configure 'contained database authentication', 1;
  RECONFIGURE;

Step 4: Restart the SQL Server Service and Clear Cached Credentials

• On Windows Server 2025 / Windows 11, open services.msc, locate SQL Server (MSSQLSERVER), and restart the service to clear any stale security token caches.
• Alternatively, run from an elevated PowerShell prompt:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Re-attempt the connection using the affected server principal.

Alternative or Verification Steps

• Confirm the fix by connecting with the affected login via SSMS or sqlcmd -S servername -U loginname -P password -d databasename and ensure the connection succeeds without Error 916.
• If the issue persists, drop and recreate the login entirely using CREATE LOGIN [LoginName] WITH PASSWORD = 'StrongPassword'; USE [YourDatabaseName]; CREATE USER [LoginName] FOR LOGIN [LoginName]; to rule out deeper SID corruption in master.sys.server_principals.

Troubleshooting Guide: SQL Server Error 912 – Script Level Upgrade Failure

Why This Error Happens

Error 912 occurs when SQL Server 2025 attempts to run internal upgrade scripts against system databases (typically master or msdb) during startup, but one of the upgrade steps fails to complete. This is commonly caused by insufficient permissions for the SQL Server service account, corrupted system database files, incomplete previous upgrades, or custom objects (triggers, jobs) in system databases that conflict with the new schema changes. The underlying error code referenced in the message (%d) points to the specific root cause, such as a disk I/O failure, object name conflict, or access denied issue.

Step-by-Step Resolution Guide

Step 1: Identify the Specific Underlying Error

• Open the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for the line containing “Error: 912” and note the accompanying error number (e.g., 2714, 5172) immediately following it.
• Cross-reference this secondary error number, as it dictates whether the issue is permissions-based or corruption-based.

Step 2: Verify Service Account Permissions

• Open Services.msc on Windows Server 2025 and locate the “SQL Server (MSSQLSERVER)” service.
• Ensure the Log On account has full control over the data directory: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.
• Right-click the DATA folder, navigate to Properties > Security, and add “Full Control” for the specific SQL Service SID if missing.
• Restart the SQL Server service via net stop MSSQLSERVER and net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Start SQL Server in Minimal Configuration Mode

• Open Command Prompt as Administrator and navigate to the Binn folder: cd C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Binn.
• Stop the standard service and launch manually using the -f flag: sqlservr.exe -f -m.
• This bypasses non-essential startup routines, allowing you to connect via SSMS to manually inspect or drop the conflicting object identified in Step 1.

Step 4: Rebuild System Databases (If Corruption is Confirmed)

• If the error log indicates physical corruption (e.g., error 5172 or checksum failures), use the Installation Center to rebuild system databases.
• Run Setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=BUILTIN\Administrators from the installation media.
• Ensure you have a recent backup, as this action resets master, model, and msdb to default states, requiring a subsequent restore of user data.

Alternative or Verification Steps

• Verification: After the service restarts successfully, run SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('EngineEdition'); in SSMS to confirm the instance is online and reporting the correct updated build number.
• Alternative: If Step 3 reveals a corrupted user-defined object in msdb causing the conflict, you may restore msdb specifically from a pre-upgrade backup rather than rebuilding the entire instance, provided the backup predates the failed upgrade attempt.

Why This Error Happens

Error 913 occurs when SQL Server’s internal metadata cache references a database ID that does not exist in the current sys.databases catalog, typically because the database was detached, dropped, or restored under a different ID while a cached execution plan or connection still points to the stale reference. This frequently happens after a failover in an Always On Availability Group, a database snapshot deletion, or when dbid reuse collides with a dangling plan cache entry. Corrupted system catalog views or an interrupted database state transition (e.g., RESTORING to ONLINE) can also trigger this error on Windows Server 2025 and Windows 11 developer instances.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Availability

• Open SQL Server Management Studio (SSMS) or run SELECT name, database_id, state_desc FROM sys.databases; to confirm the database referenced by the ID is ONLINE.
• Check for databases stuck in RESTORING, RECOVERY_PENDING, or SUSPECT states.
• If the database is in transition, wait a few seconds and reissue the query — this is the most common benign cause.

Step 2: Clear the Procedure and Plan Cache

• Run DBCC FREEPROCCACHE; to remove stale execution plans referencing dropped or renamed database IDs.
• Alternatively, target a specific plan using DBCC FREEPROCCACHE (plan_handle); if you’ve identified the offending query via sys.dm_exec_cached_plans.
• Restart the SQL Server service only if the cache clear doesn’t resolve the issue: net stop MSSQLSERVER && net start MSSQLSERVER (run from an elevated PowerShell/Command Prompt).

Step 3: Check for Orphaned Database References

• Query SELECT * FROM sys.sysdatabases WHERE dbid = <ID>; (on legacy compatibility views) to confirm whether the ID is genuinely orphaned.
• Review any linked server queries, cross-database queries, or synonyms pointing to a database that was renamed or dropped.
• Update connection strings or stored procedures referencing the old database name/ID in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ configuration files or application config XML/JSON files.

Step 4: Review Always On / Failover Cluster Configuration

• If using Always On Availability Groups, run SELECT * FROM sys.dm_hadr_database_replica_states; to confirm the database is synchronized and not in a failover transition.
• Check the Windows Server Failover Cluster (WSFC) event logs via eventvwr.msc under Applications and Services Logs > Microsoft > Windows > FailoverClustering for recent failover events coinciding with the error.
• Manually fail back to the primary replica if the secondary is unexpectedly serving stale metadata.

Step 5: Run Consistency Checks on the Database

• Execute DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to rule out catalog corruption.
• If corruption is found, restore from the last known good backup rather than attempting repair options that risk data loss.

Alternative or Verification Steps

• After applying fixes, rerun the original failing query and confirm it returns results without Error 913; also check SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) for recurrence.
• If the issue persists, capture an Extended Events trace filtering on error_reported with error_number = 913 to identify the exact query and application context generating the stale database ID reference.

Troubleshooting Guide: SQL Server Error 910 — Database Upgrade Script in Progress

Why This Error Happens

Error 910 is actually an informational status message, not a true failure — it appears in the SQL Server error log when a database is being upgraded internally, running system scripts that migrate it from one internal compatibility/version level to another (e.g., after a major version upgrade like SQL Server 2025). This process typically occurs automatically during the first startup after installation or migration, and the message is logged to indicate progress. However, if the upgrade process stalls, is interrupted, or the script fails partway through, the database can become stuck in this upgrading state due to corrupted system catalogs, insufficient permissions on system databases, or disk/resource constraints preventing script completion.

Step-by-Step Resolution Guide

Step 1: Confirm the Upgrade Is Actually Progressing (Not Stuck)

• Open the SQL Server Error Log via SSMS: Management > SQL Server Logs > Current, or check the raw log file at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for sequential entries showing increasing level numbers (e.g., “from level 950 to level 951”), which indicates normal progress rather than a hang.
• If the log hasn’t updated in over 15–20 minutes and CPU/disk activity for sqlservr.exe is idle, the upgrade is likely stalled.

Step 2: Verify Service Account Permissions and Resource Availability

• Confirm the SQL Server service account has full control over the data and log directories: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Check available disk space on the drive hosting tempdb, as upgrade scripts rely heavily on tempdb for intermediate operations — ensure at least 20% free space.
• Review Windows Event Viewer (Windows Logs > Application) for any disk I/O errors, access denied warnings, or VSS/antivirus interference events occurring at the same timestamp as Error 910.

Step 3: Restart SQL Server in Single-User Mode to Force Script Completion

• Stop the SQL Server service via services.msc or run: net stop MSSQLSERVER.
• Restart it in single-user mode with minimal configuration using a command prompt: sqlservr.exe -m -T3608.
• This forces only the master database to load initially, allowing the upgrade script to complete without interference from other connections or background jobs.
• Once the ERRORLOG shows “Recovery is complete” or “SQL Server is now ready for client connections,” stop the manual instance and restart the service normally.

Step 4: Rebuild or Restore System Databases if Upgrade Fails Repeatedly

• If the database remains stuck after Step 3, consider restoring master, model, and msdb from a pre-upgrade backup taken before the SQL Server 2025 migration.
• Alternatively, run the SQL Server Setup repair utility: Setup.exe /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER (use only as a last resort, as this resets system databases to default).
• Reapply any custom configurations (linked servers, logins, jobs) only after confirming the upgrade scripts have fully completed.

Alternative or Verification Steps

• Run SELECT DATABASEPROPERTYEX('YourDBName', 'Version'); in a new query window to confirm the database has reached the expected internal version number matching SQL Server 2025 (build-specific value documented in Microsoft’s release notes).
• If the issue persists after a service restart, run DBCC CHECKDB against the affected database to rule out underlying corruption that may be blocking the upgrade script from finalizing.

Troubleshooting Guide: SQL Server Error 911 – “Database Does Not Exist”

Why This Error Happens

This error occurs when SQL Server cannot locate a database with the specified name in its system catalog (sys.databases), typically because the database was never created, was detached, was dropped, or the name was misspelled (including incorrect case sensitivity or extra whitespace). It can also happen when a connection string or application points to a database on the wrong SQL Server instance, or when a database exists but is in a SUSPECT, OFFLINE, or RESTORING state that temporarily hides it from normal connection resolution. Additionally, this error surfaces frequently after server migrations when the connecting login’s default database was deleted or renamed.

Step-by-Step Resolution Guide

Step 1: Verify the Database Name and Existence

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to list all databases currently attached to the instance:

  SELECT name, state_desc FROM sys.databases ORDER BY name;

• Confirm there are no typos, trailing spaces, or case-mismatch issues in the database name used by your application or script.

Step 2: Check the Connection Target (Instance/Server Name)

• Verify your application’s connection string or sqlcmd command is pointing to the correct SQL Server instance (e.g., SERVERNAME\INSTANCENAME vs. just SERVERNAME).
• On Windows Server 2025 / Windows 11, open SQL Server Configuration Manager to confirm the instance name and that the SQL Server service is running under the expected instance.
• Test connectivity manually using:

  sqlcmd -S YourServerName\InstanceName -d YourDatabaseName -Q "SELECT 1"

Step 3: Inspect Database State and File Paths

• Check if the database exists but is offline or suspect:

  SELECT name, state_desc, is_read_only FROM sys.databases WHERE name = 'YourDatabaseName';

• If the state is OFFLINE, bring it back online:

  ALTER DATABASE YourDatabaseName SET ONLINE;

• If the database was detached, locate the .mdf and .ldf files (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and reattach it:

  CREATE DATABASE YourDatabaseName ON 
  (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabaseName.mdf')
  FOR ATTACH;

Step 4: Restore From Backup if the Database Was Dropped

• If no files remain and the database was accidentally dropped, locate the latest .bak file and restore it:

  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'C:\Backups\YourDatabaseName.bak' 
  WITH RECOVERY;

• Ensure the SQL Server service account has read/write permissions on the backup folder and data directory.

Alternative or Verification Steps

• After applying fixes, re-run SELECT name, state_desc FROM sys.databases; to confirm the database now appears with a state of ONLINE, then retest your application connection.
• If the issue persists, check the default database setting for the login via Login Properties in SSMS (Security → Logins → Properties → General tab) — a login pointing to a deleted default database can trigger Error 911 even when other databases are healthy.

Troubleshooting Guide: SQL Server Error 908 – Filegroup Unavailable

Why This Error Happens

Error 908 occurs when SQL Server 2025 marks a filegroup as unavailable because it is offline, restoring, recovery pending, or suspect, typically due to an interrupted restore operation, a missing or corrupted data file, or a storage I/O failure on Windows Server 2025 / Windows 11. This can also happen if a filegroup was manually taken offline (e.g., ALTER DATABASE ... MODIFY FILEGROUP ... OFFLINE) and never brought back online. In some cases, abrupt shutdowns or disk disconnections during a multi-file restore sequence leave the filegroup in an inconsistent state that SQL Server refuses to serve queries against.

Step-by-Step Resolution Guide

Step 1: Identify the Filegroup State

• Run the following query to check the exact status:

SELECT name, state_desc, is_read_only 
FROM sys.filegroups;

• Cross-reference with sys.master_files to confirm file-level status (ONLINE, OFFLINE, RESTORING, RECOVERY_PENDING, SUSPECT):

SELECT name, physical_name, state_desc 
FROM sys.master_files 
WHERE database_id = DB_ID('YourDatabaseName');

• Check the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for related I/O or restore failure messages.

Step 2: Verify Physical File Accessibility

• Confirm the underlying .mdf/.ndf file paths exist and are reachable (not on a disconnected network share or detached volume).
• Check Windows Disk Management or Get-Disk / Get-Volume in PowerShell to ensure the volume hosting the filegroup files is online.
• Validate NTFS permissions — the SQL Server service account must have Read/Write access to the file directory (right-click folder → Properties → Security).

Step 3: Bring the Filegroup Back Online

• If the filegroup is OFFLINE due to a manual action, bring it back online:

ALTER DATABASE YourDatabaseName 
MODIFY FILEGROUP YourFileGroupName ONLINE;

• If it’s stuck in RESTORING state, complete the restore sequence:

RESTORE DATABASE YourDatabaseName 
WITH RECOVERY;

• If the file is SUSPECT or corrupted, restore the specific file from the last known good backup:

RESTORE DATABASE YourDatabaseName 
FILE = 'YourLogicalFileName' 
FROM DISK = 'D:\Backups\YourBackup.bak' 
WITH NORECOVERY;

Step 4: Run Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no further corruption exists.
• Review output for allocation errors tied to the previously unavailable filegroup’s pages.

Alternative or Verification Steps

• Verify the fix: Re-run SELECT state_desc FROM sys.filegroups WHERE name = 'YourFileGroupName'; — it should now return ONLINE, and application queries against affected tables should succeed without Error 908.
• Alternative fallback: If the filegroup cannot be recovered due to severe corruption, restore the entire database from the most recent full + log backup chain to a new database name, then redirect the application connection string as a temporary workaround.

Troubleshooting Guide: Error 909 – Data Compression / Vardecimal Storage Edition Mismatch

Why This Error Happens

This error occurs when a database containing objects configured with data compression (row or page compression) or the legacy vardecimal storage format is attached to, restored on, or started by an edition of SQL Server 2025 that does not support these Enterprise-tier features. SQL Server performs a compatibility check during database startup, and if it detects these storage attributes on a non-Enterprise edition (such as Standard, Web, or Express), it refuses to bring the database online to prevent data access inconsistencies. This commonly happens after downgrading an edition, migrating a database to a lower-tier licensed server, or restoring a backup onto mismatched hardware/licensing.

Step-by-Step Resolution Guide

Step 1: Identify the Current SQL Server Edition

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check your current edition:

SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition;

• If the result is not “Enterprise Edition” or “Developer Edition,” this confirms the root cause.

Step 2: Locate Objects Using Compression or Vardecimal Storage

• Before attempting any fix, identify which tables/indexes are causing the conflict by querying the source server (if still accessible) or a compatible Enterprise instance:

SELECT OBJECT_NAME(object_id) AS TableName, 
       data_compression_desc 
FROM sys.partitions 
WHERE data_compression > 0;

• Note down all affected table and index names for the next step.

Step 3: Remove Compression Settings on a Supported Instance

• On an Enterprise or Developer edition instance where the database can start, run the following for each affected object to disable compression:

ALTER TABLE [YourTableName] REBUILD WITH (DATA_COMPRESSION = NONE);

• For vardecimal storage format, disable it using:

EXEC sp_tableoption 'YourTableName', 'vardecimal storage format', 'OFF';

• Repeat this for every object identified in Step 2.

Step 4: Re-attach or Restore the Database to the Target Edition

• Take a fresh backup of the database after compression has been removed.
• Copy the .bak file to your Windows Server 2025 / Windows 11 target machine.
• Restore the database using SSMS or T-SQL:

RESTORE DATABASE [YourDatabase] 
FROM DISK = 'C:\Backups\YourDatabase.bak' 
WITH REPLACE;

Step 5: Upgrade Edition (If Compression Is Required)

• If data compression is a business requirement, consider upgrading your SQL Server 2025 license to Enterprise or Developer edition instead of stripping compression.
• Use the SQL Server Installation Center to perform an edition upgrade: Setup.exe /ACTION=EditionUpgrade.

Alternative or Verification Steps

• Verify the fix by attempting to bring the database online again (ALTER DATABASE [YourDatabase] SET ONLINE;) and confirm Error 909 no longer appears in the SQL Server Error Log.
• As a quick alternative check, run sys.dm_db_persisted_sku_features on the target instance to confirm no Enterprise-only features remain enabled before migration.

Troubleshooting Guide: Error 905 — Database Cannot Be Started Due to Partitioning Restriction

Why This Error Happens

This error occurs when a database containing a partition function or partitioned table/index is attached, restored, or started on an edition of SQL Server 2025 that does not support table partitioning (such as Standard, Web, or Express editions). Table partitioning is an Enterprise-edition-only feature, so when SQL Server reads the database metadata and detects a partition function object, it blocks the database from coming online to prevent an unsupported configuration. This commonly happens after migrating a database from an Enterprise edition server to a lower-tier edition, or after an in-place edition downgrade on the same Windows Server 2025 / Windows 11 instance.

Step-by-Step Resolution Guide

Step 1: Confirm the SQL Server Edition Currently Installed

• Open SSMS or a query tool and connect to the target instance.
• Run the following query to check edition details:

  SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition;

• If the result shows “Standard Edition,” “Web Edition,” or “Express Edition,” this confirms partitioning is unsupported and is the root cause of Error 905.

Step 2: Identify Partitioned Objects in the Source Database

• Since the database cannot be brought online, if you still have access to the original Enterprise edition instance (or a backup copy restored on an Enterprise edition test server), run this query to list affected objects:

  SELECT DISTINCT 
      s.name AS SchemaName, 
      t.name AS TableName, 
      ps.name AS PartitionScheme, 
      pf.name AS PartitionFunction
  FROM sys.tables t
  JOIN sys.indexes i ON t.object_id = i.object_id
  JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
  JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
  JOIN sys.schemas s ON t.schema_id = s.schema_id;

• Document every table and index using partitioning so you can remediate each one in Step 3.

Step 3: Remove Partitioning from the Database (on an Enterprise Instance)

• On an Enterprise edition instance (temporary VM, trial license, or original source server), restore the database and convert partitioned tables/indexes to standard (non-partitioned) heap or clustered structures using ALTER TABLE ... SWITCH or by rebuilding indexes onto the PRIMARY filegroup:

  CREATE CLUSTERED INDEX IX_Rebuild 
  ON dbo.YourPartitionedTable (YourKeyColumn) 
  WITH (DROP_EXISTING = ON) 
  ON [PRIMARY];

• Repeat this for every table identified in Step 2, then drop the now-unused partition schemes and partition functions:

  DROP PARTITION SCHEME YourSchemeName;
  DROP PARTITION FUNCTION YourFunctionName;

Step 4: Re-Backup and Restore to the Target Edition

• Take a fresh full backup of the now-departitioned database on the Enterprise instance.
• Copy the .bak file to the Windows Server 2025 / Windows 11 machine running the lower-tier edition and restore it:

  RESTORE DATABASE [YourDB] 
  FROM DISK = 'C:\Backups\YourDB.bak' 
  WITH REPLACE;

• Confirm the database comes online without triggering Error 905.

Step 5: Upgrade the Edition Instead (If Partitioning Must Be Retained)

• If the partitioning design is required for business reasons, run the SQL Server 2025 Setup to perform an edition upgrade:

  setup.exe /QUIET /ACTION=EditionUpgrade /INSTANCENAME=MSSQLSERVER /PID=<YourEnterpriseKey> /IACCEPTSQLSERVERLICENSETERMS

• This avoids any data restructuring and immediately unlocks Enterprise-only features, including partitioning, on the existing instance.

Alternative or Verification Steps

• After applying Step 3/4, verify the fix by querying sys.partition_functions on the restored database — the result set should be empty, confirming no partitioning objects remain.
• Alternatively, run SELECT DATABASEPROPERTYEX('YourDB', 'Status'); to confirm the database status returns ONLINE rather than SUSPECT or remaining in a “Recovery Pending” state.

Troubleshooting Guide: SQL Server Error 907 — Inconsistent Database or File Metadata

Why This Error Happens

Error 907 occurs when SQL Server detects a mismatch between the metadata stored in the system catalog (sys.masterfiles, sys.databasefiles) and the actual physical state of the database files on disk. This is commonly triggered by an improperly completed file operation (such as an ALTER DATABASE ADD/REMOVE FILE that was interrupted), a restore from a backup that was taken mid-transaction on file structures, or manual tampering with .mdf/.ndf/.ldf files outside of SQL Server’s control. It can also surface after an unexpected server shutdown during a file-growth or shrink operation, leaving the metadata catalog out of sync with the actual file allocation map (FCB).

Step-by-Step Resolution Guide

Step 1: Verify Database and File State

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm the scope of corruption.
• Cross-check SELECT * FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName'); against the physical files present in the data directory (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Check the SQL Server Error Log (ERRORLOG file in the same directory) for accompanying messages referencing specific fileid or pageid values.

Step 2: Take the Database Offline and Attempt Metadata Repair

• Set the database to single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_REBUILD) WITH NO_INFOMSGS; first, since this is non-destructive.
• If metadata inconsistency persists, escalate only as a last resort to DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — this may remove unreadable or orphaned file references.
• Return to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Rebuild File Metadata Manually (If Repair Fails)

• Detach the database using EXEC sp_detach_db 'YourDatabaseName'; (ensure you have a verified backup first).
• Manually inspect and realign file paths using EXEC sp_attach_db with explicit file path parameters matching the actual .mdf/.ndf/.ldf locations.
• If a specific secondary file (.ndf) is missing or corrupted, use CREATE DATABASE YourDatabaseName ON (FILENAME = 'path') FOR ATTACH_REBUILD_LOG; to force SQL Server to reconstruct the log and validate file metadata.

Step 4: Restore From a Known Good Backup (If Unresolved)

• Restore the full backup to a new database name first: RESTORE DATABASE YourDatabaseName_Test FROM DISK = 'E:\Backups\YourDatabaseName.bak' WITH MOVE, REPLACE;
• Validate integrity on the restored copy with DBCC CHECKDB before cutting over production traffic.
• Apply any differential/log backups in sequence to minimize data loss.

Alternative or Verification Steps

• After remediation, run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; and confirm zero allocation or consistency errors are returned.
• As an alternative if repair tools fail entirely, use the SQL Server Management Studio (SSMS) “Database Properties > Files” page to manually compare the registered logical/physical file paths against the actual file system paths, then reconcile differences using ALTER DATABASE ... MODIFY FILE.

Troubleshooting Guide: SQL Server Error 902 — “To change the %ls option…”

Why This Error Happens

Error 902 occurs when a configuration option (such as database compatibility level, collation, or a database-scoped option) requires exclusive access to the database, but the operation is being attempted while other users or processes still hold active connections. SQL Server blocks these ALTER DATABASE operations because changing structural options mid-transaction could corrupt data consistency. This typically happens when the database is not properly set to SINGLE_USER mode, or when background processes (like SQL Server Agent jobs, replication, or monitoring tools) reconnect immediately after the mode switch.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections Blocking the Change

• Open SQL Server Management Studio (SSMS) and run the following query to check active sessions on the target database:

SELECT session_id, login_name, host_name, program_name, status 
FROM sys.dm_exec_sessions 
WHERE database_id = DB_ID('YourDatabaseName');

• Note any application names (e.g., SQL Server Agent, reporting services) that may auto-reconnect.

Step 2: Set the Database to SINGLE_USER Mode Safely

• Run the following command with ROLLBACK IMMEDIATE to forcibly disconnect all other sessions:

ALTER DATABASE YourDatabaseName 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Ensure you are connecting via a Dedicated Admin Connection (DAC) if the database is highly contended: sqlcmd -S admin:YourServerName.

Step 3: Apply the Required Configuration Change

• Immediately execute the original intended change (e.g., compatibility level or collation update) while in single-user mode:

ALTER DATABASE YourDatabaseName 
SET COMPATIBILITY_LEVEL = 170;

• Do not leave this session idle, as the single-user lock will be held only by your current connection.

Step 4: Revert to Multi-User Mode

• Once the change is successfully applied, release the lock to restore normal access:

ALTER DATABASE YourDatabaseName 
SET MULTI_USER;

• Verify in Windows Services (services.msc) that the SQL Server Agent (MSSQLSERVER) service did not attempt a reconnect during the transition on Windows Server 2025.

Alternative or Verification Steps

• Verify the Fix: Run SELECT DATABASEPROPERTYEX('YourDatabaseName', 'UserAccess'); and confirm the result returns MULTI_USER.
• Alternative Solution: If the error persists due to persistent background connections, stop the SQL Server Agent service via the Windows 11/Server 2025 Services console before attempting Step 2, then restart it after Step 4 is complete.

Troubleshooting Guide: Error 904 – Database Cannot Be Autostarted

Why This Error Happens

Error 904 occurs when SQL Server 2025 attempts to automatically bring a database online during service startup or shutdown but cannot do so successfully. This is typically caused by the database being marked as suspect, offline, or in an inconsistent recovery state due to corrupted data/log files, insufficient permissions on the SQL Server service account for the database file directories, or the database still being in the middle of a restore/recovery operation. It can also occur if the underlying storage volume (local disk, SAN, or cloud-attached storage) was not available or ready at the time Windows Server 2025 initialized the SQL Server service.

Step-by-Step Resolution Guide

Step 1: Check the Current State of the Database

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check database status:

  SELECT name, state_desc, is_in_standby, is_suspect_bit_set 
  FROM sys.databases;

• Look for statuses like SUSPECT, RECOVERY_PENDING, or OFFLINE next to the affected database.

Step 2: Verify File Permissions and Storage Availability

• Navigate to the default data directory, typically:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click the .mdf and .ldf files for the affected database, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If using SAN or cloud storage (Azure Disk, iSCSI), confirm the volume was mounted and online in Disk Management before the SQL Server service started.

Step 3: Review the SQL Server Error Log for Root Cause

• Open the error log located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for entries immediately preceding Error 904, such as “file activation failure,” “operating system error 5(Access is denied),” or “torn page detected.”
• Note any associated file paths or disk I/O errors for further action.

Step 4: Restore or Repair the Database

• If the database is marked SUSPECT, attempt to bring it back online using emergency mode:

  ALTER DATABASE [YourDB] SET EMERGENCY;
  DBCC CHECKDB([YourDB]);
  ALTER DATABASE [YourDB] SET ONLINE;

• If corruption is confirmed via DBCC CHECKDB, restore the database from the most recent valid backup using Restore Database in SSMS, ensuring the backup media is accessible from the Windows Server 2025 host.

Step 5: Adjust Startup Dependencies for Storage-Dependent Databases

• If the issue stems from storage not being ready at boot, open Services.msc, locate SQL Server (MSSQLSERVER), and set the startup type to Automatic (Delayed Start).
• This gives external storage subsystems (iSCSI, SAN, cloud disks) additional time to initialize before SQL Server attempts to autostart the database.

Alternative or Verification Steps

• After applying fixes, restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER, then re-run the sys.databases query to confirm the state shows ONLINE.
• If the database remains inaccessible, consider detaching and re-attaching it manually using sp_detach_db and CREATE DATABASE ... FOR ATTACH, ensuring all file paths are correct and accessible.

Why This Error Happens

Error 898 is logged when SQL Server’s Lazy Writer or Checkpoint process takes an unusually long time to scan the Buffer Pool for a specific database ID, usually because the buffer pool has grown very large (high “Max Server Memory”) relative to the speed of the underlying storage or NUMA memory access. This is often triggered during database shutdown, failover, or checkpoint operations when dirty pages must be scanned and flushed, and it worsens under memory pressure, NUMA node imbalance, or when antivirus/filter drivers intercept I/O to the data files. It can also appear after a sudden memory reconfiguration (e.g., Hyper-V dynamic memory ballooning) that forces SQL Server to re-validate large portions of its buffer pool.

Step-by-Step Resolution Guide

Step 1: Check Current Memory Configuration and NUMA Layout

• Run DBCC MEMORYSTATUS in SSMS to see current Buffer Pool size and NUMA node distribution.
• Open Task Manager > Performance > Memory on Windows Server 2025 to confirm physical RAM allocation matches what SQL Server expects.
• Verify “Max Server Memory” (sp_configure 'max server memory') is not set higher than physical RAM minus OS overhead (leave at least 10-15% for the OS).

Step 2: Rule Out Storage Latency as the Root Cause

• Open Perfmon.exe and add the counters: Avg. Disk sec/Read, Avg. Disk sec/Write, and SQLServer:Buffer Manager > Page life expectancy.
• Anything consistently above 20ms on disk latency counters indicates the storage subsystem, not SQL Server, is the bottleneck.
• If using virtualized storage (Hyper-V/VMware), confirm the VHDX/VMDK files are on fixed-size disks, not dynamically expanding ones.

Step 3: Disable Dynamic Memory Ballooning (If Virtualized)

• In Hyper-V Manager, right-click the SQL Server VM > Settings > Memory.
• Uncheck “Enable Dynamic Memory” and assign a fixed static memory value instead.
• Restart the VM and confirm SQL Server starts with the full static memory allocation (check via sys.dm_os_sys_memory).

Step 4: Exclude SQL Server Data Files from Antivirus Scanning

• Navigate to Windows Security > Virus & threat protection > Exclusions.
• Add exclusions for the .mdf, .ndf, and .ldf file paths (commonly under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Restart the SQL Server service via services.msc to apply the exclusion cleanly.

Step 5: Rebuild Buffer Pool Extension Settings (If Enabled)

• Run sp_configure to check if Buffer Pool Extension (BPE) is enabled on slow SSD/NVMe storage.
• If BPE is pointed to a slow disk, disable it with ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF; and re-enable it pointing to a faster NVMe drive.

Alternative or Verification Steps

• After applying changes, monitor the SQL Server Error Log (EXEC xp_readerrorlog) for 24-48 hours to confirm Error 898 no longer appears during checkpoints or shutdowns.
• If the error persists, run DBCC CHECKDB against the affected database ID to rule out page-level corruption as a contributing factor to the extended scan time.

Troubleshooting Guide: Error 899 – Failed to Disable Resilient Buffer Pool Extension

Why This Error Happens

Error 899 typically occurs when SQL Server 2025 attempts to disable the Resilient Buffer Pool Extension (BPE) while active transactions or checkpoint operations are still referencing the extension file. This can also be triggered by insufficient file-system permissions on the BPE cache file path, or because the extension file has become locked or corrupted due to an unexpected service interruption. Additionally, if the underlying storage volume (often NVMe or SSD-backed) is in a degraded or disconnected state, SQL Server cannot safely flush and release the resilient buffer pool pages.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to confirm BPE state:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Check the state_description column — if it shows IN_TRANSITION or ERROR, this confirms a stuck disable operation.

Step 2: Check File Permissions and Lock Status

• Navigate to the BPE cache file location, typically:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ResilientBPE.BPE
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions on this file and its parent directory.
• Use Resource Monitor or handle.exe (Sysinternals) to confirm no orphaned process is locking the .BPE file.
• If locked, restart the SQL Server service to release the handle:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 3: Manually Disable the Buffer Pool Extension

• Run the disable command explicitly through T-SQL rather than relying on GUI toggles:

  ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION OFF;

• If Error 899 persists, stop the SQL Server service first, then delete the .BPE file manually before restarting:

  Stop-Service -Name 'MSSQLSERVER' -Force
  Remove-Item "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ResilientBPE.BPE" -Force
  Start-Service -Name 'MSSQLSERVER'

Step 4: Validate Storage and Driver Health

• On Windows Server 2025 / Windows 11, open Device Manager and confirm the storage controller drivers (NVMe/SSD) are up to date.
• Run chkdsk on the volume hosting the BPE file to rule out underlying disk corruption:

  chkdsk D: /f /r

Alternative or Verification Steps

• After applying the fix, re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and confirm state_description returns DISABLED_FULL_RECOVERY or DISABLED.
• If the error still occurs, failover to a secondary replica (if using Always On Availability Groups) and perform the disable operation there first, then repeat on the primary node after sync.

Troubleshooting Guide: SQL Server Error 900 – Buffer Pool Extension File Configuration Mismatch

Why This Error Happens

This error occurs when SQL Server 2025 attempts to restart or recover the Buffer Pool Extension (BPE) using a configuration (file path, file size, or block size) that does not match the metadata stored from the previous session. This commonly happens when the underlying BPE cache file (.BPE) was manually moved, deleted, resized outside of T-SQL commands, or when the drive hosting the extension file was disconnected during a Windows Server 2025 update or reboot. Since SQL Server validates the BPE file signature against system catalog views (sys.dm_os_buffer_pool_extension_configuration) at startup, any mismatch between the physical file and the expected configuration triggers a hard failure rather than a silent re-creation.

Step-by-Step Resolution Guide

Step 1: Verify the Current Buffer Pool Extension Configuration

• Open SQL Server Management Studio (SSMS) or a command-line tool with sysadmin access.
• Run the following query to check the last known configuration state:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Note the path and current_size_in_kb values, and compare them against the actual file properties on disk (e.g., D:\SQLCache\BPE_Cache.bpe).

Step 2: Disable the Buffer Pool Extension Cleanly

• Execute the following T-SQL command to turn off BPE before making changes:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

• If the command fails due to the service being in a crashed loop, stop the SQL Server service via services.msc or PowerShell:

Stop-Service -Name 'MSSQLSERVER' -Force

Step 3: Remove or Relocate the Corrupted Cache File

• Navigate to the file path specified in Step 1 (e.g., D:\SQLCache\).
• Delete the existing .bpe file if it is orphaned or mismatched in size.
• Ensure the target directory has correct NTFS permissions, granting Full Control to the NT SERVICE\MSSQLSERVER account.

Step 4: Re-provision the Buffer Pool Extension

• Restart the SQL Server service via services.msc or PowerShell:

Start-Service -Name 'MSSQLSERVER'

• Re-enable the BPE with a fresh configuration pointing to a stable, high-speed storage volume (SSD/NVMe recommended):

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'D:\SQLCache\BPE_Cache.bpe', SIZE = 10 GB);

Alternative or Verification Steps

• Verification: Query sys.dm_os_buffer_pool_extension_configuration again to confirm the state_description column reads NORMAL rather than DISABLED or CORRUPT.
• Alternative Fix: If the error persists after recreation, check the Windows Event Viewer under Application Logs for underlying disk I/O errors (Event ID 833), as a failing physical disk sector is often the root cause of a corrupted BPE handle.

Troubleshooting Guide: SQL Server Error 896 – Failed to Disable Buffer Pool Extension

Why This Error Happens

Error 896 typically occurs when SQL Server attempts to disable the Buffer Pool Extension (BPE) while active memory-mapped I/O operations are still referencing the BPE cache file, preventing a clean handoff. This can also happen if the BPE configuration state is corrupted in the server’s internal metadata, or if the service account lacks the necessary file-handle permissions to release the .bpe file on disk. In some cases, a pending transaction or an improperly terminated previous ALTER SERVER CONFIGURATION command leaves the extension in a transitional “disabling” state that blocks subsequent attempts.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to check BPE state:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm whether state shows 0 (disabled), 1 (enabled), or a transitional value indicating a stuck process.

Step 2: Terminate Blocking Sessions and Retry Disable Command

• Identify any active sessions holding locks on system resources using:

SELECT * FROM sys.dm_exec_requests WHERE command LIKE '%BUFFER POOL%';

• Kill any blocking session IDs (SPIDs) found using KILL <session_id>;.
• Re-attempt the disable command:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

Step 3: Check File Permissions and Service Account Access

• Navigate to the directory where the .bpe file resides (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Right-click the .bpe file, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If permissions are missing, grant Full Control, apply changes, and restart the SQL Server service via services.msc or:

Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Restart SQL Server Service and Clear Transitional State

• Fully stop the SQL Server instance using PowerShell:

Stop-Service -Name 'MSSQLSERVER' -Force

• Manually delete the orphaned .bpe file if it still exists on disk (ensure no process is locking it via Resource Monitor).
• Restart the service and re-run the ALTER SERVER CONFIGURATION command to disable BPE cleanly.

Alternative or Verification Steps

• After applying the fix, re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and confirm the state column returns 0, indicating BPE is fully disabled.
• If the error persists, as an alternative, use sp_configure 'buffer pool extension', 0; followed by RECONFIGURE; to force the setting through server-level configuration rather than the ALTER SERVER CONFIGURATION syntax.

Troubleshooting Guide: SQL Server Error 897 – Buffer Pool Extension Paused

Why This Error Happens

Error 897 occurs when SQL Server 2025 cannot maintain the Buffer Pool Extension (BPE) file on the designated SSD cache drive, typically because the storage volume has run out of disk space or the underlying file has become inaccessible. This can also happen if the SQL Server service account loses read/write permissions to the BPE file path, or if the physical disk reports I/O errors due to drive failure or sudden disconnection. In virtualized or cloud-hosted Windows Server 2025 environments, this is frequently triggered by auto-shrinking storage volumes or snapshot operations that temporarily lock the file.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Drive Health

• Open File Explorer or run Get-PSDrive in PowerShell to confirm the BPE host drive has sufficient free space (SQL Server requires headroom equal to the configured BPE size).
• Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings (Event ID 7, 11, or 51) indicating hardware-level I/O failures.
• If the drive is full, clear unnecessary files or expand the volume using Disk Management.

Step 2: Validate File Permissions and Service Account Access

• Navigate to the BPE file location (commonly D:\SQLCache\BufferPoolExtension.bpe or wherever configured).
• Right-click the file/folder, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If permissions are missing, grant them and restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Disable and Re-enable Buffer Pool Extension

• Open SQL Server Management Studio (SSMS) or a query window and run:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

• Confirm the file is released (no longer locked) using Resource Monitor or handle.exe from Sysinternals.
• Re-enable it with a fresh path and size definition:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'E:\SQLCache\BPE_new.bpe', SIZE = 16384 MB);

Step 4: Check for Underlying Storage Corruption

• Run chkdsk E: /f /r (replace with your BPE drive letter) during a scheduled maintenance window to repair file system errors.
• If using Storage Spaces or a SAN-attached LUN, verify the volume status in Server Manager > File and Storage Services for “Degraded” or “Offline” states.

Alternative or Verification Steps

• Verify the fix: Query sys.dm_os_buffer_pool_extension_configuration in SSMS — the state_description column should read “NORMAL” instead of “ERROR” or “PAUSED”.
• Alternative workaround: If the original SSD/cache drive is unreliable, relocate the BPE file to a different high-speed volume (e.g., a local NVMe disk) and reconfigure the ALTER SERVER CONFIGURATION statement to point to the new path permanently.

Troubleshooting Guide: SQL Server Error 894 – Buffer Pool Hash Pages Allocation

Why This Error Happens

Error 894 occurs when the SQL Server Database Engine attempts to allocate memory for buffer pool hash pages but encounters insufficient contiguous memory or an unstable memory configuration at the OS level. This is typically tied to misconfigured “Max Server Memory” settings, Lock Pages in Memory privileges not being granted, or memory pressure caused by competing processes on Windows Server 2025. In some cases, this is also triggered by fragmented virtual address space when the buffer pool attempts to scale under high transactional load.

Step-by-Step Resolution Guide

Step 1: Verify and Adjust SQL Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run EXEC sp_configure 'show advanced options', 1; RECONFIGURE; followed by EXEC sp_configure 'max server memory'; to check current limits.
• Ensure “max server memory” is set at least 2-4 GB below total physical RAM to leave room for OS and hash page overhead.
• Example: EXEC sp_configure 'max server memory', 24576; RECONFIGURE; (for a 32GB system).

Step 2: Grant Lock Pages in Memory Privilege

• Open secpol.msc on Windows Server 2025.
• Navigate to Local Policies > User Rights Assignment > Lock Pages in Memory.
• Add the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER) to this policy.
• Restart the SQL Server service via services.msc for the change to take effect.

Step 3: Check for Memory Fragmentation and Virtual Address Space

• Open Windows Performance Monitor (perfmon) and add the counter SQLServer:Memory Manager > Total Server Memory (KB) and Target Server Memory (KB).
• Compare these values; a large gap indicates the engine cannot acquire contiguous memory for hash pages.
• If fragmentation is confirmed, restart the SQL Server service during a maintenance window to reset the buffer pool allocation map.

Step 4: Update SQL Server 2025 Cumulative Updates

• Navigate to the Microsoft Update Catalog and check for the latest Cumulative Update (CU) for SQL Server 2025.
• Install the CU using elevated permissions: right-click the installer and select Run as Administrator.
• Reboot the Windows Server 2025 host after installation to clear any residual memory locks.

Alternative or Verification Steps

• After applying fixes, run DBCC MEMORYSTATUS; in SSMS and confirm no error entries appear in the Buffer Pool section related to hash page allocation.
• If the error persists under heavy load, consider enabling Trace Flag 845 (for non-Lock Pages scenarios) temporarily to bypass large page allocation checks while monitoring stability via Event Viewer.

Troubleshooting Guide: SQL Server Hybrid Buffer Pool Error 895

Why This Error Happens

Error 895 occurs when SQL Server 2025’s Hybrid Buffer Pool feature calculates the number of hash pages required to map Persistent Memory (PMEM) and encounters a configuration mismatch between the DAX-formatted volume size and the available non-volatile memory. This typically happens when the Persistent Memory volume is improperly aligned, the PMEM hardware is not correctly initialized in App Direct mode, or the underlying NVDIMM/Optane device drivers on Windows Server 2025 are outdated. Additionally, this message can surface as informational noise rather than a true failure if SQL Server is simply logging its internal memory-mapping proposal during startup diagnostics.

Step-by-Step Resolution Guide

Step 1: Verify Persistent Memory Hardware Configuration

• Open Windows Admin Center or ipmctl show -dimm to confirm NVDIMMs are recognized and healthy.
• Ensure the PMEM modules are configured in App Direct Mode (not Memory Mode), since Hybrid Buffer Pool only supports App Direct.
• Run Get-PmemDisk in PowerShell to confirm the disk is visible to the OS.

Step 2: Validate the DAX Volume Formatting

• Confirm the volume is formatted with DAX (Direct Access) enabled using:
  Get-Volume | Where-Object FileSystem -eq "NTFS" | Select DriveLetter, DedupMode
• Reformat if necessary using:
  Format-Volume -DriveLetter F -FileSystem NTFS -DaxMode Enabled
• Ensure the volume is not nested inside Storage Spaces Direct without proper PMEM passthrough.

Step 3: Update Persistent Memory and Storage Drivers

• Navigate to Device Manager > Persistent Memory Controller.
• Update drivers to the latest version certified for Windows Server 2025 (check vendor site, e.g., Intel Optane PMem drivers).
• Reboot the server after driver installation to ensure proper DAX re-initialization.

Step 4: Reconfigure SQL Server Hybrid Buffer Pool Settings

• Open SQL Server Configuration Manager and stop the SQL Server service.
• Edit the startup parameter or use ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED_HYBRID_BUFFER_POOL = ON;
• Restart the SQL Server instance and monitor the SQL Error Log for Event ID 895 recurrence.

Alternative or Verification Steps

• Check the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\) to confirm the hash page proposal completes without subsequent failure messages.
• If the error persists, disable Hybrid Buffer Pool entirely via ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED_HYBRID_BUFFER_POOL = OFF; as a temporary workaround while hardware diagnostics are performed.

Troubleshooting Guide: SQL Server Error 892 (Shared Memory in Memory Manager)

Why This Error Happens

Error 892 typically occurs when SQL Server 2025’s memory manager encounters restrictions while attempting to allocate or utilize shared memory segments, often due to insufficient “Lock Pages in Memory” privileges or conflicting memory configurations. This can also arise from corrupted shared memory handles left over from an improper shutdown, or from another process (such as a secondary SQL instance or antivirus software) interfering with the memory-mapped files SQL Server relies on. Additionally, misconfigured Max Server Memory settings relative to available physical RAM on Windows Server 2025 can trigger this allocation failure.

Step-by-Step Resolution Guide

Step 1: Verify and Grant Required Memory Permissions

• Open Local Security Policy (secpol.msc) or Group Policy Editor on the Windows Server 2025 machine.
• Navigate to Security Settings > Local Policies > User Rights Assignment.
• Locate Lock Pages in Memory and ensure the SQL Server service account is added.
• Restart the SQL Server service for the policy to take effect:

net stop MSSQLSERVER
net start MSSQLSERVER

Step 2: Clear Orphaned Shared Memory Handles

• Fully stop all SQL Server-related services, including SQL Server Agent and Full-Text Search.
• Open Task Manager and confirm no sqlservr.exe processes remain active in the background.
• Check C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ for ERRORLOG entries referencing memory handle conflicts.
• Restart the Windows Server to force a release of any locked shared memory segments.

Step 3: Adjust Max Server Memory Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check current memory allocation:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory';

• Set the Max Server Memory to a value that leaves at least 20% of physical RAM for the OS:

EXEC sp_configure 'max server memory', 8192; -- Example: 8GB
RECONFIGURE;

Step 4: Check for Antivirus or Third-Party Interference

• Add exclusions for the SQL Server data directory (e.g., D:\SQLData\) and the sqlservr.exe binary path in your antivirus software.
• Temporarily disable real-time scanning to test if the error persists.
• Review Windows Defender or third-party AV logs for blocked memory-mapped file access events correlating with Event ID 892.

Alternative or Verification Steps

• Verify the fix: Open the Windows Event Viewer, filter for Event ID 892 under the Application log, and confirm no new occurrences appear after a full server restart and SQL service cycle.
• Alternative solution: If the error persists, run SQL Server setup in repair mode (setup.exe /ACTION=REPAIR) to rebuild potentially corrupted memory manager binaries and configuration files.

Troubleshooting Guide: SQL Server Error 893 – Failed to Release Latch

Why This Error Happens

Error 893 occurs when SQL Server’s Buffer Manager cannot release a latch on a specific page (identified by BUF address, Database ID, File ID, and Page ID) after an I/O operation completes. This is typically caused by underlying storage subsystem instability, disk I/O timeouts, or memory corruption affecting the buffer pool. It can also surface due to faulty storage drivers, antivirus interference on data files, or hardware-level disk controller failures on Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Storage Subsystem Health

• Check Windows Event Viewer under System and Application logs for related Disk, ntfs, or iSCSI warnings (Event ID 153, 129, or 51).
• Run chkdsk /f /r on the volume hosting the affected database files (requires offline/reboot for OS drive).
• Confirm SAN/NAS connectivity and multipath drivers (MPIO) are up to date if using networked storage.

Step 2: Run DBCC CHECKDB to Identify Corruption

• Open SQL Server Management Studio (SSMS) and execute:

DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review output for consistency errors tied to the Page ID reported in the 893 error.
• If corruption is confirmed, restore from the most recent clean backup rather than attempting in-place repair.

Step 3: Exclude SQL Server Files from Antivirus/Endpoint Scanning

• Navigate to your antivirus console and add exclusions for:
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• *.mdf, *.ldf, *.ndf file extensions
• Restart the SQL Server (MSSQLSERVER) service via services.msc to apply changes.

Step 4: Update Storage and Network Drivers

• Open Device Manager on Windows Server 2025 and update storage controller and NIC drivers (especially for RAID/HBA cards).
• Apply the latest cumulative update (CU) for SQL Server 2025 via SQL Server Setup or Microsoft Update.

Step 5: Increase I/O Timeout Thresholds

• Edit the registry key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Disk

• Set TimeOutValue to 60 (decimal) if currently lower, then reboot the server to apply.

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB again and monitor SQL Server Error Log for recurrence of Error 893 over a 24–48 hour window.
• If the issue persists despite clean disk/storage checks, failover to a secondary replica (if using Always On Availability Groups) and rebuild the primary node from backup to rule out hardware-level memory corruption.

Troubleshooting Guide: Error 891 – Buffer Pool Extension Not Supported

Why This Error Happens

Error 891 occurs when SQL Server 2025 attempts to enable the Buffer Pool Extension (BPE) feature on a platform or edition that does not support it. This typically happens because BPE is restricted to specific SQL Server editions (such as Standard or Enterprise) and is not available on Express, Web, or certain containerized/ARM-based deployments. Additionally, this can surface if the underlying OS (Windows Server 2025 / Windows 11) is running on an unsupported architecture (e.g., ARM64) or if the SSD/NVMe caching device path is incompatible with the BPE driver requirements.

Step-by-Step Resolution Guide

Step 1: Verify SQL Server Edition Compatibility

• Run SELECT SERVERPROPERTY('Edition'); in SSMS to confirm your current edition.
• Confirm that Buffer Pool Extension is only supported on Standard, Enterprise, and Developer editions of SQL Server 2025.
• If running Express or Web edition, BPE is not supported and the feature must be disabled in your configuration scripts.

Step 2: Check Platform Architecture

• Open PowerShell as Administrator and run Get-ComputerInfo | Select-Object CsSystemType to confirm you are on x64 architecture.
• Buffer Pool Extension is not supported on ARM64 builds of Windows Server 2025 or Windows 11.
• If running on ARM, migrate the workload to an x64-based instance or disable BPE entirely.

Step 3: Validate Storage Path and Permissions

• Ensure the BPE file path points to a local SSD or NVMe drive, not a network share or mapped drive.
• Check that the SQL Server service account has Read/Write permissions on the target directory (e.g., D:\SQLCache\BPE.bpe).
• Run the following T-SQL to manually configure BPE with correct syntax:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'D:\SQLCache\BPE.bpe', SIZE = 50 GB);

Step 4: Update SQL Server Cumulative Updates

• Navigate to Microsoft’s official download center and install the latest Cumulative Update (CU) for SQL Server 2025.
• Some platform-detection bugs related to BPE eligibility are resolved in later CUs; verify via SELECT @@VERSION; post-installation.

Alternative or Verification Steps

• After applying fixes, verify BPE status by querying sys.dm_os_buffer_pool_extension_configuration; a state_description of “RUNNING” confirms success.
• If the error persists due to hardware/platform restrictions, disable BPE entirely using ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; and rely on increased physical RAM allocation as an alternative performance solution.

Troubleshooting Guide: SQL Server Error 889 – “Resilient Buffer Pool Extension Must Be Disabled”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to perform a maintenance or configuration operation—such as resizing memory, changing recovery settings, or modifying the Buffer Pool Extension (BPX) file—while the Resilient Buffer Pool Extension feature is still actively enabled. SQL Server requires the BPX to be temporarily disabled for these specific operations because the resilient caching layer locks the underlying memory-mapped file structure, preventing safe reconfiguration. This typically happens after an unexpected server restart or power loss, where the resiliency feature automatically re-engaged to protect dirty pages before the administrator finished their maintenance task.

Step-by-Step Resolution Guide

Step 1: Identify the Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) and connect to the instance throwing Error 889.
• Run the following query to check the current configuration state:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Note the state_description column; if it reads RESILIENT or ENABLED, proceed to the next step.

Step 2: Disable the Buffer Pool Extension Temporarily

• Execute the T-SQL command to disable the extension before running your original operation (e.g., ALTER DATABASE, memory config changes):

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION OFF;

• Ensure the command completes without errors; this releases the lock on the extension file (usually located in a path like D:\SQLCache\BPE.bpe).
• Re-run the operation or query that originally triggered Error 889.

Step 3: Clear the Resiliency Flag via Registry (If Step 2 Fails)

• If the ALTER SERVER CONFIGURATION command hangs or fails because the resiliency flag is stuck in memory (common after a Windows Server 2025 crash dump), stop the SQL Server service entirely.
• Navigate to the registry path: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\BufferPoolExtension.
• Manually verify or reset the ResilientFlag DWORD value to 0, then restart the SQL Server (MSSQLSERVER) service via services.msc.

Step 4: Re-enable Buffer Pool Extension (Optional)

• Once the original maintenance operation (like a failover cluster update or memory resize) is successful, re-enable the BPX for performance benefits.
• Use the following syntax, specifying the file path and size:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'D:\SQLCache\BPE.bpe', SIZE = 50 GB);

Alternative or Verification Steps

• Verification: Re-run the sys.dm_os_buffer_pool_extension_configuration query; the state_description should now show ENABLED or OFF (not RESILIENT), and the specific operation that threw Error 889 should complete successfully.
• Alternative Fix: If the error persists despite disabling the feature via T-SQL, delete the physical .bpe cache file from the disk (while the SQL service is stopped) to force SQL Server to rebuild the extension metadata from scratch upon the next service start.

Troubleshooting Guide: SQL Server Error 890 — “Could Not Create Getting Data”

Why This Error Happens

Error 890 typically occurs when SQL Server 2025 is unable to allocate or initialize a required resource object during a query or data retrieval operation, often due to insufficient disk space in the tempdb directory or restrictive NTFS permissions on the SQL Server service account. This can also result from a corrupted or misconfigured database file path on Windows Server 2025, where the Database Engine cannot write temporary structures needed to process the “Getting Data” request. In some cases, antivirus or Windows Defender real-time scanning locks the file handle before SQL Server can complete the structure creation.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Tempdb Location

• Open SQL Server Management Studio (SSMS) and run EXEC sp_helpdb tempdb; to confirm the current file path.
• Check available disk space on the drive hosting tempdb via File Explorer or Get-PSDrive in PowerShell.
• If space is below 10% free, move tempdb to a drive with more capacity using ALTER DATABASE tempdb MODIFY FILE.

Step 2: Correct Service Account Permissions

• Navigate to the SQL Server data directory (default: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Right-click the folder, select Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Restart the SQL Server service via services.msc or run Restart-Service -Name MSSQLSERVER in an elevated PowerShell session.

Step 3: Exclude SQL Directories from Antivirus Scanning

• Open Windows Security > Virus & threat protection > Manage settings > Add or remove exclusions.
• Add exclusions for the SQL Server data, log, and tempdb folders to prevent file-locking conflicts.
• Re-run the failing query or data import job to confirm the structure creation completes without interruption.

Step 4: Check for Corrupted System Catalog Objects

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect corruption.
• Review the output for allocation errors tied to Error 890.
• If corruption is confirmed, restore from the most recent verified backup.

Alternative or Verification Steps

• Confirm the fix by re-executing the original query or ETL job and checking the SQL Server Error Log (EXEC xp_readerrorlog;) for the absence of Error 890.
• If the issue persists, failover to a secondary replica (if using Always On Availability Groups) and rebuild the tempdb database on the primary node using net stop MSSQLSERVER followed by a manual tempdb file relocation.

Troubleshooting Guide: SQL Server Error 886 — Resilient Buffer Pool Extension

Why This Error Happens

Error 886 is an informational message rather than a critical failure. It occurs when SQL Server 2025 detects that the Resilient Buffer Pool Extension (RBPEX) feature is already in an enabled state or is currently in the process of initializing during startup or recovery. This typically happens when a configuration script or startup trace flag attempts to re-enable the feature while a prior instance restart or failover (in an Always On Availability Group) has not yet fully completed the initialization handshake.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check the current state:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Look at the state_description column; if it shows RUNNING, no action is required as the system is functioning as intended.

Step 2: Check for Redundant Startup Configurations

• Navigate to the SQL Server configuration files located at:
  C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG
• Search for duplicate ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION commands in your deployment or Powershell provisioning scripts.
• Remove or comment out redundant initialization scripts that attempt to enable the extension on every service restart.

Step 3: Clear Pending Recovery States (If Applicable)

• If the error persists alongside connectivity issues, restart the SQL Server service to clear any “pending” initialization locks.
• Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and select Restart.
• Ensure the .BPE file path defined in your configuration (usually on a high-speed SSD drive) is accessible and not locked by another process.

Step 4: Review Always On Availability Group Sync (If Clustered)

• If running in a High Availability setup, check the Failover Cluster Manager to ensure the resource is not stuck in a “pending” state.
• Confirm that the Buffer Pool Extension configuration is synchronized across all secondary replicas to prevent a conflict message during failover events.

Alternative or Verification Steps

• Verification: Re-run the sys.dm_os_buffer_pool_extension_configuration query. If state equals 2 (Running), the feature is stable and the Error 886 message can be safely ignored as a benign notification.
• Alternative: If instability continues, manually disable and re-enable the feature via a single ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; command, followed by a service restart, before re-enabling it with a fresh file path.

Troubleshooting Guide: SQL Server Error 887 – Invalid RBPEX Operation

Why This Error Happens

Error 887 occurs when SQL Server’s Resident Buffer Pool Extension (RBPEX) subsystem encounters an invalid operation while managing the buffer pool extension cache file, typically due to corruption in the extension file or a mismatch between the configured cache size and available disk/memory resources. This can also happen if the underlying storage device backing the buffer pool extension file was disconnected, resized, or experienced I/O errors mid-transaction. Additionally, insufficient permissions on the SQL Server service account to read/write the BPE cache file path can trigger this invalid state.

Step-by-Step Resolution Guide

Step 1: Disable and Reconfigure Buffer Pool Extension

• Open SQL Server Management Studio (SSMS) or a query window and run:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

• Verify the extension is disabled by checking sys.dm_os_buffer_pool_extension_configuration.
• Delete the existing .BPE cache file from its configured path (commonly C:\SQLCache\BPE.BPE).

Step 2: Validate Storage and File System Integrity

• Run chkdsk C: /f /r on the drive hosting the BPE file (replace C: with the correct volume).
• Confirm the drive is NTFS-formatted and not a network-mapped or removable device, as RBPEX requires local SSD-backed storage.
• Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings correlating with the SQL error timestamp.

Step 3: Re-enable Buffer Pool Extension with Correct Permissions

• Ensure the SQL Server service account has Full Control on the target BPE directory (e.g., C:\SQLCache) via Properties > Security in File Explorer.
• Re-enable the extension with a size appropriate to available physical RAM (typically 4–10x RAM size):

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'C:\SQLCache\BPE.BPE', SIZE = 50GB);

• Restart the SQL Server service via SQL Server Configuration Manager.

Step 4: Apply Latest Cumulative Update

• Open SQL Server Setup and check the current build via SELECT @@VERSION;.
• Download and install the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, as RBPEX stability fixes are frequently shipped in CUs.
• Reboot the Windows Server 2025 / Windows 11 host after installation.

Alternative or Verification Steps

• Verify the fix by monitoring sys.dm_os_buffer_pool_extension_configuration and checking the SQL Server Error Log for recurrence of Error 887 over a 24-hour period.
• If the error persists, disable Buffer Pool Extension permanently (BUFFER POOL EXTENSION OFF) and rely on physical RAM allocation alone, especially if running on a VM without guaranteed SSD passthrough.

Troubleshooting Guide: SQL Server Error 888 – Resilient Buffer Pool Extension Required

Why This Error Happens

This error occurs when a SQL Server operation (such as resuming from a suspended state or performing a specific memory-resilient transaction) requires the Resilient Buffer Pool Extension (BPE) feature to be active, but it is currently disabled or improperly configured in the instance settings. In SQL Server 2025, operations tied to persistent memory (PMEM) or accelerated recovery rely on this extension to maintain buffer pool state across restarts; if the configuration file path is missing, corrupted, or the feature was never enabled via sp_configure, the engine blocks the operation to prevent data inconsistency. Additionally, this can happen if the underlying storage device assigned to the buffer pool extension does not support the resiliency flags required by Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance via a New Query window.
• Run the following T-SQL command to check the current configuration:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm if the state column shows 0 (disabled) or 3 (error), which confirms the root cause.

Step 2: Enable the Resilient Buffer Pool Extension

• Ensure you have a high-speed, resilient storage path (e.g., NVMe or PMEM mapped drive) available, such as S:\SSD_Cache.
• Execute the enabling command, specifying the file path and size (in MB):

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'S:\SSD_Cache\SQLServerBPE.BPE', SIZE = 4096 MB);

• If the command fails due to file locks, ensure the S:\SSD_Cache directory exists and the SQL Server Service Account has Full Control permissions via Windows File Explorer (Properties > Security tab).

Step 3: Restart the SQL Server Service

• Open the Services app (services.msc) on Windows Server 2025 or Windows 11.
• Locate SQL Server (MSSQLSERVER) or the specific named instance.
• Right-click and select Restart to apply the new buffer pool extension settings.
• Alternatively, use PowerShell to restart the service remotely:

  Restart-Service -Name 'MSSQLSERVER' -Force

Step 4: Re-run the Failed Operation

• Navigate back to the specific maintenance task, backup, or query that triggered Error 888 (referenced in the %ls placeholder of the error log).
• Execute the operation again to confirm the engine now recognizes the active buffer extension.

Alternative or Verification Steps

• Verification: Re-run the query from Step 1. The state column should now display 1 (Enabled), and current_size_in_kb should reflect the size you configured (e.g., 4,194,304 KB for a 4GB cache).
• Alternative Fix: If enabling the BPE fails due to storage incompatibility, check the Windows Event Viewer (Windows Logs > Application) for disk-specific errors. You may need to format the target drive using ReFS instead of NTFS, as ReFS offers better resiliency support required by SQL Server 2025’s persistent memory features.

Why This Error Happens

Error 883 typically occurs when SQL Server’s Accelerated Database Recovery (ADR) feature attempts to initialize the Write Page Recorder (WPR) but cannot persist the wpr_bucket_table due to insufficient disk space, corrupted system catalog metadata, or restrictive NTFS/service account permissions on the data file directory. This is commonly triggered after an in-place upgrade to SQL Server 2025 where the database compatibility level was changed without a corresponding checkpoint of the persisted version store (PVS). In rarer cases, a third-party antivirus or Windows Defender real-time scanning policy locks the .mdf/.ndf file handles during the internal table creation transaction.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File Growth Settings

• Check the volume hosting the database’s data files (.mdf/.ndf) has at least 15–20% free space; WPR tables require contiguous allocation during creation.
• Run EXEC sp_helpdb 'YourDatabaseName'; in SSMS to confirm autogrowth is not set to a restrictive fixed MB value.
• If space is low, expand the volume in Disk Management or move secondary files using ALTER DATABASE ... MODIFY FILE.

Step 2: Validate Service Account Permissions

• Open Services.msc on Windows Server 2025 / Windows 11 and confirm the SQL Server service account has Full Control on the directory path (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Right-click the DATA folder → Properties → Security → add the service account if missing, then apply recursively.
• Restart the SQL Server (MSSQLSERVER) service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Disable Accelerated Database Recovery Temporarily

• Run the following T-SQL to turn off ADR, which forces SQL Server to rebuild the WPR structures cleanly on re-enable:
• ALTER DATABASE YourDatabaseName SET ACCELERATED_DATABASE_RECOVERY = OFF;
• Wait for the PVS cleanup background task to complete (monitor via sys.dm_tran_persistent_version_store_stats).
• Re-enable ADR: ALTER DATABASE YourDatabaseName SET ACCELERATED_DATABASE_RECOVERY = ON;

Step 4: Exclude SQL Directories from Antivirus Scanning

• Add exclusions in Windows Security (or your third-party AV console) for the SQL Server DATA, LOG, and TempDB directories.
• Reboot the server to ensure file locks are released before retrying the database operation that triggered Error 883.

Alternative or Verification Steps

• Confirm the fix by re-running the triggering transaction or executing DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS; to ensure no residual corruption remains.
• If the error persists, restore the database from the last known good backup to a test instance and attempt the ADR toggle there first, isolating whether the issue is environmental (disk/permissions) or data corruption-based.

Troubleshooting Guide: SQL Server Error 885 — “An Internal Error Occurred”

Why This Error Happens

Error 885 is a generic internal error raised by the SQL Server Database Engine when an unexpected condition occurs in a code module, often pointing to a deeper issue such as corrupted system metadata, a damaged database page, or an unhandled exception within a specific internal context (identified by the %ls::%d values in the message). This is frequently triggered by disk-level corruption, an incompatible or outdated build of SQL Server 2025 interacting with Windows Server 2025/Windows 11 security policies, or insufficient permissions on the SQL Server service account preventing access to critical system resources. Because the error code and context string vary by instance, it typically requires correlating the SQL Server error log with Windows Event Viewer to isolate the exact failing module.

Step-by-Step Resolution Guide

Step 1: Capture the Full Error Context and Correlate Logs

• Open SQL Server Management Studio (SSMS) and check ERRORLOG located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Cross-reference the exact timestamp with Windows Event Viewer under Windows Logs > Application and Windows Logs > System.
• Note the full hex error code and the %ls::%d context string — this identifies the specific internal module (e.g., storage engine, query optimizer) causing the fault.

Step 2: Run Database Consistency and Integrity Checks

• Open a new query window in SSMS and run:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found, review repair options (REPAIR_REBUILD is preferred over REPAIR_ALLOW_DATA_LOSS).
• Also run DBCC CHECKDB against tempdb and master, since internal errors often stem from system database corruption.

Step 3: Verify Service Account Permissions and File System Integrity

• Open Services.msc and confirm the SQL Server service account has full control over:
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log
• Run sfc /scannow and chkdsk C: /f /r from an elevated Command Prompt to rule out underlying NTFS or system file corruption on Windows Server 2025/Windows 11.
• Restart the SQL Server service after permission or file system corrections.

Step 4: Apply the Latest Cumulative Update

• Check the installed build via:

  SELECT @@VERSION;

• Download and apply the latest Cumulative Update (CU) for SQL Server 2025 from the Microsoft Update Catalog, since Error 885 is often resolved in engine-level hotfixes.
• Reboot the server post-installation and confirm the new build number matches the patch notes.

Alternative or Verification Steps

• After applying fixes, re-run the failing query or workload that originally triggered Error 885 and monitor ERRORLOG for recurrence over a 24-hour period.
• If the error persists, restore the affected database from a known-good backup to a test instance and run DBCC CHECKDB there to confirm whether the corruption is isolated to production storage or if it indicates a wider hardware/disk subsystem failure requiring a chkdsk on the underlying volume.

Troubleshooting Guide: SQL Server Error 881 — Resilient Buffer Pool Extension Already Disabled

Why This Error Happens

Error 881 is an informational message indicating that SQL Server 2025 attempted to disable the Resilient Buffer Pool Extension (RBPEX) feature, but it was already in a disabled state at the time of the request. This typically occurs after an unexpected shutdown or restart when SQL Server performs a consistency check on persisted memory configurations and finds no active buffer pool extension file to disable. In most cases, this is not indicative of corruption or failure—it is a benign status message confirming the current configuration state, often triggered by configuration scripts or startup routines that redundantly issue a disable command.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance reporting the error.
• Run the following query to check the current state of the buffer pool extension:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• If the state column returns 0 or the table returns no rows, this confirms RBPEX is already disabled, validating that Error 881 is purely informational.

Step 2: Review SQL Server Error Logs for Context

• Navigate to the default log directory: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\
• Open the most recent ERRORLOG file using Notepad or a text editor.
• Search for the string “Error: 881” to identify the surrounding context, such as which startup script or maintenance job triggered the redundant disable command.
• Check if a scheduled SQL Agent Job or a Dockerfile/PowerShell deployment script is explicitly calling ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF on every restart cycle.

Step 3: Clean Up Redundant Configuration Scripts

• If a startup script is the cause, locate and edit the relevant .sql or .ps1 file (commonly found in C:\SQLScripts\Startup\ or your CI/CD deployment pipeline).
• Remove or comment out the redundant ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF command if the extension is not currently being used.
• Save the script and redeploy or restart the SQL Server service to confirm the error no longer populates in the log.

Step 4: Re-Enable Buffer Pool Extension (If Required for Workload)

• If your workload actually requires RBPEX (common on servers with NVMe/SSD caching tiers), re-enable it explicitly rather than leaving it in a disabled state.
• Run the following T-SQL command, replacing the path with a valid high-speed storage location:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'E:\SSDCache\BPE.BPE', SIZE = 8 GB);

• Restart the SQL Server service via services.msc or the command line using NET STOP MSSQLSERVER followed by NET START MSSQLSERVER.

Alternative or Verification Steps

• Verification: After implementing changes, restart the SQL Server instance and immediately check the ERRORLOG file again to ensure Error 881 does not reappear, or confirm it only appears once during the initial state-check phase without repeating in a loop.
• Alternative: If the error persists despite disabling redundant scripts, use Resource Governor settings to audit memory allocation, ensuring no conflicting Group Policy Object (GPO) or Windows Server 2025 Storage Spaces configuration is interfering with the buffer pool extension file path permissions.

Troubleshooting Guide: SQL Server Error 882 – Corrupt Schema on InternalBaseTable

Why This Error Happens

Error 882 typically occurs when the internal metadata describing a system-generated table (created via InternalBaseTable) becomes desynchronized from the actual on-disk schema, often due to an interrupted DDL operation, abrupt service termination, or a failed in-place upgrade. This can also stem from corrupted system catalog pages, filesystem-level corruption on the data file, or incompatible schema versioning after migrating a database between SQL Server builds. In rare cases, insufficient permissions during automated internal table creation (common with temporal tables, memory-optimized tables, or columnstore internals) can leave a partially-built schema that SQL Server cannot validate on subsequent access.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether the corruption is isolated to the internal table or affects the entire database.
• Check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for accompanying messages referencing object IDs tied to the InternalBaseTable.
• Cross-reference the Windows Event Viewer (Application Log, Source: MSSQLSERVER, Event ID 882) for timestamps correlating with recent schema changes or failovers.

Step 2: Isolate and Rebuild the Internal Table

• Identify the parent object using SELECT * FROM sys.internal_tables WHERE name LIKE '%<suspected_object>%'; to locate the corrupt internal table’s parent object ID.
• If the internal table supports a memory-optimized or temporal table, drop and recreate the parent object’s feature (e.g., disable and re-enable system versioning for temporal tables) rather than attempting to directly modify the internal table, since internal tables are not user-editable.
• Execute ALTER TABLE [ParentTable] SET (SYSTEM_VERSIONING = OFF); followed by re-enabling it after validating the underlying schema definition in sys.columns.

Step 3: Restore from a Known-Good Backup (If Rebuild Fails)

• Stop the SQL Server service via services.msc or net stop MSSQLSERVER to prevent further writes to the affected database.
• Restore the most recent full backup plus transaction log backups using RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH NORECOVERY; followed by RESTORE LOG statements as needed.
• Bring the database online with RESTORE DATABASE YourDatabaseName WITH RECOVERY; and re-run DBCC CHECKDB to confirm the internal schema is now consistent.

Step 4: Apply the Latest Cumulative Update

• Confirm your current build number via SELECT @@VERSION; and compare it against the latest SQL Server 2025 Cumulative Update listed on the Microsoft Update Catalog.
• Install the update using the standalone installer or through Windows Update if integrated, then restart the SQL Server service and re-validate the schema with DBCC CHECKDB.

Alternative or Verification Steps

• After remediation, run DBCC CHECKTABLE('ParentTableName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm the internal table schema reports zero allocation or consistency errors.
• If corruption recurs immediately after rebuild, treat it as a storage-layer issue — run chkdsk D: /f /r on the volume hosting the .mdf/.ndf files and consider relocating the database to a different physical disk.

Troubleshooting Guide: SQL Server Error 878 — Resilient Buffer Pool Extension Creation Failure

Why This Error Happens

Error 878 occurs when SQL Server 2025 cannot allocate or create the resilient buffer pool extension (BPE) file at the specified path, typically because the SQL Server service account lacks NTFS write permissions on the target directory or drive. This can also happen if the target volume has insufficient free disk space to accommodate the requested buffer pool extension size, or if the path references a non-existent directory, a removable/network drive that doesn’t support the required persistent memory semantics, or a volume formatted with an incompatible file system (non-NTFS/ReFS). Additionally, file corruption or a pre-existing locked file at that path from a previous failed attempt can block creation of the new resilient buffer extension file.

Step-by-Step Resolution Guide

Step 1: Verify NTFS Permissions on the Target Path

• Open File Explorer, navigate to the drive/folder specified in the error message (e.g., D:\SQLBPE\).
• Right-click the folder → Properties → Security tab.
• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain service account) has Full Control permissions.
• If missing, click Edit → Add, enter the service account name, and grant Full Control, then click Apply.

Step 2: Confirm Disk Space and Path Validity

• Open PowerShell as Administrator and run:

  Get-PSDrive -PSProvider FileSystem

• Ensure the target volume has enough free space to match the configured BPE size (check via sys.dm_os_buffer_pool_extension_configuration).
• Confirm the path exists and is on a local, fixed NTFS or ReFS volume — not a network share (UNC paths are unsupported for BPE).
• If the directory doesn’t exist, create it manually:

  New-Item -ItemType Directory -Path "D:\SQLBPE"

Step 3: Remove Stale or Locked BPE Files

• Check the target path for leftover .bpe files from a previous failed configuration attempt.
• Stop the SQL Server service via SQL Server Configuration Manager.
• Manually delete any orphaned .bpe file in the directory.
• Restart the SQL Server service and re-run the ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION command.

Step 4: Reconfigure the Buffer Pool Extension

• Execute the following T-SQL to disable and re-enable the BPE with correct parameters:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
  GO
  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
  ( FILENAME = 'D:\SQLBPE\BPE.bpe', SIZE = 4096 MB );
  GO

• Ensure the SIZE value does not exceed available free disk space and follows the supported ratio relative to physical RAM.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; in SSMS to confirm the BPE state shows as state_description = RUNNING.
• If the error persists, check the SQL Server Error Log and Windows Event Viewer (Application log) for underlying I/O or antivirus-blocking errors, and consider targeting a different local SSD-backed volume as an alternative path.

Troubleshooting Guide: Error 879 – Resilient Buffer Pool Extension Failure

Why This Error Happens

This error occurs because SQL Server’s resilient buffer pool extension (a feature designed to persist buffer pool contents across restarts using persistent memory) requires the target database to be memory-optimized. When the specified database (identified by its database ID %d) does not have the memory-optimized filegroup or configuration enabled, SQL Server cannot map the resilient buffer pool extension to it. This typically happens after a migration, a restore from an older version, or when the feature is enabled on a database that was never configured for In-Memory OLTP.

Step-by-Step Resolution Guide

Step 1: Verify Database Memory-Optimized Status

• Open SQL Server Management Studio (SSMS) and connect to the instance running SQL Server 2025.
• Run the following query to check if the database has a memory-optimized filegroup:

SELECT name, type_desc 
FROM sys.filegroups 
WHERE type = 'FX';

• If no rows are returned, the database is not memory-optimized and is the root cause of Error 879.

Step 2: Add a Memory-Optimized Filegroup

• If the database lacks a memory-optimized filegroup, add one using:

ALTER DATABASE [YourDatabaseName] 
ADD FILEGROUP [MOD_FG] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [YourDatabaseName] 
ADD FILE (NAME='MOD_Data', FILENAME='C:\SQLData\MOD_Data') 
TO FILEGROUP [MOD_FG];

• Ensure the file path (e.g., C:\SQLData\) exists and the SQL Server service account has write permissions.

Step 3: Reconfigure the Resilient Buffer Pool Extension

• Disable the existing (failed) buffer pool extension configuration:

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION OFF;

• Re-enable it, explicitly pointing to the now memory-optimized database:

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'E:\SSDCACHE\BPE.bpe', SIZE = 50 GB);

• Restart the SQL Server service via Services.msc or net stop MSSQLSERVER && net start MSSQLSERVER on Windows Server 2025.

Step 4: Check Windows Server Persistent Memory Configuration

• Confirm that persistent memory modules (if using Intel Optane or NVDIMM) are correctly recognized in Device Manager under “Persistent Memory Disks.”
• Validate drive letter assignments match the FILENAME path specified in Step 3.

Alternative or Verification Steps

• Verify the fix: Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for a confirmation message stating “Buffer Pool Extension is already enabled” or successful startup with no Error 879 entries.
• Alternative solution: If converting the database to memory-optimized is not feasible, disable the resilient buffer pool extension feature entirely for that specific database instance using ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; and rely on standard buffer pool management instead.

Troubleshooting Guide: Error 880 – Resilient Buffer Pool Extension Version Mismatch

Why This Error Happens

This error occurs when SQL Server 2025’s resilient Buffer Pool Extension (BPE) feature detects that the persisted state file on disk was written by a different binary version than the one currently starting up. This commonly happens after an in-place upgrade, a cumulative update installation, or a rollback where the SQL Server binaries changed but the old BPE cache file was left on disk. Because the persisted buffer pool extension format is version-locked for data integrity reasons, SQL Server refuses to load a mismatched file rather than risk silent data corruption.

Step-by-Step Resolution Guide

Step 1: Identify the Persisted BPE File Location

• Open SQL Server Configuration Manager or check the SQL Server error log for the BPE file path (typically a .bpe file in a path like D:\SQLData\BufferPoolExtension\).
• Confirm the SQL Server version by running SELECT @@VERSION; in a working instance or checking setup.exe /Version from the installation media.
• Cross-reference the build number in the error log against the currently installed binary version in Help > About or via sqlservr.exe -v.

Step 2: Remove or Relocate the Incompatible BPE File

• Stop the SQL Server service using net stop MSSQLSERVER (or the appropriate named instance service).
• Navigate to the BPE file path and rename or move the .bpe file to a backup location (e.g., C:\Temp\BPE_Backup\).
• Ensure no other process has a lock on the file using Resource Monitor or handle.exe from Sysinternals.

Step 3: Reconfigure the Buffer Pool Extension

• Restart the SQL Server service: net start MSSQLSERVER.
• Re-enable BPE using T-SQL:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\SQLData\BufferPoolExtension\BPE.bpe', SIZE = 8 GB);

• Confirm the SQL Server service account has Modify permissions on the target BPE directory in Windows 11/Server 2025 File Explorer security settings.

Step 4: Validate Binary Consistency Across the Cluster (If Applicable)

• For Always On Availability Groups or Failover Cluster Instances, verify all nodes are running the identical SQL Server 2025 build using SELECT SERVERPROPERTY('ProductVersion'); on each node.
• Apply any pending Cumulative Updates uniformly across all nodes before re-enabling BPE.

Alternative or Verification Steps

• Check the SQL Server Error Log (ERRORLOG file, usually in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LOG\) for a confirmation message indicating “Buffer Pool Extension is enabled” with no version warnings on the next startup.
• If the error persists, disable BPE entirely with ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;, then re-run Step 3 to rebuild the extension file from scratch rather than reusing any legacy file.

Why This Error Happens

Error 876 occurs when SQL Server 2025 cannot initialize the resilient buffer pool extension (BPE) file at the specified path. This is typically caused by insufficient NTFS permissions for the SQL Server service account on the target directory, the underlying storage volume lacking the required capacity for the configured cache size, or the BPE file/path being locked by another process or corrupted from an unclean shutdown. In some cases, the path references a drive that uses a file system (such as ReFS without proper configuration) or storage tier that does not support the persistent memory semantics required by the resilient buffer pool extension.

Step-by-Step Resolution Guide

Step 1: Verify Service Account Permissions on the Target Path

• Open File Explorer and navigate to the directory specified in the error (e.g., D:\SQLCache\BPE\).
• Right-click the folder, select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If missing, click Edit > Add, type the service account name, and grant Full Control.
• Restart the SQL Server service via services.msc or net start MSSQLSERVER after applying permissions.

Step 2: Check Disk Space and Storage Configuration

• Confirm the target volume has free space greater than the configured buffer pool extension size (%I64d MB reported in the error).
• Run fsutil fsinfo drives in an elevated Command Prompt to confirm the drive letter is still mapped correctly.
• If using ReFS, verify integrity streams are disabled for the BPE file’s parent directory using Set-FileIntegrity -FileName "D:\SQLCache" -Enable $false in PowerShell, since integrity streams can block the low-level I/O pattern BPE requires.

Step 3: Remove Corrupted or Locked BPE Files

• Stop the SQL Server service completely: net stop MSSQLSERVER.
• Navigate to the configured BPE path and delete any stale .BPE or .cache files left from a previous crash.
• Use Process Explorer (Sysinternals) to confirm no orphaned sqlservr.exe handle is still locking the file before deletion.

Step 4: Reconfigure the Buffer Pool Extension via T-SQL

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Disable and re-enable the extension with a validated path and size:

ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\SQLCache\BPE\Example.BPE', SIZE = 4096 MB);
GO

• Ensure the SIZE value does not exceed 16x the instance’s configured max server memory, per SQL Server 2025 guidelines.

Step 5: Validate Windows Server 2025 Storage Driver Compatibility

• Update storage controller and disk drivers via Windows Update or vendor-specific tools, as outdated drivers on Windows Server 2025 have been known to interfere with memory-mapped file extensions like BPE.
• Reboot the server after driver updates and attempt to restart the SQL Server service.

Alternative or Verification Steps

• After applying fixes, check the SQL Server Error Log (via SSMS or ERRORLOG file in the MSSQL\Log directory) for the message “Buffer Pool Extension is enabled” to confirm successful startup.
• If the error persists, move the BPE file to a different physical disk (preferably a local SSD rather than network-attached storage) since resilient BPE does not support remote or UNC paths.

Troubleshooting Guide: Error 877 – Resilient Buffer Pool Extension Status Message

Why This Error Happens

Error 877 is informational in nature, not a failure code — it is logged when SQL Server 2025’s Resilient Buffer Pool Extension (BPE) feature successfully initializes and binds to a specified cache file on non-volatile storage. This message typically appears during service startup or after a manual ALTER SERVERCONFIGURATION command, confirming that the hybrid buffer pool has extended into the designated SSD/NVMe cache path. While it is a success notification, administrators often investigate it when the reported size (%I64d MB) doesn’t match the expected configuration, or when it appears unexpectedly after a crash recovery, indicating the extension may have re-initialized with default values rather than the custom settings previously applied.

Step-by-Step Resolution Guide

Step 1: Verify the Current Buffer Pool Extension Configuration

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Run the following query to check the active path and size: SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;
• Compare the path and current_size_in_kb columns against your intended architecture (e.g., E:\SQLCache\BPE.BIN).

Step 2: Validate File Permissions and Disk Health on Windows Server 2025

• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control on the directory hosting the .BIN cache file.
• Navigate to the folder in File Explorer, right-click → Properties → Security tab, and confirm permissions.
• Check Windows Event Viewer under Windows Logs > System for underlying disk latency or NVMe controller errors that might cause the extension to reset.

Step 3: Reconfigure the Buffer Pool Extension Size (If Incorrect)

• If the reported size in the Error 877 log does not match your requirements, disable and re-enable the feature with explicit parameters.
• Run the following T-SQL command to disable it first:

 ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF;

• Re-enable it with the correct size definition:

 ALTER SERVERCONFIGURATION 
 SET BUFFER POOL EXTENSION ON 
 (FILENAME = 'E:\SQLCache\BPE.BIN', SIZE = 32 GB);

Step 4: Restart the SQL Server Service

• Open SQL Server Configuration Manager.
• Right-click the SQL Server (MSSQLSERVER) service instance and select Restart.
• Monitor the SQL Server Error Log immediately after restart to confirm Error 877 logs the correct updated size.

Alternative or Verification Steps

• Verify Success: Check the SQL Server Error Log (via EXEC sp_readerrorlog 0, 1, 'buffer pool extension';) to ensure the logged size matches the capacity defined in Step 3.
• Alternative Fix: If the extension continuously resets to a default size after reboots, check the Windows Server 2025 Storage Spaces Direct (S2D) configuration, as caching tier conflicts can override manual SQL Server BPE settings.

Troubleshooting Guide: Error 874 — Resilient Buffer Pool Extension Initialization Failure

Why This Error Happens

Error 874 occurs when SQL Server 2025 cannot initialize the Buffer Pool Extension (BPX) file at the specified path, typically because the service account lacks sufficient NTFS permissions on the target directory or drive. This can also happen if the underlying storage volume has insufficient free space to accommodate the configured size, or if the path points to a non-existent directory, a network location, or a drive with an incompatible file system (BPX requires direct-attached, non-removable storage with NTFS or ReFS). Additionally, a stale or corrupted .BPE file left over from a previous crash can prevent clean reinitialization.

Step-by-Step Resolution Guide

Step 1: Verify Path Existence and Drive Type

• Confirm the directory specified in the error (e.g., D:\SQLCache\) actually exists; SQL Server will not auto-create nested folders.
• Ensure the target is a local SSD/NVMe drive — BPX does not support network shares (UNC paths) or removable media.
• Run fsutil fsinfo drivetype D: in an elevated Command Prompt to confirm it returns “Fixed Drive.”

Step 2: Grant Correct NTFS Permissions

• Open File Explorer → right-click the target folder → Properties → Security tab.
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or your custom domain service account) with Full Control.
• Alternatively, run this PowerShell command as Administrator:

  icacls "D:\SQLCache" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F"

• Restart the SQL Server service afterward for permissions to take effect.

Step 3: Check Available Disk Space

• Compare the size requested in the error message (e.g., “size 8192 MB”) against actual free space using Get-PSDrive D in PowerShell.
• Free up space or reconfigure BPX with a smaller size using:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION OFF;
  GO
  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'D:\SQLCache\BPE.BIN', SIZE = 4096 MB);
  GO

Step 4: Remove Stale or Corrupted BPE Files

• Navigate to the configured path and manually delete any leftover .BIN or .BPE files from a previous failed session.
• Ensure SQL Server service is stopped before deleting the file to avoid file-lock conflicts.
• Restart the SQL Server service and allow it to recreate the file cleanly.

Step 5: Review SQL Server Error Log for Underlying Cause

• Open the SQL Server Error Log (via SSMS → Management → SQL Server Logs, or ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\).
• Look for accompanying OS-level error codes (e.g., Access Denied, Disk Full) printed near Error 874 for more precise root-cause confirmation.

Alternative or Verification Steps

• After applying fixes, verify BPX is active by running SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; in SSMS — the state_description column should show “ON”.
• If the error persists, disable BPX entirely via ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; and redeploy it on a different physical drive to rule out hardware-level corruption or driver issues.

Why This Error Happens

Error 875 is not actually a failure—it is an informational message logged by the SQL Server Database Engine confirming that the Resilient Buffer Pool Extension (a persisted memory-mapped cache feature introduced to protect buffer pool data across restarts) has initialized correctly on the specified file. It typically appears in the SQL Server Error Log whenever the instance starts, restarts, or recovers from an unexpected shutdown, and the extension file is successfully remapped to its configured size. Users often mistake this for a critical error because it carries an “Error” severity classification in the log viewer, even though it indicates a successful operation, not a malfunction.

Step-by-Step Resolution Guide

Step 1: Confirm the Message Severity in SQL Server Logs

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Locate the Error 875 entry and confirm the message text reads “…has been started successfully…”.
• Cross-reference the timestamp with a recent service restart, failover, or Windows Server 2025 patch cycle to confirm it is a startup artifact, not an ongoing fault.

Step 2: Validate Buffer Pool Extension Configuration

• Run the following T-SQL query to inspect current buffer pool extension settings:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Check the path, file_size_in_kb, and state_description columns to ensure the extension file (commonly a .bpe file) resides on a fast SSD/NVMe volume as required by SQL Server 2025 resiliency features.
• Verify the configured size aligns with your intended RAM-to-extension ratio (commonly 4x–10x physical RAM for OLTP workloads).

Step 3: Check File System Permissions and Disk Health on Windows Server 2025

• Ensure the SQL Server service account has Full Control NTFS permissions on the folder containing the .bpe file (e.g., D:\SQLExtension\).
• Open Event Viewer > Windows Logs > System and look for disk-related warnings (Event ID 153 or 157) that could indicate underlying storage latency affecting the extension remap process.
• Confirm Storage Spaces Direct or local disk health using:

Get-PhysicalDisk | Select FriendlyName, HealthStatus, OperationalStatus

Step 4: Re-enable or Resize the Buffer Pool Extension (If Needed)

• If the extension file was deleted or corrupted during an unclean shutdown, disable and re-enable it:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON
(FILENAME = 'D:\SQLExtension\BPE_Cache.bpe', SIZE = 50 GB);

• Restart the SQL Server service via SQL Server Configuration Manager to confirm clean reinitialization.

Alternative or Verification Steps

• Re-check the SQL Server Error Log after the next restart — a single, clean “started successfully” message with no accompanying warning (Error 876 or 878) confirms normal operation.
• If the message repeats excessively on every short interval (not tied to restarts), run DBCC CHECKDB and inspect the storage subsystem for intermittent I/O failures, as recurring re-initialization may indicate disk instability rather than a logging quirk.

Why This Error Happens

Error 872 is actually an informational/status message rather than a critical failure—it occurs when an administrator or an automated script attempts to enable Buffer Pool Extension (BPX) on a SQL Server 2025 instance where the feature is already active. This typically happens due to duplicate execution of ALTER SERVER CONFIGURATION scripts during automated deployment pipelines, or when a DBA re-runs a configuration script without first checking the current BPX state. It can also surface if a previous configuration change didn’t commit properly, leaving the SQL Server metadata and the actual runtime state temporarily out of sync.

Step-by-Step Resolution Guide

Step 1: Verify the Current Buffer Pool Extension Status

• Open SQL Server Management Studio (SSMS) and connect to the instance running on Windows Server 2025.
• Run the following query to check the current state:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm the state_description column shows RUNNING, which validates that no further action is required.

Step 2: Review and Clean Up Deployment/Automation Scripts

• Check any PowerShell DSC, Ansible, or T-SQL deployment scripts stored in paths like C:\Scripts\SQLConfig\ for redundant ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION commands.
• Add a conditional check before the enable command, for example:

  IF NOT EXISTS (SELECT 1 FROM sys.dm_os_buffer_pool_extension_configuration WHERE state = 1)
  BEGIN
      ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON 
      (FILENAME = 'E:\SSDCache\BPE.bpe', SIZE = 50 GB);
  END

• Save and re-deploy the corrected script to prevent future duplicate triggers.

Step 3: Restart the SQL Server Service (Optional Cleanup)

• If you suspect a metadata-to-runtime mismatch, restart the SQL Server service via Services.msc or PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• This clears any stale configuration cache and forces SQL Server to reload the actual BPX state from the configuration file path (e.g., E:\SSDCache\BPE.bpe).

Step 4: Suppress the Informational Message in Logging (If Needed)

• If Error 872 is cluttering SQL Server Agent job logs or Error Logs unnecessarily, update your job step logic to catch and ignore this specific message using TRY/CATCH:

  BEGIN TRY
      ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON 
      (FILENAME = 'E:\SSDCache\BPE.bpe', SIZE = 50 GB);
  END TRY
  BEGIN CATCH
      PRINT 'BPE already enabled - ignoring Error 872';
  END CATCH

Alternative or Verification Steps

• Verify the fix by querying sys.dm_os_buffer_pool_extension_configuration again post-restart to confirm state_description = RUNNING and that the path matches your intended SSD cache file location.
• If the issue persists across restarts, drop and re-create the Buffer Pool Extension entirely using ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF; followed by a fresh ON command with updated file paths.

Troubleshooting Guide: SQL Server Error 873 — Buffer Pool Extension Already Disabled

Why This Error Happens

This message appears when a DISABLE command is issued against the Buffer Pool Extension (BPE) feature while it is already turned off at the instance level, typically because a previous administrative script or maintenance job ran without first checking the current BPE state. It is technically an informational message rather than a critical failure, but it often surfaces when configuration scripts are executed redundantly or when BPE was never enabled on that particular instance to begin with. In some cases, this also occurs after a failed or incomplete BPE configuration change left the sys.dm_os_buffer_pool_extension_configuration state inconsistent with what the administrator expected.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension State

• Open SQL Server Management Studio (SSMS) and connect to the target SQL Server 2025 instance.
• Run the following query to check the current BPE status:

SELECT state, state_description, path, current_size_in_kb 
FROM sys.dm_os_buffer_pool_extension_configuration;

• If state_description returns DISABLED or the result set is empty, this confirms the feature is already off and no further action is required.

Step 2: Review and Correct the Triggering Script or Job

• Open SQL Server Agent (via SSMS Object Explorer under SQL Server Agent > Jobs) and locate any job steps containing ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF.
• Edit the T-SQL step to include a conditional check before disabling, so the command only runs if BPE is currently enabled:

IF EXISTS (SELECT 1 FROM sys.dm_os_buffer_pool_extension_configuration WHERE state = 1)
BEGIN
    ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
END

• Save the modified job step to prevent the message from recurring on future runs.

Step 3: Re-enable Buffer Pool Extension (If It Should Be Active)

• If BPE was intended to be active (e.g., on a server with SSDs supplementing RAM), re-enable it with a valid path on the Windows Server 2025 / Windows 11 host:

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'E:\SQLCache\BPE.bpe', SIZE = 50 GB);

• Ensure the SQL Server service account has Modify NTFS permissions on the target drive (e.g., E:\SQLCache) via Windows Explorer > Properties > Security tab.
• Confirm the drive is a solid-state drive (SSD) for optimal performance, as BPE is designed for SSD-backed caching, not spinning disks.

Step 4: Check SQL Server Error Log for Related Entries

• Navigate to SSMS > Management > SQL Server Logs or check the physical log file at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for Error 873 entries and note the timestamp to correlate with the job or script execution that triggered it.
• Confirm there are no accompanying critical errors (e.g., disk I/O failures) that might indicate a deeper storage issue rather than a simple redundant command.

Alternative or Verification Steps

• To verify the fix, re-run the sys.dm_os_buffer_pool_extension_configuration query after applying the conditional script; the job should complete without generating Message 873 in the SQL Server Agent job history.
• If the error persists unexpectedly even when BPE appears disabled, restart the SQL Server service (services.msc > SQL Server (MSSQLSERVER) > Restart) to clear any cached configuration state, then re-check the DMV output.

Troubleshooting Guide: SQL Server Error 870 — “BPE Feature Switch Is On”

Why This Error Happens

Error 870 occurs when SQL Server 2025 detects that the Buffer Pool Extension (BPE) feature switch has been enabled, either through a trace flag, registry configuration, or an internal engine default, but the underlying hardware or storage configuration does not meet the requirements to support it safely. This typically happens when a non-SSD or network-attached storage device is specified as the BPE cache file location, or when the feature flag is toggled on without a corresponding valid BUFFERPOOLEXTENSION configuration. In some cases, this is also triggered by a corrupted or stale configuration file left over from a previous instance upgrade or migration to Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Buffer Pool Extension Configuration Status

• Open SQL Server Management Studio (SSMS) and run: SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;
• Check the state_description column — if it reads “FEATURE SWITCH ON” without a valid path, this confirms the misconfiguration.
• Note the current path and current_size_in_kb values for reference before making changes.

Step 2: Disable and Reset the BPE Feature Switch

• Run the following T-SQL command to disable BPE cleanly: ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF;
• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ and archive the current ERRORLOG file.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt.

Step 3: Reconfigure BPE on Supported Storage

• Confirm your target drive is a solid-state (SSD) volume using Get-PhysicalDisk | Select DeviceID, MediaType in PowerShell.
• Re-enable BPE pointing to the correct SSD path: ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\SQLCache\BPE.bpe', SIZE = 20GB);
• Ensure the SQL Server service account has Modify permissions on the target folder via Windows File Explorer security settings.

Step 4: Check Windows Server 2025 Group Policy and Registry Settings

• Open regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\Parameters.
• Confirm no conflicting trace flag (such as -T868 or -T869) is forcing the BPE switch on at startup.
• Remove any erroneous startup parameters via SQL Server Configuration Manager under the “Startup Parameters” tab.

Alternative or Verification Steps

• Re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and confirm state_description now shows “NORMAL OPERATION” with the correct file path and size.
• If Error 870 persists after reconfiguration, perform a full repair of the SQL Server 2025 instance using the Installation Center’s “Repair” option to rebuild corrupted configuration metadata.

Troubleshooting Guide: SQL Server Error 871 — Buffer Pool Extension Size Reporting Issue

Why This Error Happens

Error 871 is an informational message that occurs when SQL Server 2025 initializes the Buffer Pool Extension (BPE) using a size value inherited from Resource Governor (RG) settings, typically read from a configuration file or registry key. This message appears when the configured BPE size in gigabytes does not align correctly with the byte-level allocation SQL Server calculates internally, often due to a misconfigured BUFFER POOL EXTENSION clause or a stale Resource Governor configuration pool. In most cases, this is triggered by manually edited configuration files, outdated SSD cache paths, or a Resource Governor pool that was modified without a proper ALTER RESOURCE GOVERNOR RECONFIGURE command being issued afterward.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting Error 871.
• Run the following query to inspect current BPE settings:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm the current_size_in_kb value matches what Resource Governor expects (cross-check against sys.resource_governor_resource_pools).

Step 2: Disable and Reconfigure the Buffer Pool Extension

• Disable the existing BPE to clear any corrupted size metadata:

  ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF;

• Re-enable it with an explicit, validated size and path (ensure the path exists on fast SSD storage, e.g., S:\BPE\):

  ALTER SERVERCONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'S:\BPE\SQLServerBPE.bpe', SIZE = 16 GB);

• Ensure the SIZE value is a whole-number multiple that aligns with 1 GB increments to avoid byte-conversion rounding errors.

Step 3: Reset Resource Governor and Force Reconfiguration

• Navigate to Object Explorer → Management → Resource Governor in SSMS.
• Run the following to force RG to re-read updated pool definitions:

  ALTER RESOURCE GOVERNOR RECONFIGURE;

• If the error persists, reset Resource Governor to defaults before reapplying custom pools:

  ALTER RESOURCE GOVERNOR DISABLE;
  ALTER RESOURCE GOVERNOR RECONFIGURE;

Step 4: Validate File System and Storage Permissions

• On Windows Server 2025 / Windows 11, confirm the SQL Server service account has Modify permissions on the BPE file path (e.g., S:\BPE\).
• Check via File Explorer → Properties → Security tab, or run:

  icacls "S:\BPE"

• Ensure no disk quota or storage policy is restricting the required allocation size.

Step 5: Restart the SQL Server Service

• Open SQL Server Configuration Manager.
• Restart the SQL Server (MSSQLSERVER) service (or named instance) to apply the new BPE and Resource Governor settings cleanly.
• Monitor the SQL Server Error Log immediately after restart for recurrence of Error 871.

Alternative or Verification Steps

• Check the SQL Server Error Log (Management → SQL Server Logs in SSMS) to confirm the new BPE size in GB and bytes matches your configured value with no further warnings.
• If the error persists after reconfiguration, drop and recreate the custom Resource Governor pool entirely using DROP RESOURCE POOL followed by a fresh CREATE RESOURCE POOL definition, then reapply the BPE settings from Step 2.

Troubleshooting Guide: SQL Server Error 868 – Buffer Pool Extension Size Threshold

Why This Error Happens

This error occurs when the size specified for the Buffer Pool Extension (BPE) file is smaller than the current memory allocation threshold required by SQL Server 2025. SQL Server enforces a minimum ratio between the BPE file size and the physical RAM allocated to the buffer pool, and if the SSD/flash cache file is undersized relative to this threshold, the engine refuses to enable the extension. This typically happens after a RAM upgrade on the server without a corresponding resize of the existing BPE file, or when the BPE file was initially configured with an arbitrarily small value.

Step-by-Step Resolution Guide

Step 1: Check Current Memory Configuration

• Open SQL Server Management Studio (SSMS) and run EXEC sp_configure 'max server memory' to view the current allocated memory threshold in MB.
• Cross-reference this value with the physical RAM available on your Windows Server 2025 / Windows 11 host using Task Manager or systeminfo in PowerShell.
• Note the MB value reported in the Error 868 message text, as this is the exact minimum size SQL Server expects for the BPE file.

Step 2: Disable the Existing Buffer Pool Extension

• In SSMS, run the following T-SQL command to safely turn off the current BPE configuration before resizing:
• ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
• Confirm there are no active transactions relying on the extended cache before proceeding to avoid I/O stalls.

Step 3: Recreate the BPE File with a Larger Size

• Locate your SSD or NVMe drive path (e.g., D:\SSDCache\) where the .BPE file will reside.
• Run the command below, replacing the SIZE value with a number larger than the MB threshold reported in the error message (SQL Server generally requires the BPE file to be 4x to 32x the RAM allocated to max server memory):
• ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\SSDCache\SQLServerBPE.BPE', SIZE = 256GB);
• Ensure the SQL Server service account has Full Control NTFS permissions on the target directory via icacls D:\SSDCache /grant "MSSQLSERVER":(OI)(CI)F.

Step 4: Restart the SQL Server Service

• Open services.msc on Windows Server 2025 and restart the SQL Server (MSSQLSERVER) service to apply the new buffer pool extension settings.
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.

Alternative or Verification Steps

• Verify the fix by querying sys.dm_os_buffer_pool_extension_configuration in SSMS; the state_description column should read NORMAL OPERATION rather than an error state.
• If the error persists, check the SQL Server Error Log (ERRORLOG file in C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\) for underlying disk space or permission conflicts preventing the file from growing to the required size.

Troubleshooting Guide: SQL Server Error 869 – Buffer Pool Extension Cannot Be Closed

Why This Error Happens

This error occurs when SQL Server attempts to disable or resize the Buffer Pool Extension (BPE) file while there are still pending asynchronous I/O operations referencing pages in that extension cache. It typically happens during a high-throughput workload where the engine cannot flush or cancel outstanding reads/writes fast enough before the configuration change is applied. Underlying causes often include abrupt configuration changes via ALTER SERVER CONFIGURATION, slow or overloaded storage subsystems (especially on NVMe/SSD BPE files sitting on network-attached or throttled storage), or a sudden service shutdown request while BPE I/O queues are still active.

Step-by-Step Resolution Guide

Step 1: Quiesce the Workload Before Modifying BPE

• Pause or throttle active sessions and batch jobs that heavily use buffer pool reads before disabling BPE.
• Run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; to confirm BPE state and size.
• Avoid running ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF during peak I/O windows.

Step 2: Check Storage Health and I/O Latency

• Open Resource Monitor or Performance Monitor (perfmon.exe) on Windows Server 2025 / Windows 11 and inspect the Avg. Disk sec/Transfer counter for the drive hosting the BPE file (commonly .bpe extension in D:\SQLData\ or similar).
• Verify there are no pending hardware resets, RAID rebuilds, or network share disconnects if BPE resides on remote storage (not recommended by Microsoft).
• If latency spikes are found, move the BPE file to local NVMe/SSD storage using:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\SQLBPE\bpe_cache.bpe', SIZE = 50 GB);

Step 3: Gracefully Restart the SQL Server Service

• Use SQL Server Configuration Manager (not Task Manager) to restart the SQL Server (MSSQLSERVER) service, allowing it to complete shutdown checkpoints properly.
• Confirm via Windows Event Viewer (Applications and Services Logs > MSSQLSERVER) that no additional I/O-related errors (Event ID 833 or 845) accompany Error 869.
• If the instance hangs on shutdown, wait for the default shutdown timeout (defined by -t startup parameter) rather than forcing termination, which can corrupt the BPE cache file.

Step 4: Rebuild or Resize the BPE Cache File

• If the BPE file was left in an inconsistent state, delete the stale .bpe file only after confirming SQL Server is fully stopped.
• Recreate it with a conservative size (start at 1x RAM instead of 4x) to reduce I/O backlog during flush operations:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'E:\SQLBPE\bpe_cache_new.bpe', SIZE = 20 GB);

Alternative or Verification Steps

• Verify the fix by querying sys.dm_os_buffer_pool_extension_configuration and confirming state_description shows NORMAL with no pending I/O after a configuration change.
• If the error persists under heavy load, consider disabling BPE entirely during maintenance windows and re-enabling it only after confirming disk queue length (via perfmon) stays consistently below 2.

Troubleshooting Guide: SQL Server Error 866 – Buffer Pool Extension Not Supported on Architecture

Why This Error Happens

Error 866 occurs when SQL Server 2025 attempts to initialize the Buffer Pool Extension (BPE) feature on a system where the underlying processor architecture or SQL Server edition does not support it, typically affecting ARM64 builds or certain lightweight container deployments. This is frequently seen when SQL Server binaries are installed on an unsupported CPU architecture (such as ARM-based Windows 11 devices) where the BPE memory-mapping APIs have not been compiled into the engine. It can also surface if a configuration file (such as a .ini or startup parameter) was copied from an x64 instance and applied to an incompatible architecture build.

Step-by-Step Resolution Guide

Step 1: Verify Your System Architecture

• Open PowerShell as Administrator and run (Get-WmiObject Win32_Processor).Architecture to confirm if the output indicates ARM64 (value 12) rather than x64 (value 9).
• Cross-check your SQL Server 2025 installation media/ISO to confirm whether you downloaded the x64 build or an ARM64 preview build.

Step 2: Disable Buffer Pool Extension Configuration

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following T-SQL command to disable BPE if it was mistakenly enabled in a config script:

ALTER SERVERCONFIGURATION
SET BUFFER POOL EXTENSION OFF;

• Remove any -k startup parameter referencing a BPE file path from SQL Server Configuration Manager under SQL Server Services > Properties > Startup Parameters.

Step 3: Reinstall the Correct SQL Server Build

• Uninstall the current instance via Control Panel > Programs and Features.
• Download the correct x64 distribution of SQL Server 2025 from the Microsoft Evaluation Center, specifically matching your Windows Server 2025 or Windows 11 architecture.
• Re-run setup using setup.exe /ACTION=Install /FEATURES=SQLENGINE and skip the BPE configuration screen entirely during the installation wizard.

Step 4: Validate Server Configuration Post-Install

• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to ensure no residual BPE initialization attempts are logged on startup.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; in SSMS; if the feature is truly unsupported, this should return an empty result set without throwing Error 866 on service restart.
• If you specifically require BPE for performance tuning, install the x64 version of SQL Server under Windows 11 using Hyper-V or a virtual machine configured for x64 emulation, since native ARM64 hardware does not currently support this legacy SSD-caching feature.

Troubleshooting Guide: SQL Server Error 867 — Buffer Pool Extension Disabled/Deleted

Why This Error Happens

This message appears when SQL Server 2025 automatically disables and removes the Buffer Pool Extension (BPX) file, typically because the underlying SSD/flash storage path became unavailable, ran out of disk space, or encountered I/O errors during a checkpoint or page read operation. It can also occur if the BPX cache file was manually deleted, moved, or lost permissions while the SQL Server service account no longer has read/write access to it. In some cases, this is a benign informational event triggered intentionally by an administrator running ALTER SERVERCONFIGURATION SET BUFFER POOL EXTENSION OFF, but when unexpected, it usually signals storage instability.

Step-by-Step Resolution Guide

Step 1: Verify the Storage Path and Disk Health

• Confirm the BPX file path (commonly D:\SQLCache\BPE.BIN) still exists and the drive is online in Disk Management.
• Check Windows Event Viewer (System log) for Disk or NTFS warnings (Event ID 7, 51, or 153) around the same timestamp.
• Run chkdsk D: /f (replace D: with your cache drive letter) during a maintenance window to rule out file system corruption.

Step 2: Validate Permissions and Free Space

• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control on the BPX folder via right-click → Properties → Security.
• Confirm at least 10-20% free space remains on the target SSD; BPX creation fails silently if space is insufficient.
• Use PowerShell to check free space: Get-PSDrive D | Select-Object Used,Free.

Step 3: Re-enable the Buffer Pool Extension

• Open SQL Server Management Studio (SSMS) or a query window and run:

  ALTER SERVERCONFIGURATION
  SET BUFFER POOL EXTENSION ON
  (FILENAME = 'D:\SQLCache\BPE.BIN', SIZE = 20GB);

• Adjust SIZE to 4-8x your physical RAM allocation, not exceeding SSD capacity.
• Restart the SQL Server service only if the command returns a pending configuration error.

Step 4: Review SQL Server Error Log for Root Trigger

• Open the log via SSMS → Management → SQL Server Logs, or read directly from C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Search for preceding Error 865 or 866 entries, which usually indicate the specific I/O failure that triggered the auto-disable.
• Correlate timestamps with any storage maintenance, antivirus scans, or SAN failover events.

Alternative or Verification Steps

• Verify success by querying SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; — the state_description column should read “NORMAL” with is_enabled = 1.
• If the BPX continues to disable repeatedly, consider relocating the cache file to a different physical SSD or temporarily disabling BPX until underlying storage hardware is replaced or validated.

Troubleshooting Guide: Buffer Pool Extension Initialization Failure (Error 864)

Why This Error Happens

Error 864 occurs when SQL Server 2025 cannot initialize the Buffer Pool Extension (BPE) file at the requested size, typically because the underlying disk lacks sufficient free space or the SSD/storage path specified is inaccessible. This can also happen if the SQL Server service account lacks NTFS read/write permissions on the target directory, or if the BPE file path points to a non-existent or network-based drive (BPE requires local, non-volatile storage such as SSD). Additionally, specifying a buffer pool extension size that conflicts with system memory configuration or exceeds supported ratios relative to physical RAM can trigger this failure.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Storage Type

• Confirm the target drive has free space exceeding the requested BPE size (e.g., if requesting 64,000 KB, ensure several GB of headroom remains).
• Check that the path (e.g., D:\SQLBPE\bpe.bin) resides on a local SSD, not a network share or HDD-backed volume, since BPE is optimized for low-latency flash storage.
• Run Get-PSDrive -PSProvider FileSystem in PowerShell to confirm available space on the target volume.

Step 2: Validate Service Account Permissions

• Open File Explorer, navigate to the BPE directory, right-click → Properties → Security tab.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Modify and Write permissions on the folder.
• If permissions are missing, grant them via:

  icacls "D:\SQLBPE" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F"

Step 3: Review and Correct the BPE Configuration Syntax

• Open SQL Server Management Studio (SSMS) and run:

  ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION ON
  (FILENAME = 'D:\SQLBPE\bpe.bin', SIZE = 64000 KB);

• Ensure the SIZE value is between 1x and 16x the configured max server memory (SQL Server 2025 enforces this ratio check).
• If a stale or corrupted .bin file exists from a previous failed attempt, delete it manually before re-running the command.

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11, locate SQL Server (MSSQLSERVER), and restart it.
• Alternatively, use PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Monitor the SQL Server Error Log immediately after restart for recurrence of Error 864.

Alternative or Verification Steps

• Verify success by querying sys.dm_os_buffer_pool_extension_configuration in SSMS — the state_description column should read “ENABLED”.
• If the error persists, try creating the BPE file on a different local SSD volume with a simplified path (e.g., C:\BPE\) to rule out path-length or special-character issues.

Troubleshooting Guide: SQL Server Error 865 – Buffer Pool Extension Not Supported

Why This Error Happens

This error occurs because Buffer Pool Extension (BPE) is an edition-specific feature in SQL Server 2025, available only on Standard and Enterprise Editions. If SQL Server is running on Express, Web, or Developer Edition (in some restricted configurations), attempting to enable BPE via ALTER SERVER CONFIGURATION will trigger Error 865. Additionally, this can occur if a Standard/Enterprise instance was improperly licensed or downgraded after installation, causing the edition metadata to mismatch feature availability checks.

Step-by-Step Resolution Guide

Step 1: Verify Your Current SQL Server Edition

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check the active edition:

  SELECT SERVERPROPERTY('Edition') AS Edition, SERVERPROPERTY('EngineEdition') AS EngineEdition;

• Confirm whether the result returns “Express Edition,” “Web Edition,” or “Developer Edition” — these do not support BPE.

Step 2: Check the Buffer Pool Extension Configuration Syntax

• If you are on Standard or Enterprise Edition but still see the error, verify the T-SQL syntax used to enable BPE.
• Correct syntax example:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'E:\SSDCache\BPE.bpe', SIZE = 50 GB);

• Ensure the file path points to a fast SSD volume with sufficient free space, as BPE requires solid-state storage for optimal performance.

Step 3: Upgrade or Repair the SQL Server Edition

• If the server is running an unsupported edition (Express/Web), you must upgrade the edition using the SQL Server Installation Center.
• Navigate to Start > SQL Server 2025 Installation Center > Maintenance > Edition Upgrade.
• Enter a valid Standard or Enterprise Edition product key to unlock BPE functionality.
• Restart the SQL Server service via services.msc or run:

  Restart-Service -Name 'MSSQLSERVER'

Step 4: Validate Windows Server Licensing and Instance Registry Keys

• On Windows Server 2025, open the Registry Editor (regedit) and navigate to:

  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\Setup

• Check the Edition key value matches the licensed SQL Server edition (should read “Standard Edition” or “Enterprise Edition”).
• If mismatched, re-run the SQL Server setup repair tool to resync the edition metadata.

Alternative or Verification Steps

• After applying the fix, re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; to confirm the BPE state shows state_description = 'RUNNING'.
• If an edition upgrade isn’t feasible, consider increasing physical RAM allocation instead, as BPE is primarily a cost-effective alternative to additional memory on budget-constrained Standard Edition deployments.

Troubleshooting Guide: SQL Server Error 861 – Buffer Pool Extension Size Must Be Larger Than Physical Memory

Why This Error Happens

This error occurs when the file size specified for the Buffer Pool Extension (BPE) is smaller than or equal to the amount of physical RAM installed on the server. SQL Server requires the BPE file to exceed physical memory size so it can act as a meaningful secondary cache tier (typically on SSD/NVMe storage) rather than redundantly duplicating RAM capacity. This is a configuration validation failure, not a corruption or permissions issue, and it prevents the BPE feature from initializing entirely.

Step-by-Step Resolution Guide

Step 1: Check Current Physical Memory and BPE Configuration

• Open SQL Server Management Studio (SSMS) and run:

  SELECT total_physical_memory_kb/1024 AS Physical_Memory_MB
  FROM sys.dm_os_sys_memory;

• Review your existing ALTER SERVER CONFIGURATION statement for SET BUFFERPOOLEXTENSION to see the currently defined file size.
• Confirm the error message value (%I64d MB) matches the physical memory reported above.

Step 2: Disable the Existing Buffer Pool Extension Setting

• In SSMS, run the following to turn off BPE before reconfiguring:

  ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION OFF;

• Confirm no errors are returned and that the feature is cleanly disabled before proceeding.

Step 3: Reconfigure BPE with a Correct File Size

• Choose a fast storage path (preferably NVMe/SSD) such as E:\SQLBPE\.
• Set the new file size to be at least 2x to 4x the physical memory size reported in Step 1.
• Run:

  ALTER SERVER CONFIGURATION
  SET BUFFER POOL EXTENSION ON
  (FILENAME = 'E:\SQLBPE\BufferPoolExtension.BPE', SIZE = 65536MB);

• Adjust the SIZE value based on your actual RAM (e.g., if RAM = 32 GB, use 64 GB or higher).

Step 4: Validate Disk Space and Permissions

• Confirm the target drive (e.g., E:\) has sufficient free space to accommodate the new BPE file size.
• Ensure the SQL Server service account has Modify permissions on the target folder via Windows Server 2025 File Explorer or icacls E:\SQLBPE /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F.

Step 5: Restart SQL Server Service

• Open Services.msc on Windows Server 2025/Windows 11.
• Restart the SQL Server (MSSQLSERVER) or named instance service to apply the configuration cleanly.
• Monitor the SQL Server Error Log for successful BPE initialization messages.

Alternative or Verification Steps

• Verify success by querying sys.dm_os_buffer_pool_extension_configuration — the state_description column should show “ON” with no errors in the SQL Server Error Log.
• If the error persists, temporarily disable BPE entirely (SET BUFFER POOL EXTENSION OFF) and confirm whether physical memory was recently increased (e.g., via a VM memory hot-add), which can invalidate a previously valid BPE file size.

Troubleshooting Guide: SQL Server Error 862 – Buffer Pool Extension Disable Failure

Why This Error Happens

This error occurs when SQL Server receives a command to disable the Buffer Pool Extension (BPE) while the instance is in a transitional or incompatible state, such as during startup, shutdown, or while another configuration change is actively being applied. SQL Server 2025 enforces strict state validation for BPE operations because disabling it mid-transition could corrupt the extended memory cache mapping. Additionally, this can happen if a previous ALTER SERVER CONFIGURATION command is still pending or if the BPE file is locked by another process.

Step-by-Step Resolution Guide

Step 1: Verify Current Buffer Pool Extension State

• Open SQL Server Management Studio (SSMS) or a query tool connected via sqlcmd.
• Run the following query to check the current state:

  SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm whether the state column shows 0 (disabled), 1 (enabled), 2 (enabling in progress), or 3 (disabling in progress). If it shows a transitional state (2 or 3), wait for the operation to complete before retrying.

Step 2: Ensure No Conflicting Sessions or Scripts Are Running

• Check sys.dm_exec_requests for any blocking sessions executing ALTER SERVER CONFIGURATION commands.

  SELECT session_id, status, command FROM sys.dm_exec_requests 
  WHERE command LIKE '%BUFFER POOL EXTENSION%';

• Kill any stuck or long-running session using KILL <session_id> if it is safe to do so.
• Confirm no automated maintenance jobs (via SQL Server Agent) are scheduled to modify BPE settings simultaneously.

Step 3: Restart the SQL Server Service Cleanly

• On Windows Server 2025 / Windows 11, open Services.msc and locate SQL Server (MSSQLSERVER) or your named instance.
• Right-click and select Restart to clear any stuck internal state flags.
• Alternatively, use PowerShell as Administrator:

  Restart-Service -Name 'MSSQLSERVER' -Force

• Once the service is back online, re-attempt the disable command:

  ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

Step 4: Check for File Lock or Permission Issues on the BPE Cache File

• Navigate to the directory where the .bpe cache file resides (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Ensure the SQL Server service account has full read/write permissions on this file and folder.
• If the file appears locked, use Resource Monitor or handle.exe (Sysinternals) to identify and release the lock before retrying the disable command.

Step 5: Apply Configuration via T-SQL After State Reset

• Once state is confirmed as stable (0 or 1), execute:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION OFF;

• If the error persists, drop and reconfigure the BPE from scratch:

  ALTER SERVER CONFIGURATION 
  SET BUFFER POOL EXTENSION ON 
  (FILENAME = 'E:\SSDCache\BPE.bpe', SIZE = 4 GB);

Alternative or Verification Steps

• After applying the fix, re-run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and confirm the state column returns 0, confirming BPE is fully disabled.
• If the issue persists after a service restart, check the SQL Server Error Log (via EXEC sp_readerrorlog;) for any additional context tied to Error 862, and consider applying the latest Cumulative Update (CU) for SQL Server 2025, as state-handling bugs in BPE are occasionally patched in CUs.

Troubleshooting Guide: SQL Server Error 863 – Buffer Pool Extension State Conflict

Why This Error Happens

This error occurs when SQL Server attempts to enable the Buffer Pool Extension (BPE) feature while the instance is in a state that does not permit the operation, such as during startup, shutdown, or an active reconfiguration of the buffer pool. BPE enablement requires the server to be in a stable, fully operational state because it directly modifies how memory pages are extended to a non-volatile storage file (typically an SSD-backed .bpe file). This conflict is commonly triggered by executing the ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION command too early in a startup script, during a failover event, or while another configuration change is still being applied internally.

Step-by-Step Resolution Guide

Step 1: Verify Current Server State and Buffer Pool Status

• Open SQL Server Management Studio (SSMS) or a query tool connected via sqlcmd.
• Run the following query to check the current BPE state:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• Confirm the server is not currently in STARTING, RECOVERING, or SHUTTING_DOWN state by checking:

SELECT state_desc FROM sys.databases WHERE name = 'master';

Step 2: Disable and Reset the Buffer Pool Extension Configuration

• If BPE is already partially configured or stuck, disable it first before re-enabling:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;

• Wait a few seconds for the configuration to fully commit before attempting re-enablement.
• Check SQL Server error logs located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation that the OFF state applied successfully.

Step 3: Re-Enable Buffer Pool Extension with Correct Syntax and Timing

• Ensure the SQL Server service has fully completed startup recovery (check Event Viewer under Windows Logs > Application for Event ID 17137, indicating SQL Server startup completion).
• Run the enablement command only after confirming a stable RUNNING state:

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'E:\SSDCache\BPE.BPE', SIZE = 50GB);

• Ensure the target path (e.g., E:\SSDCache\) exists, resides on a fast SSD volume, and that the SQL Server service account has Modify NTFS permissions on that directory.

Step 4: Check for Conflicting Configuration Scripts or Automation Tasks

• Review any startup scripts, SQL Agent jobs, or PowerShell DSC configurations on Windows Server 2025 that may attempt to toggle BPE during instance startup.
• Disable or delay any automation task that runs ALTER SERVER CONFIGURATION commands within the first 60 seconds of SQL Server service start.

Alternative or Verification Steps

• Run SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; again and confirm the state_description column shows ENABLED rather than ENABLEDPENDINGON or DISABLED.
• If the error persists, restart the SQL Server service via services.msc, wait for full recovery completion (confirmed by Event ID 17137), and re-run the ALTER SERVER CONFIGURATION command as a standalone script rather than part of a batch or startup trigger.

Troubleshooting Guide: SQL Server Error 859 – Buffer Pool Extension Configuration Format Error

Why This Error Happens

This error occurs when the BUFFERPOOLEXTENSION configuration string passed to SQL Server does not match the required syntax of filename, size (e.g., E:\SSD\BPE.BIN, 50000MB). It is typically triggered by a malformed T-SQL command, a missing comma separator, an unsupported size unit, or a corrupted/manually edited startup parameter in the registry or configuration file. SQL Server 2025 strictly validates this string format before attempting to allocate the buffer pool extension file, and any deviation—such as extra quotes, missing units (KB/MB/GB), or incorrect path escaping—will immediately throw Error 859.

Step-by-Step Resolution Guide

Step 1: Verify the Correct Syntax of the Command

• Open SQL Server Management Studio (SSMS) or a query window connected to the instance.
• Ensure your command follows this exact pattern:

ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'E:\SSD\BPE.BIN', SIZE = 50000 MB);

• Confirm there is a comma between the FILENAME and SIZE parameters and that the size unit (KB, MB, or GB) is correctly appended without extra spaces or symbols.

Step 2: Check the File Path and Permissions

• Ensure the target drive (e.g., E:\SSD\) exists and is accessible from the Windows Server 2025 / Windows 11 machine.
• Verify that the SQL Server service account has Read/Write permissions on the target folder via Properties > Security in File Explorer.
• Avoid using mapped network drives or UNC paths, as Error 859 can also surface if the path type is unsupported for buffer pool extension files.

Step 3: Inspect and Reset the Server Configuration

• Run the following command to check the current buffer pool extension configuration state:

SELECT * FROM sys.dm_os_buffer_pool_extension_configuration;

• If the configuration is corrupted, disable and reconfigure it:

ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION OFF;
GO
ALTER SERVER CONFIGURATION 
SET BUFFER POOL EXTENSION ON 
(FILENAME = 'E:\SSD\BPE.BIN', SIZE = 50000 MB);

• Restart the SQL Server service via SQL Server Configuration Manager to apply changes cleanly.

Step 4: Review Startup Parameters (If Using -k or Registry Edits)

• Open SQL Server Configuration Manager > SQL Server Services > Properties > Startup Parameters.
• Remove any manually added or duplicate -k trace flags or buffer pool extension parameters that may conflict with the T-SQL configuration.
• Save changes and restart the SQL Server service.

Alternative or Verification Steps

• Confirm the fix by re-running SELECT * FROM sys.dm_os_buffer_pool_extension_configuration; and checking that the state_description column shows ENABLED instead of an error state.
• If the issue persists, check the SQL Server Error Log (via SSMS > Management > SQL Server Logs) for additional details, and validate that no antivirus or disk encryption software is altering the file path syntax before SQL Server processes it.