Troubleshooting Guide: Error 5194 — FILESTREAM Log File Size Must Be Greater Than or Equal to 1 MB

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create, attach, or restore a FILESTREAM-enabled database, but the log file size specified for the FILESTREAM container is set below the required 1 MB minimum threshold. This typically happens due to a misconfigured database creation script, an incorrectly edited model database template, or a manual override in the file growth settings that sets the initial size to 0 KB or a value under 1024 KB. It can also surface during database migration or restore operations where the original FILESTREAM log file metadata was corrupted or improperly specified in the T-SQL command.

Step-by-Step Resolution Guide

Step 1: Review the T-SQL Script or Command Causing the Error

• Locate the CREATE DATABASE or ALTER DATABASE statement responsible for defining the FILESTREAM filegroup.
• Check the SIZE parameter under the FILESTREAM file specification; ensure it is explicitly set to 1MB or higher (e.g., SIZE = 1MB).
• Example of a corrected script snippet:

  ALTER DATABASE [YourDB]
  ADD FILE (NAME = FileStreamLog, FILENAME = 'C:\SQLData\FileStreamLog')
  TO FILEGROUP [FileStreamGroup]

• Confirm no automation tool or PowerShell deployment script is passing a size value of 0 or a null parameter.

Step 2: Verify FILESTREAM Configuration in SQL Server Configuration Manager

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Services > right-click your instance > Properties > FILESTREAM tab.
• Ensure Enable FILESTREAM for Transact-SQL access is checked.
• Confirm the Windows share name is valid and matches the path referenced in your database file structure.

Step 3: Inspect and Repair the Underlying File System Path

• Navigate to the physical FILESTREAM data directory (e.g., C:\SQLData\FileStreamLog).
• Verify the folder is not read-only, corrupted, or redirected to a network share with insufficient permissions.
• If the directory was manually created, delete it and allow SQL Server to regenerate it automatically during the next CREATE DATABASE attempt.
• Check NTFS permissions to ensure the SQL Server service account has Full Control on the FILESTREAM data path.

Step 4: Recreate the Database Using Correct Size Parameters

• Drop the problematic database (if it is not yet in production) using:

  DROP DATABASE [YourDB]

• Rerun the CREATE DATABASE statement with the FILESTREAM log size explicitly defined as 1MB or greater.
• If working with a restore operation, edit the backup metadata script to correct the SIZE value before executing RESTORE DATABASE.

Step 5: Apply the Latest Cumulative Update for SQL Server 2025

• Check Windows Update or the Microsoft Update Catalog for the latest SQL Server 2025 Cumulative Update (CU).
• Install the update, as known FILESTREAM metadata validation bugs are often patched in CU releases.
• Restart the SQL Server service after installation via SQL Server Configuration Manager.

Alternative or Verification Steps

• Run SELECT * FROM sys.master_files WHERE type_desc = 'FILESTREAM' to confirm the file size and path are correctly registered without errors.
• If the issue persists, use SQL Server Management Studio (SSMS) to manually create the database through the GUI, explicitly setting the FILESTREAM log file size field to 1 MB or higher before executing the script.

Troubleshooting Guide: SQL Server Error 5193 — Lease Mismatch File Access Failure

Why This Error Happens

Error 5193 occurs when SQL Server 2025 detects a mismatch between the lease token it holds for a database file and the lease token recorded by the underlying storage layer, typically on network-attached storage, Storage Spaces Direct, or cloud-backed volumes. This usually happens when another process or node has taken ownership of the file lease (common in failover cluster or Always On scenarios), or when the lease expired due to a network interruption or storage latency spike. SQL Server treats this as a potential data integrity risk and proactively takes the database offline rather than risk writing to a file it no longer has exclusive, verified control over.

Step-by-Step Resolution Guide

Step 1: Verify Storage Connectivity and Lease Ownership

• Check Windows Server 2025 Event Viewer under Windows Logs > System for disk or network timeout events (Event ID 153 or 157) that may have preceded the SQL error.
• Run Get-SmbMultichannelConnection or Get-PhysicalDisk in PowerShell to confirm storage path stability if using SMB3 or Storage Spaces Direct.
• Confirm no other SQL Server instance, cluster node, or snapshot/backup tool currently holds an active lease on the same .mdf/.ldf file path.

Step 2: Restart the Affected Database Safely

• Open SQL Server Management Studio (SSMS) or run via sqlcmd:
• ALTER DATABASE [YourDBName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
• ALTER DATABASE [YourDBName] SET ONLINE;
• This forces SQL Server to release and re-acquire a fresh lease token against the storage subsystem.

Step 3: Check Cluster and Always On Availability Group Configuration

• If using Always On AGs or Failover Cluster Instances (FCI), open Failover Cluster Manager and verify only one node is actively hosting the resource group.
• Inspect %ProgramFiles%\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for concurrent lease acquisition attempts from a secondary node.
• Ensure cluster heartbeat network settings (under Networks tab in Failover Cluster Manager) have no latency exceeding the configured SameSubnetThreshold.

Step 4: Update Storage Drivers and SQL Server Cumulative Update

• Confirm the storage HBA/NIC drivers are current via Device Manager > Properties > Driver tab, especially for SMB Direct (RDMA) adapters.
• Apply the latest SQL Server 2025 Cumulative Update from the Microsoft Update Catalog, as early builds may contain known lease-handling timing bugs.
• Reboot the node after driver and CU installation to clear any stale lease cache held by the OS storage driver.

Alternative or Verification Steps

• After bringing the database back online, run DBCC CHECKDB('YourDBName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm no corruption occurred during the lease failure window.
• If the issue recurs intermittently, move the database files to local SSD storage temporarily to isolate whether the root cause is network/storage-layer latency versus a SQL Server configuration problem.

Troubleshooting Guide: SQL Server Error 5192 – Maximum Allowed File Size Exceeded

Why This Error Happens

Error 5192 occurs when a database file (data or log) attempts to grow beyond the maximum size limit defined either by the file’s own MAXSIZE property or by an underlying constraint such as disk volume capacity or file system limitations (e.g., FAT32’s 4GB cap). This is common when autogrowth settings push a file toward a hardcoded ceiling, or when a file is restricted to a fixed size during creation and transactional activity has filled it completely. In virtualized or containerized Windows Server 2025 environments, this can also surface if the underlying VHDX or disk partition itself has less free space than the file’s configured maximum.

Step-by-Step Resolution Guide

Step 1: Identify the Affected File and Current Size Limit

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

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

• Note the max_size value (in 8KB pages) — a value of -1 means unlimited, while any positive number indicates a hard cap.
• Cross-reference the physical file path to confirm which disk volume it resides on in Windows Server 2025.

Step 2: Modify the File’s MAXSIZE Property

• Open a new query window in SSMS and increase or remove the size restriction:

  ALTER DATABASE YourDatabaseName 
  MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = UNLIMITED);

• Alternatively, set a specific larger cap instead of UNLIMITED:

  ALTER DATABASE YourDatabaseName 
  MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = 102400MB);

• Ensure the target disk has sufficient free space to accommodate this new ceiling.

Step 3: Check Underlying Disk and File System Constraints

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 and verify the volume hosting the .mdf/.ldf files is formatted as NTFS or ReFS (not FAT32).
• Confirm there is adequate unallocated space on the physical or virtual disk; extend the VHDX or partition if necessary using:

  Resize-Partition -DriveLetter D -Size (Get-PartitionSupportedSize -DriveLetter D).SizeMax

• Restart the SQL Server service via services.msc if any file-growth settings required a service-level refresh.

Step 4: Review Autogrowth Settings to Prevent Recurrence

• Right-click the database in SSMS, go to Properties > Files, and set a reasonable fixed-size autogrowth increment (e.g., 512MB) instead of a percentage-based growth.
• Avoid setting MAXSIZE too close to current disk capacity to leave headroom for future growth.

Alternative or Verification Steps

• Re-run the sys.master_files query from Step 1 to confirm max_size now reflects the updated value, then trigger a test transaction to ensure the file grows without throwing Error 5192.
• If the error persists, check Windows Event Viewer (Applications and Services Logs > SQL Server) for correlating disk I/O or storage-level errors that may indicate a SAN/VHDX capacity issue overriding the SQL Server configuration.

Troubleshooting Guide: SQL Server Error 5191 – Local Directory Used for TempDB in a Clustered Server

Why This Error Happens

This error occurs when SQL Server 2025 is configured as a clustered instance (Failover Cluster Instance) and the tempdb data or log files are pointed to a local, non-shared directory path that does not exist identically on every node in the Windows Server 2025 cluster. Because failover clustering requires that any node be able to host the SQL Server resource at any time, a path that only exists on one node (or lacks proper permissions) causes validation to fail. This typically stems from manually editing file paths during setup, restoring a configuration from a standalone instance, or forgetting to pre-create the local folder structure on all passive nodes.

Step-by-Step Resolution Guide

Step 1: Verify TempDB Path Consistency Across All Nodes

• Open File Explorer or PowerShell on every node participating in the Windows Server Failover Cluster (WSFC).
• Confirm the exact folder path referenced in the error message (e.g., D:\SQLData\TempDB) exists identically on each node.
• Run Test-Path "D:\SQLData\TempDB" in PowerShell on each node to confirm True is returned.

Step 2: Create the Missing Directory on All Cluster Nodes

• If the path does not exist on one or more nodes, manually create it using New-Item -ItemType Directory -Path "D:\SQLData\TempDB" -Force.
• Ensure the drive letter and folder structure match exactly across nodes (local disks only, not cluster shared storage, since tempdb is intentionally placed on local storage in clustered setups).

Step 3: Assign Correct SQL Server Service Permissions

• Open Computer Management > Local Users and Groups or use icacls to grant the SQL Server service account full control.
• Run: icacls "D:\SQLData\TempDB" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" (replace with the correct service account or named instance service).
• Repeat this permission grant on every node, since local security permissions do not replicate across the cluster automatically.

Step 4: Update TempDB Configuration via SQL Server Configuration Manager

• Open SQL Server Configuration Manager and navigate to the instance’s Startup Parameters, or use ALTER DATABASE tempdb MODIFY FILE in SQL Server Management Studio.
• Set the correct local path that now exists on all nodes:
• ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'D:\SQLData\TempDB\tempdb.mdf');
• Restart the SQL Server service or fail the resource over to apply changes.

Step 5: Validate Cluster Role Failover

• Use Failover Cluster Manager to manually move the SQL Server role to each node.
• Confirm the SQL Server service starts successfully on every node without re-triggering Error 5191.

Alternative or Verification Steps

• Check the SQL Server Error Log and Windows Event Viewer (Application log) after failover to confirm no further Event ID 5191 entries appear.
• As an alternative, use a SQL Server startup trace flag or the -T parameter temporarily to redirect tempdb to a guaranteed-valid local path (e.g., C:\TEMP) on all nodes while permissions and directory structures are being corrected.

Troubleshooting Guide: Error 5190 – Checkpoint Failure on Transactional File System Resource Manager (SQL Server 2025)

Why This Error Happens

Error 5190 occurs when SQL Server 2025, leveraging the Windows Transactional NTFS (TxF) subsystem, fails to checkpoint the resource manager (RM) responsible for tracking filesystem transaction logs, typically due to I/O failures, disk corruption, or insufficient permissions on the $TxfLog directory. This can also happen if the underlying volume has TxF disabled, is running low on disk space, or if third-party antivirus/backup software is locking the transaction log files during the checkpoint operation. Since Windows Server 2025 and Windows 11 have deprecated and restricted TxF functionality by default, SQL Server instances relying on FILESTREAM or FileTable features are particularly susceptible to this NT status failure.

Step-by-Step Resolution Guide

Step 1: Verify Transactional NTFS (TxF) Status on the Volume

• Open an elevated Command Prompt and run fsutil resource info <VolumeLetter>: to check if TxF is active on the drive hosting your SQL Server data files.
• Confirm the resource manager GUID is valid and not marked as corrupted or unavailable.
• If TxF is disabled (common on fresh Windows Server 2025/Windows 11 builds), you will need to re-enable it or migrate away from FILESTREAM/FileTable features that depend on it.

Step 2: Check Disk Space and Health on the Hosting Volume

• Run chkdsk <VolumeLetter>: /f /r during a maintenance window to repair underlying NTFS structural issues.
• Ensure at least 15-20% free space remains on the volume, as TxF checkpointing requires buffer space for the $TxfLog system file.
• Review Event Viewer under Windows Logs > System for any corresponding Disk or Ntfs warnings (Event ID 55 or 140) that correlate with the SQL Server error timestamp.

Step 3: Audit and Correct NTFS Permissions on the Resource Manager

• Navigate to the root of the affected volume and use icacls <VolumeLetter>:\$Extend\$RmMetadata /T to verify the SQL Server service account has Read/Write access.
• Grant the SQL Server service account (e.g., NT Service\MSSQLSERVER) full control over the $TxfLog and $TxfLogContainer system files if access is denied.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER to reinitialize the resource manager handle.

Step 4: Exclude SQL Server Files from Antivirus and Backup Agents

• Add exclusions for the SQL Server data directory (e.g., D:\SQLData\) and the $TxfLog system folder in your antivirus console.
• Disable “real-time scanning” temporarily to test if a third-party agent is locking the file handle during the checkpoint cycle.

Alternative or Verification Steps

• Verify the Fix: Monitor the SQL Server Error Log and Windows Event Viewer for 24-48 hours to confirm Error 5190 does not recur during automatic checkpoint intervals.
• Alternative Solution: If TxF remains unstable due to Microsoft’s deprecation roadmap, migrate FILESTREAM data to a standard VARBINARY(MAX) column or utilize FileTable alternatives that do not rely on the legacy Transactional NTFS driver, as this feature is slated for full removal in future Windows builds.

Troubleshooting Guide: SQL Server Error 5189 – TxF Resource Manager Undo Failure

Why This Error Happens

This error occurs when SQL Server 2025 attempts to roll back or recover a transaction using the Transactional NTFS (TxF) Resource Manager, but the underlying NT status code indicates the operation failed. This is most commonly caused by a corrupted or inaccessible TxF log on the volume hosting the database files, insufficient permissions for the SQL Server service account on the Resource Manager directory, or the TxF feature being disabled/deprecated on Windows Server 2025. Since Microsoft has deprecated TxF in recent Windows builds, filestream or FILETABLE-based databases relying on it may encounter undo failures during crash recovery.

Step-by-Step Resolution Guide

Step 1: Verify TxF Feature Status on the Host OS

• Open PowerShell as Administrator and run: Get-WindowsOptionalFeature -Online -FeatureName TFC-TxF
• Confirm the feature state is “Enabled.” If disabled, this explains the undo failure since TxF is deprecated and may be partially removed in Windows Server 2025 builds.
• If disabled, consider migrating away from FILESTREAM/FILETABLE features that depend on TxF, as re-enabling may not be supported long-term.

Step 2: Check Resource Manager Folder Permissions

• Navigate to the FILESTREAM data container path, typically found under your database’s filegroup directory (e.g., C:\SQLData\MyDB_FileStreamData\).
• Right-click the folder, go to Properties > Security, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run icacls "C:\SQLData\MyDB_FileStreamData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" to reset permissions if needed.

Step 3: Repair or Rebuild the Transactional Resource Manager Log

• Stop the SQL Server service via services.msc or net stop MSSQLSERVER.
• Locate the hidden $TxfLog folder on the root of the volume hosting your database files (e.g., D:\$TxfLog).
• If corruption is suspected, run chkdsk D: /f /r to repair the NTFS volume and its transactional log structures, then restart the SQL Server service.

Step 4: Restore the Database from a Clean Backup

• If the error persists after repair attempts, the FILESTREAM container may be unrecoverable.
• Restore the affected database from the last known good full backup plus transaction logs using RESTORE DATABASE MyDB FROM DISK = 'E:\Backups\MyDB.bak' WITH NORECOVERY, followed by applying log backups.
• Bring the database online with RESTORE DATABASE MyDB WITH RECOVERY.

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB('MyDB') WITH NO_INFOMSGS, ALL_ERRORMSGS to confirm no further corruption exists and that the database comes online cleanly.
• As a long-term alternative, migrate FILESTREAM data to standard BLOB storage or Azure Blob Storage integration, since TxF dependency is being phased out and will continue causing instability on newer Windows Server releases.

Why This Error Happens

Error 5188 occurs when SQL Server’s Transactional NTFS (TxF) integration attempts to replay (redo) operations against a File System Resource Manager (FSRM) during database recovery, but the underlying NTFS transaction log or marshaled data has become corrupted or inaccessible. This is commonly triggered by abrupt power loss, storage controller failures, or disk corruption that occurred while FILESTREAM or FileTable transactions were in flight. Since Windows Server 2025 deprecates and restricts legacy TxF functionality, this error can also surface when TxF is disabled at the OS level while SQL Server still expects it for FILESTREAM recovery.

Step-by-Step Resolution Guide

Step 1: Verify TxF Status on Windows Server 2025 / Windows 11

• Open an elevated PowerShell session and run: fsutil resource info C:
• Confirm whether the Transactional Resource Manager (RM) is present and online for the volume hosting your FILESTREAM data.
• If TxF is disabled (common in hardened Windows Server 2025 builds), re-enable it temporarily using: fsutil transaction ktmutil start

Step 2: Inspect and Repair the KTM (Kernel Transaction Manager) Log

• Locate the resource manager folder, typically hidden under \System Volume Information\{GUID} on the affected volume.
• Run a full chkdsk pass to repair underlying NTFS metadata corruption: chkdsk D: /f /r (replace D: with your FILESTREAM data volume).
• Reboot the server to allow Windows to complete the repair pass before SQL Server restarts recovery.

Step 3: Rebuild the FILESTREAM/FileTable Resource Manager

• Take the affected database offline: ALTER DATABASE [YourDB] SET OFFLINE;
• Remove and recreate the FILESTREAM filegroup container if corruption persists, pointing to a fresh NTFS volume with TxF explicitly enabled.
• Bring the database back online and allow SQL Server to complete redo: ALTER DATABASE [YourDB] SET ONLINE;

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

• If error 5188 persists after repair attempts, restore the database from the last verified full backup plus transaction log chain.
• Use RESTORE DATABASE [YourDB] WITH RECOVERY only after confirming log backups are uncorrupted via RESTORE VERIFYONLY.

Alternative or Verification Steps

• Check the SQL Server error log and Windows Event Viewer (Application log) for subsequent Event ID 5188 recurrences after the fix; a clean startup with no FILESTREAM recovery warnings confirms resolution.
• If the issue reoccurs on the same volume, migrate the FILESTREAM filegroup to a different physical disk, as this strongly indicates underlying storage hardware degradation rather than a software-level NTFS transaction issue.

Troubleshooting Guide: SQL Server Error 5186 – TxF Resource Manager Startup Failure

Why This Error Happens

Error 5186 occurs when SQL Server 2025 attempts to initialize the Transactional File System (TxF) Resource Manager on a database that relies on filestream or filetable functionality, but the underlying NTFS volume cannot process the transaction log correctly. This is typically caused by the TxF feature being disabled at the OS level (common in newer Windows Server 2025 / Windows 11 builds where TxF is deprecated), corrupted transaction metadata in the $TxfLog system directory, or insufficient permissions for the SQL Server service account on the volume hosting the database files. The NT status code accompanying the error usually points to STATUS_OBJECT_NAME_NOT_FOUND or STATUS_ACCESS_DENIED, confirming either a missing resource manager log or a security context mismatch.

Step-by-Step Resolution Guide

Step 1: Verify TxF Status on the Volume

• Open an elevated Command Prompt and run fsutil resource info C: (replace C: with the drive hosting your database files) to check if a resource manager already exists.
• Confirm TxF is not disabled by checking fsutil behavior query DisableTxfAndTm — a return value of 1 means TxF is disabled system-wide.
• If disabled, re-enable temporarily using fsutil behavior set DisableTxfAndTm 0, then restart the server.

Step 2: Check and Repair NTFS Volume Integrity

• Run chkdsk C: /f /r on the affected volume to repair file system inconsistencies, especially within the $TxfLog and $Tops metadata streams.
• Schedule the check on next reboot if the volume is in use: type Y when prompted.
• After reboot, re-run fsutil resource info C: to confirm the resource manager initializes without error.

Step 3: Validate SQL Server Service Account Permissions

• Open Computer Management > Local Users and Groups, confirm the SQL Server service account has Full Control on the database directory (e.g., D:\SQLData\).
• Alternatively, use icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F to explicitly grant permissions.
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Rebuild the Resource Manager (If Corrupted)

• If the error persists, delete the orphaned TxF resource manager handle using fsutil resource delete C: (ensure no active transactions are pending first).
• Reboot the server to allow SQL Server to recreate the resource manager cleanly on next startup.
• Monitor the SQL Server Error Log (Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation that Error 5186 no longer appears.

Alternative or Verification Steps

• Verify the fix by querying SELECT * FROM sys.dm_tran_current_transaction; in SSMS to confirm transactional operations execute without triggering Event ID 5186 in the Windows Event Viewer (System log).
• If TxF remains unstable due to Microsoft’s ongoing deprecation of the feature, consider migrating filestream-dependent tables to FILESTREAM with FILETABLE disabled or moving to BLOB storage via Azure Blob integration, as TxF is not guaranteed in future Windows Server releases.

Troubleshooting Guide: Error 5185 – FILESTREAM Log File Mismatch

Why This Error Happens

Error 5185 occurs when SQL Server 2025 attempts to attach or recover a database and discovers that the FILESTREAM data container on disk does not contain a matching filestream.hdr log record, or the container’s internal GUID does not align with what is registered in the primary database files. This typically happens after an improper file copy (using Windows Explorer instead of a proper backup/restore), a failed or interrupted detach operation, or because the FILESTREAM container was moved/restored without its accompanying transaction log metadata. It can also surface when antivirus or backup software locks or modifies files inside the FILESTREAM container folder, corrupting the internal RSRC/log stream structure.

Step-by-Step Resolution Guide

Step 1: Verify the FILESTREAM Container Structure

• Navigate to the FILESTREAM data container path (e.g., D:\SQLData\FileStreamContainer\).
• Confirm the presence of the hidden filestream.hdr file and the $FSLOG folder.
• Use dir /a in Command Prompt to reveal hidden system files, since Windows Explorer may hide these by default.
• If either filestream.hdr or $FSLOG is missing or has a size of 0 KB, the container is corrupted and cannot be auto-repaired.

Step 2: Detach and Properly Reattach the Database

• Run EXEC sp_detach_db 'YourDatabaseName', 'true'; in SQL Server Management Studio (SSMS) to cleanly detach without losing FILESTREAM pointers.
• Move the entire database folder (not just the .mdf/.ldf) as a single unit to the new location if relocating.
• Reattach using CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\SQLData\YourDB.mdf'), (FILENAME = 'D:\SQLData\YourDB_log.ldf'), (FILENAME = 'D:\SQLData\FileStreamContainer') FOR ATTACH;

Step 3: Restore From a Valid Backup Instead of Manual Copy

• If the container is confirmed corrupted (Step 1), do not attempt manual repair; restore the full database and FILESTREAM filegroup from the last known good .bak file.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDB.bak' WITH REPLACE, RECOVERY;
• Ensure the RESTORE statement includes both the FILESTREAM filegroup and standard filegroups in a single operation.

Step 4: Check Antivirus and Backup Agent Exclusions

• Open Windows Security settings or your third-party antivirus console on Windows Server 2025.
• Add an exclusion rule for the entire FILESTREAM container directory path to prevent real-time scanning from locking or altering the $FSLOG stream during write operations.
• Restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) after applying exclusions.

Alternative or Verification Steps

• Run DBCC CHECKDB('YourDatabaseName') WITH DATA_PURITY; after reattaching to confirm no further consistency errors are reported.
• If the container remains unrecoverable, use ALTER DATABASE YourDatabaseName REMOVE FILE FileStreamGroupName; to drop the broken filegroup and recreate it fresh, then reload FILESTREAM data from your application-layer backup source.

Troubleshooting Guide: SQL Server Error 5184 – Clustered File Resource Dependency Failure

Why This Error Happens

Error 5184 occurs when SQL Server 2025, running as a clustered instance, attempts to create or access a database file located on a disk that is not registered as a dependency resource of the SQL Server cluster role. This typically happens during database creation, file moves, or restores where the target path points to a volume outside the cluster group’s ownership, or the physical disk resource has failed to come online in Failover Cluster Manager. Additionally, this can surface if Storage Spaces Direct (S2D) volumes or CSV (Cluster Shared Volumes) paths are misconfigured and not properly associated with the SQL Server resource dependency chain in Windows Server 2025.

Step-by-Step Resolution Guide

Step 1: Verify Disk Ownership in Failover Cluster Manager

• Open Failover Cluster Manager on the active node.
• Navigate to the specific Role hosting your SQL Server instance.
• Check the Disks tab to confirm the target volume (e.g., E:\ or F:\) is listed as Online and owned by the current node.
• If the disk is missing or offline, right-click it and select Bring Online.

Step 2: Add the Disk as a Dependency Resource

• In Failover Cluster Manager, right-click the SQL Server resource (not the Network Name) under the Role.
• Select Properties, then navigate to the Dependencies tab.
• Click Insert and add the specific disk resource (e.g., “Cluster Disk 3”) that contains the path referenced in the error.
• Click Apply and OK, ensuring no circular dependency errors are thrown.

Step 3: Correct the File Path in T-SQL

• Verify the exact path syntax used in your CREATE DATABASE or ALTER DATABASE command.
• Ensure the path matches the mount point exactly as seen in Disk Management (e.g., D:\MSSQL\Data\ vs. a nested mount point that isn’t clustered).
• Re-run the query:

  ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourDB_dat, FILENAME = 'D:\MSSQL\Data\YourDB.mdf');

Step 4: Restart the SQL Server Resource

• Take the SQL Server resource offline via Failover Cluster Manager.
• Bring the resource back online to force it to re-read the updated dependency tree.
• Monitor the Cluster Events log for any immediate re-triggering of Error 5184.

Alternative or Verification Steps

• Verification: Attempt a test failover of the SQL Server role to a secondary node; if the disk and database move successfully without manual intervention, the dependency is correctly configured.
• Alternative: If the disk cannot be added as a cluster resource (e.g., it is a local, non-shared disk), move the database files to a Cluster Shared Volume (CSV) or a dedicated clustered disk resource instead of a local server path.

Troubleshooting Guide: SQL Server Error 5183 During Database Restore

Why This Error Happens

Error 5183 occurs when SQL Server attempts to restore a database but encounters a conflict with the physical file paths specified in the backup. This typically happens because the target file path or file name already exists on disk (often from a previous restore attempt or an existing database with the same logical file names), or because the original file paths stored in the backup header do not exist on the destination server. Without explicit instructions via WITH MOVE or WITH REPLACE, SQL Server refuses to overwrite or redirect files to avoid accidental data loss.

Step-by-Step Resolution Guide

Step 1: Identify the Logical and Physical File Names in the Backup

• Run the following command to inspect the backup file structure before restoring:

RESTORE FILELISTONLY FROM DISK = 'C:\Backups\YourDatabase.bak';

• Note the LogicalName and PhysicalName columns returned — you will need the LogicalName values for the next step.
• Check if the PhysicalName paths (e.g., D:\SQLData\YourDB.mdf) already exist on the target server, as this is the most common trigger for Error 5183.

Step 2: Restore the Database Using WITH MOVE

• Use the MOVE clause to redirect the data (.mdf) and log (.ldf) files to new, valid paths on Windows Server 2025 or Windows 11:

RESTORE DATABASE YourDatabase
FROM DISK = 'C:\Backups\YourDatabase.bak'
WITH MOVE 'YourDB_Data' TO 'D:\SQLData\YourDatabase.mdf',
     MOVE 'YourDB_Log' TO 'E:\SQLLogs\YourDatabase_log.ldf',
     REPLACE;

• Ensure the destination directories (D:\SQLData\ and E:\SQLLogs\) actually exist; SQL Server will not auto-create folders.
• Verify the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Modify permissions on these target folders via Windows file security settings.

Step 3: Resolve File Conflicts if a Database Already Exists

• If a database with the same name and file paths already exists, add the REPLACE option to explicitly overwrite it:

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

• If the existing database is still attached and in use, take it offline first using SQL Server Management Studio (SSMS) or run:

ALTER DATABASE YourDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE;

• Delete or rename any orphaned .mdf/.ldf files left on disk from a failed previous restore attempt before retrying.

Step 4: Check Folder Permissions and Disk Space

• Open File Explorer, right-click the target data/log folder, select Properties > Security, and confirm the SQL Server service account has Full Control or Modify rights.
• Verify sufficient free disk space exists on the target drive using Get-PSDrive in PowerShell, since insufficient space can also trigger file creation failures that masquerade as path conflicts.

Alternative or Verification Steps

• After the restore completes, run RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourDatabase.bak'; beforehand on future attempts, and confirm the database shows as Online in SSMS under Databases with no error icons.
• If the error persists, use SSMS’s graphical restore wizard (Right-click Databases > Restore Database > Files tab) to manually edit the restore-as paths, which auto-populates valid MOVE paths and avoids manual syntax errors.

Troubleshooting Guide: SQL Server Error 5182 — “New Log File Was Created”

Why This Error Happens

Error 5182 is typically raised during database recovery when SQL Server detects that a transaction log file is missing, corrupted, or has an invalid Log Sequence Number (LSN) chain, forcing the engine to create a new log file to replace the inconsistent one. This often occurs after an improper shutdown, a failed storage subsystem, forcibly detaching a database without flushing transactions, or manually deleting/replacing the .ldf file while the database was offline. Because the newly created log file breaks continuity with prior transactions, the database may be left in a state where point-in-time recovery or log-chain-dependent operations (like replication or Always On) are no longer possible.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and Confirm Log Discontinuity

• Check the SQL Server Error Log (via SSMS > Management > SQL Server Logs, or xp_readerrorlog) for the exact message text referencing error 5182 and the database name.
• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOREPAIR to confirm whether log/data consistency issues exist beyond the log file recreation.
• Note whether the database is currently in SUSPECT, RECOVERY_PENDING, or EMERGENCY mode via SELECT name, state_desc FROM sys.databases;.

Step 2: Restore From a Verified Backup (Preferred Fix)

• If a recent full backup and subsequent log backups exist, restore the full backup first: RESTORE DATABASE YourDatabaseName FROM DISK = 'C:\Backups\YourDatabaseName.bak' WITH NORECOVERY;
• Apply all available transaction log backups in sequence using RESTORE LOG ... WITH NORECOVERY, then finish with WITH RECOVERY on the last one.
• This avoids relying on the auto-created log file and restores full LSN continuity, which is the only fully supported fix for broken log chains.

Step 3: Rebuild the Log File if No Backup Is Available

• Set the database to emergency mode: ALTER DATABASE YourDatabaseName SET EMERGENCY;
• Put the database in single-user mode: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); — note this may cause data loss since it rebuilds the log chain from scratch.
• Return the database to multi-user mode: ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Check Underlying Storage and Permissions

• On Windows Server 2025 / Windows 11, verify the SQL Server service account has Full Control on the data/log directory (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Run chkdsk C: /f (or the relevant drive) during a maintenance window to rule out underlying disk corruption that triggered the original log failure.
• Review Windows Event Viewer (Applications and Services Logs > Windows > Ntfs/Disk) for storage-related warnings around the time of the incident.

Alternative or Verification Steps

• After recovery, run DBCC LOGINFO('YourDatabaseName') and DBCC CHECKDB to confirm the log file is healthy and there are no remaining consistency errors.
• If REPAIRALLOWDATA_LOSS was used, immediately take a fresh full backup and audit affected tables for missing or inconsistent data, since the rebuilt log file breaks the original backup chain going forward.

Why This Error Happens

Error 5181 occurs when SQL Server attempts to bring a database back online during recovery but encounters an obstacle that prevents the database file state from being restored correctly. This is typically caused by corrupted primary data files (.mdf), inaccessible or locked transaction log files (.ldf), insufficient disk space during the recovery process, or the SQL Server service account lacking the necessary NTFS permissions on the database file directory. In many Windows Server 2025 / Windows 11 environments, this also surfaces after an improper shutdown, a failed Windows Update reboot, or antivirus software locking database files mid-recovery.

Step-by-Step Resolution Guide

Step 1: Check the SQL Server Error Log for the Underlying Cause

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Alternatively, locate the raw log file at C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Look for accompanying errors such as 823, 824, or 9004, which indicate I/O or corruption issues tied to the 5181 failure.

Step 2: Verify File Permissions and Service Account Access

• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control on the data/log directory, typically C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA.
• Right-click the folder, go to Properties > Security > Edit, and add the service account if missing.
• Restart the SQL Server service via services.msc or run: net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Set the Database to EMERGENCY Mode and Run Integrity Checks

• Open a new query window in SSMS and execute:

  ALTER DATABASE YourDB SET EMERGENCY;
  DBCC CHECKDB (YourDB) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• Review the output for the extent of corruption before deciding on a repair strategy.

Step 4: Attempt Database Repair or Restore from Backup

• If corruption is confirmed, set the database to single-user mode:

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

• If repair fails or data loss is unacceptable, restore the database from the most recent valid full backup combined with transaction log backups, using RESTORE DATABASE ... WITH NORECOVERY followed by RESTORE LOG as needed.

Step 5: Exclude SQL Directories from Antivirus Scanning

• Add exclusions in Windows Security (or your third-party AV) for the DATA and Log folders under MSSQL16.MSSQLSERVER\MSSQL.
• This prevents file-locking conflicts during future recovery operations, especially after reboots triggered by Windows Server 2025 update cycles.

Alternative or Verification Steps

• After applying fixes, run ALTER DATABASE YourDB SET MULTI_USER; and confirm the database state shows ONLINE via SELECT name, state_desc FROM sys.databases WHERE name = 'YourDB';.
• If the database still fails to come online, consider restoring onto a new instance from the last known-good backup rather than repairing the existing corrupted files, as REPAIR_ALLOW_DATA_LOSS can permanently discard unrecoverable data.

Troubleshooting Guide: SQL Server Error 5180 – Invalid File ID (FCB Open Failure)

Why This Error Happens

Error 5180 occurs when SQL Server’s internal file mapping becomes desynchronized from the actual physical file layout recorded in the database’s system catalog (sys.database_files). This typically happens after an improper file move, a failed autogrow operation, disk-level corruption, or a crash during a shrink/grow operation that left the File Control Block (FCB) pointing to a file ID that no longer resolves correctly. It can also surface after restoring a database where the underlying file structure was altered outside of SQL Server’s transaction log awareness.

Step-by-Step Resolution Guide

Step 1: Verify Database and File State

• Open SQL Server Management Studio (SSMS) or sqlcmd and run sys.database_files and sys.master_files to compare the physical file paths SQL Server expects versus what exists on disk.
• Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG on Windows Server 2025/Windows 11) for accompanying messages like Error 823 or 824, which indicate deeper I/O corruption.
• Confirm the database is not currently in SUSPECT or RECOVERY_PENDING mode by running SELECT name, state_desc FROM sys.databases;.

Step 2: Take the Database Offline and Run Integrity Checks

• Set the database to single-user mode: ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify the full scope of corruption, including the specific invalid file ID referenced in the error.
• Review the output for allocation errors tied to the file ID mentioned in Error 5180 (this confirms whether the issue is metadata-only or involves actual page corruption).

Step 3: Repair or Realign the File Mapping

• If DBCC CHECKDB reports only metadata/file-id mismatches, attempt DBCC CHECKDB ('YourDB', REPAIR_REBUILD) first, as this is non-destructive.
• If corruption is confirmed on data pages, escalate to DBCC CHECKDB ('YourDB', REPAIR_ALLOW_DATA_LOSS) only as a last resort, and only after taking a full backup of the current (damaged) state.
• If the file was physically relocated without updating SQL Server, correct it via ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourLogicalFileName, FILENAME = 'D:\SQLData\YourFile.mdf'); then restart the SQL Server service.

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

• Restore the most recent clean full backup plus subsequent transaction log backups using RESTORE DATABASE [YourDB] FROM DISK = 'E:\Backups\YourDB_Full.bak' WITH NORECOVERY, followed by log restores WITH RECOVERY on the last one.
• Validate file paths in the WITH MOVE clause match your current Windows Server 2025 storage layout if drive letters differ from the original server.

Step 5: Return Database to Multi-User Mode

• Run ALTER DATABASE [YourDB] SET MULTI_USER; once repairs or restore operations are complete.
• Re-run DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS; one final time to confirm zero allocation or consistency errors remain.

Alternative or Verification Steps

• Verify the fix by querying the previously failing table/object directly and confirming DBCC CHECKDB returns “0 allocation errors and 0 consistency errors.”
• If corruption recurs immediately after repair, run Windows Server 2025’s chkdsk D: /f /r (adjust drive letter) and check Event Viewer’s System log for Disk or NTFS warnings, as this points to underlying storage hardware failure rather than a SQL Server-level issue.

Troubleshooting Guide: SQL Server Error 5179 — “Cannot use file ‘%.*ls'”

Why This Error Happens

Error 5179 occurs when SQL Server 2025 attempts to mount a primary data file (.mdf) that is either corrupted, truncated, or does not match the header information SQL Server expects for a valid database file. This frequently happens after an improper file copy, an incomplete disk transfer, antivirus interference, or if the file was mistakenly renamed from a secondary (.ndf) or log (.ldf) file into a primary data file role. It can also surface when NTFS permissions prevent the SQL Server service account from reading the full file header on Windows Server 2025 or Windows 11.

Step-by-Step Resolution Guide

Step 1: Verify File Integrity and Header Information

• Confirm the file wasn’t copied using a method that breaks sparse/NTFS attributes (avoid simple drag-and-drop over network shares).
• Use fsutil file queryextents <path-to-mdf> in an elevated Command Prompt to check if the file structure is intact.
• Compare the file size against the original source file (if available) to rule out truncation during transfer.

Step 2: Check NTFS Permissions for the SQL Server Service Account

• Navigate to the folder location (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Right-click the file, select Properties > Security, and ensure the service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run the following in PowerShell to grant permissions if missing:

  icacls "C:\SQLData\YourDatabase.mdf" /grant "NT Service\MSSQLSERVER:(F)"

Step 3: Confirm the File Is Not Being Blocked by Antivirus or Windows Defender

• Open Windows Security on Windows Server 2025 / Windows 11 and add an exclusion for the SQL Server Data directory.
• Use PowerShell to add an exclusion directly:

  Add-MpPreference -ExclusionPath "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA"

• Restart the SQL Server service via services.msc and attempt to reattach the database.

Step 4: Attempt a Database Attach Using T-SQL with Correct File Mapping

• Ensure you are referencing the correct .mdf as PRIMARY and not accidentally pointing to an .ndf file.
• Run the following query, substituting your actual file paths:

  CREATE DATABASE YourDB
  ON (FILENAME = 'C:\SQLData\YourDB.mdf')
  FOR ATTACH;

• If the log file (.ldf) is missing, use FOR ATTACH_REBUILD_LOG instead (only for user databases, not system databases).

Step 5: Restore From Backup if the File Is Confirmed Corrupted

• If Steps 1–4 fail and the header is unreadable, the .mdf file is likely corrupted beyond repair.
• Restore the most recent valid .bak file using RESTORE DATABASE YourDB FROM DISK = 'C:\Backups\YourDB.bak'.

Alternative or Verification Steps

• Verify the fix by running sp_helpdb YourDB or checking SQL Server Management Studio (SSMS) > Databases to confirm the database shows status “Online” rather than “Suspect” or “Recovery Pending.”
• If attaching still fails, use DBCC CHECKDB('YourDB') WITH PHYSICAL_ONLY after a successful restore to rule out any underlying disk-level corruption before resuming production use.

Troubleshooting Guide: SQL Server Error 5178 – Sector Size Mismatch

Why This Error Happens

This error occurs because SQL Server database files (MDF/NDF/LDF) store the disk sector size of the volume they were originally created on as metadata within the file header. When a database file is moved, restored, or attached to a different volume—such as one using Advanced Format 4Kn drives instead of the original 512-byte or 512e sector size—SQL Server detects the mismatch and blocks the operation to prevent potential I/O corruption. This commonly happens during storage migrations, SAN/NAS reconfigurations, or when moving databases between on-premises servers and cloud-based virtual disks with differing sector size defaults.

Step-by-Step Resolution Guide

Step 1: Identify the Sector Size of the Source and Target Volumes

• Open PowerShell as Administrator and run Get-PhysicalDisk | Get-StorageAdvancedProperty to check the physical sector size.
• Alternatively, use fsutil fsinfo sectorinfo <DriveLetter>: to display both the logical and physical sector sizes of the volume.
• Compare the original volume’s sector size (where the file was created) to the new target volume’s sector size.

Step 2: Migrate the Database File to a Compatible Volume

• Identify or provision a new volume formatted with a sector size equal to or smaller than the original (e.g., 512 bytes or 512e).
• Use Format-Volume -DriveLetter <X> -FileSystem NTFS -AllocationUnitSize 4096 with the correct underlying physical sector size when creating the new volume.
• Move the affected MDF, NDF, and LDF files to this newly formatted volume using File Explorer or Move-Item in PowerShell.

Step 3: Reattach or Restore the Database in SQL Server

• Open SQL Server Management Studio (SSMS) and run sp_detach_db 'YourDatabaseName' if the database is currently attached.
• Reattach the database pointing to the new file location using CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\NewPath\YourDB.mdf') FOR ATTACH;.
• If using a backup/restore approach instead, run RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDB.bak' WITH MOVE 'YourDB_Data' TO 'D:\NewPath\YourDB.mdf', MOVE 'YourDB_Log' TO 'D:\NewPath\YourDB.ldf';.

Step 4: Validate SQL Server Service Account Permissions

• Ensure the SQL Server service account has Full Control (Modify/Read/Write) permissions on the new volume’s directory.
• Navigate to the folder via File Explorer, right-click → Properties → Security tab, and confirm the NT SERVICE\MSSQLSERVER account is listed.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER after confirming permissions.

Alternative or Verification Steps

• Verify the fix by querying SELECT name, physical_name FROM sys.master_files; in SSMS and confirming the database status shows “ONLINE” without errors in the SQL Server Error Log.
• If moving to a compatible sector-size volume isn’t feasible, consider exporting the data via BACKUP DATABASE and performing a fresh RESTORE DATABASE onto the new volume, which rebuilds the file structure natively compatible with the target sector size.

Troubleshooting Guide: SQL Server Error 5177 – Sector Size Check Failure

Why This Error Happens

This error occurs when SQL Server 2025 attempts to verify the physical sector size of a database file but cannot complete the check because the file resides on a volume that doesn’t support the required NTFS-level API calls (such as network shares, ReFS volumes without proper configuration, or compressed/encrypted drives). It can also happen if the underlying disk uses a non-standard sector size (like 4Kn drives) that conflicts with SQL Server’s I/O alignment requirements. Additionally, corrupted file system metadata or insufficient permissions on the storage volume can prevent the GetDiskFreeSpace or FSCTL_QUERY_DISK_SECTOR_SIZE API calls from returning valid data.

Step-by-Step Resolution Guide

Step 1: Verify the File Location and Volume Type

• Confirm the database file (.mdf, .ldf, or .ndf) is not stored on a network share (UNC path), USB drive, or mapped network drive.
• Run fsutil fsinfo volumeinfo C: in an elevated Command Prompt to confirm the volume is formatted as NTFS, not ReFS, FAT32, or exFAT.
• If the file is on a non-NTFS volume, plan to migrate it to a local NTFS-formatted disk.

Step 2: Move the Database File to a Supported Local NTFS Volume

• Stop the SQL Server service via services.msc or run NET STOP MSSQLSERVER in an elevated prompt.
• Physically copy the affected .mdf/.ldf file to a local NTFS path, such as C:\SQLData\.
• Update the file path reference using ALTER DATABASE [YourDB] MODIFY FILE (NAME = YourLogicalFileName, FILENAME = 'C:\SQLData\YourFile.mdf').
• Restart the SQL Server service with NET START MSSQLSERVER.

Step 3: Check Disk Sector Size Compatibility

• Run fsutil fsinfo sectorinfo C:\SQLData to inspect the physical and logical sector size of the target drive.
• Ensure the drive does not report an unsupported Advanced Format (4Kn) configuration without proper driver support in Windows Server 2025.
• If using 4Kn drives, confirm you are running the latest storage controller and SQL Server 2025 cumulative update, as early builds have had compatibility issues with native 4K sector reporting.

Step 4: Validate Permissions on the Storage Volume

• Right-click the target folder (e.g., C:\SQLData), select Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Run icacls C:\SQLData from an elevated Command Prompt to audit current permissions.
• Grant missing permissions using icacls C:\SQLData /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F.

Step 5: Rule Out File System Corruption

• Run chkdsk C: /f /r and schedule a restart if prompted, to repair any underlying NTFS structure issues.
• Re-attempt the database attach or file creation operation once the check completes.

Alternative or Verification Steps

• After moving the file, attempt to attach or restore the database again using sp_attach_db or RESTORE DATABASE, and confirm Error 5177 no longer appears in the SQL Server Error Log.
• If the error persists on a local NTFS drive, test with a brand-new, simple database file created directly via CREATE DATABASE TestDB ON (NAME = TestDB, FILENAME = 'C:\SQLData\TestDB.mdf') to isolate whether the issue is file-specific or environment-wide (driver/storage controller related).

Troubleshooting Guide: SQL Server Error 5176

Why This Error Happens

Error 5176 typically occurs when SQL Server encounters a database file (MDF/LDF/NDF) during recovery that has an inconsistent or mismatched Virtual Log File (VLF) sequence, often caused by improper shutdowns, storage-level corruption, or a forcibly detached database file being reattached incorrectly. This can also happen if the file was copied or restored from a snapshot while SQL Server still held an exclusive lock on it, leaving the header metadata out of sync with the actual log chain. In most cases, the root cause is a corrupted or truncated transaction log file that prevents the recovery process from replaying committed transactions.

Step-by-Step Resolution Guide

Step 1: Verify Database and File State

• Open SQL Server Management Studio (SSMS) or run a query via sqlcmd to check the current state of the affected database.
• Execute: SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Confirm whether the state shows SUSPECT, RECOVERY_PENDING, or RESTORING.
• Check the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact file path referenced in Error 5176.

Step 2: Take the Database Offline and Inspect File Permissions

• Stop dependent applications or services connecting to the instance.
• In SSMS, right-click the database, select Tasks > Take Offline (if accessible), or run: ALTER DATABASE YourDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE;
• Navigate to the data file directory (commonly D:\SQLData\ or D:\SQLLogs\) and verify that the SQL Server service account has Full Control NTFS permissions.
• Right-click the .mdf/.ldf files > Properties > Security tab, and confirm NT SERVICE\MSSQLSERVER has read/write access.

Step 3: Rebuild or Attach with Emergency Mode

• Set the database into Emergency Mode to allow minimal recovery access: ALTER DATABASE YourDatabaseName SET EMERGENCY;
• Run a consistency check to identify corruption: DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS;
• If corruption is confirmed in the log file, rebuild it using: ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE YourDatabaseName REBUILD LOG ON (NAME=YourLogName, FILENAME='D:\SQLLogs\YourDatabaseName_log.ldf');
• Restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 4: Restore From Backup (If Rebuild Fails)

• If the log rebuild does not resolve the issue, restore the last known good full backup along with subsequent transaction log backups.
• Use: RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH NORECOVERY;
• Apply log backups sequentially with WITH RECOVERY on the final file to bring the database back online.

Alternative or Verification Steps

• Run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; and confirm the status returns ONLINE.
• If the database remains inaccessible, use the SQL Server Configuration Manager to confirm the service is running under the correct account, then re-attempt attaching the database using CREATE DATABASE YourDatabaseName ON (FILENAME = 'D:\SQLData\YourDatabaseName.mdf') FOR ATTACH_REBUILD_LOG; as a last resort.

Troubleshooting Guide: Error 5175 – File Expanded to Allow Recovery to Succeed

Why This Error Happens

This error occurs when SQL Server encounters a database file during crash recovery that is smaller than what the transaction log indicates it should be, forcing SQL Server to automatically grow the file to complete the recovery process. This typically happens after an unexpected shutdown, forced termination of the SQL Server service, or a VM snapshot restore where the physical data/log file was truncated or did not match its last known logical size. It can also occur when disk space constraints or third-party backup tools interact improperly with the file system, leaving file metadata inconsistent with actual allocation.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and File System Health

• Open File Explorer or run Get-PSDrive in PowerShell to confirm the volume hosting .mdf/.ldf files has sufficient free space (at least 2-3x the database size recommended).
• Run chkdsk C: /f /r (replace C: with the correct drive letter) on Windows Server 2025 to rule out underlying file system corruption.
• Check Windows Event Viewer under Windows Logs > System for Disk or NTFS warnings around the time of the crash.

Step 2: Review SQL Server Error Log for Recovery Status

• Navigate to C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG (path may vary based on instance name/version folder).
• Search for entries immediately before and after Error 5175, such as “Starting up database” and “Recovery is writing a checkpoint.”
• Confirm whether the database successfully completed recovery by looking for the message “Recovery completed” or “CHECKDB found 0 allocation errors.”

Step 3: Run DBCC CHECKDB to Validate Database Integrity

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Execute the following query against the impacted database:

  DBCC CHECKDB (YourDatabaseName) WITH NO_INFOMSGS, ALL_ERRORMSGS;

• If corruption is found, review repair options (REPAIR_REBUILD preferred over REPAIR_ALLOW_DATA_LOSS) based on the severity reported.

Step 4: Adjust Autogrowth Settings to Prevent Recurrence

• Right-click the database in SSMS, select Properties > Files.
• Set Autogrowth for both data and log files to a fixed MB value (e.g., 256MB) instead of a percentage, avoiding unpredictable expansion behavior.
• Ensure Instant File Initialization is enabled by confirming the SQL Server service account has the “Perform Volume Maintenance Tasks” permission in secpol.msc under Local Policies > User Rights Assignment.

Step 5: Restart SQL Server Service and Monitor

• Open Services.msc on Windows Server 2025, locate SQL Server (MSSQLSERVER), and restart it.
• Monitor the ERRORLOG again for successful database recovery messages without repeated Error 5175 entries.

Alternative or Verification Steps

• Run SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; in SSMS to confirm the database status shows ONLINE rather than RECOVERY_PENDING or SUSPECT.
• If the error persists, restore the database from the last known good backup using RESTORE DATABASE YourDatabaseName FROM DISK = 'BackupPath' WITH RECOVERY; to bypass file-level inconsistencies entirely.

Troubleshooting Guide: SQL Server Error 5174 — “Each File Size Must Be Greater Than or Equal to 512 KB”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to attach, create, or restore a database file (.mdf, .ndf, or .ldf) that is either corrupted, truncated, or has a reported file size smaller than the minimum 512 KB threshold required by the Database Engine. This typically happens when a file was incompletely copied, transferred over a network with interruptions, compressed/decompressed improperly, or manually edited outside of SQL Server. It can also occur if the file header is corrupted, causing SQL Server to misread the actual file size during the attach or restore operation.

Step-by-Step Resolution Guide

Step 1: Verify the Actual File Size on Disk

• Navigate to the file location, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .mdf or .ldf file and check Properties > Size.
• If the file is significantly smaller than expected (e.g., a few KB instead of several MB), the file is likely corrupted or incomplete.
• Run dir in Command Prompt from the DATA directory to confirm the exact byte size matches SQL Server’s expectations.

Step 2: Re-copy or Re-transfer the Database File

• If the file originated from a network share, USB drive, or cloud storage, re-copy it using a lossless transfer method.
• Use Robocopy instead of standard copy-paste to preserve file integrity:
• robocopy "D:\Source" "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA" filename.mdf /Z
• Verify checksums before and after transfer using Get-FileHash in PowerShell to confirm no data loss occurred.

Step 3: Attempt to Reattach the Database Using T-SQL

• Open SQL Server Management Studio (SSMS) and connect to your instance.
• Run the following command to attempt a manual attach:

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

• If this fails with Error 5174, the file itself is confirmed as the point of failure, not a permissions or path issue.

Step 4: Restore From a Known-Good Backup

• If the source file cannot be repaired, locate the most recent .bak file.
• Restore using:

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

• Ensure the backup file itself is also above the 512 KB minimum and not truncated.

Step 5: Check for Antivirus or Storage Driver Interference

• Confirm Windows Defender or third-party antivirus is not quarantining or locking the SQL data files during transfer.
• Add an exclusion for the SQL Server DATA directory in Windows Security > Virus & Threat Protection > Exclusions.
• Re-run the attach operation after confirming no file locks are present using Resource Monitor.

Alternative or Verification Steps

• To verify the fix, run sp_helpdb YourDBName in SSMS and confirm the database status shows ONLINE with the correct file sizes listed under size (in 8-KB pages, so 512 KB = 64 pages minimum).
• If the original file cannot be recovered and no backup exists, use a third-party MDF repair tool to attempt data extraction before rebuilding the database from scratch.

Why This Error Happens

Error 5173 typically occurs when SQL Server cannot locate one or more secondary data files (.ndf) or log files (.ldf) that are associated with the primary data file (.mdf) during an attach operation. This often happens because the files were moved, renamed, or are missing from their original path, or because the log file (LDF) has become detached/mismatched with the primary file’s internal metadata (such as the Database ID or LSN sequence). It can also occur if the file structure recorded in the primary file header doesn’t match the physical files currently present on disk.

Step-by-Step Resolution Guide

Step 1: Verify File Locations and Permissions

• Ensure all .mdf, .ndf, and .ldf files for the database are located in the exact paths referenced in your CREATE DATABASE... FOR ATTACH script.
• Check that the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Read/Write permissions on the folder, typically found at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA.
• Confirm the files are not marked as “Read-Only” via File Properties in Windows 11/Server 2025 Explorer.

Step 2: Attempt to Attach Without the Log File

• If the primary data file (MDF) is healthy but the log file is missing or corrupted, use the following T-SQL command to force SQL Server to rebuild the log:

CREATE DATABASE [YourDB] ON 
(FILENAME = 'C:\Data\YourDB.mdf')
FOR ATTACH_REBUILD_LOG;

• Note: This only works if the database was shut down cleanly prior to detachment.

Step 3: Use spattachdb for Legacy Compatibility Checks

• If using older attachment syntax, ensure you are listing every single file (including all NDFs) in the correct order.
• Run EXEC sp_attach_db @dbname = 'YourDB', @filename1 = 'C:\Data\YourDB.mdf', @filename2 = 'C:\Data\YourDB_log.ldf';
• If the error persists, check the SQL Server Error Log (via SSMS Object Explorer > Management > SQL Server Logs) for specific details on which exact file is causing the mismatch.

Step 4: Rebuild the Database Structure via Detach/Reattach

• If files were copied from a different server, ensure they weren’t copied while the source SQL instance was still running (which corrupts the header).
• Stop the SQL Server service via services.msc, copy the files again ensuring the source DB was properly detached first, and restart the service.

Alternative or Verification Steps

• Verification: Run sp_helpdb YourDB or query sys.master_files to confirm all files show a “ONLINE” status and the paths match the physical disk locations.
• Alternative: If the log file is unrecoverable and ATTACH_REBUILD_LOG fails, restore the database from the most recent .bak file instead of attempting a manual attach.

Troubleshooting Guide: SQL Server Error 5172 — Invalid Database File Header

Why This Error Happens

Error 5172 occurs when SQL Server reads the header of a .mdf or .ndf file and finds that it does not match the expected database file structure, typically because the file was truncated, copied incorrectly, or corrupted due to a storage failure or improper shutdown. This can also happen if the file was renamed, edited outside of SQL Server, or affected by antivirus/backup software locking or modifying it mid-write. In some cases, the header mismatch results from attempting to attach a file created by an incompatible or newer version of SQL Server.

Step-by-Step Resolution Guide

Step 1: Verify the File Integrity and Source

• Confirm the .mdf/.ndf file was not copied while SQL Server was still writing to it (use a clean copy taken after a graceful shutdown).
• Check the file size in File Explorer or via Get-ItemProperty in PowerShell — a 0 KB or unusually small file indicates truncation/corruption.
• Review Windows Event Viewer (Windows Logs > Application) for related disk or storage errors (Event ID 7, 9, or 51) that may indicate hardware-level corruption.

Step 2: Attempt to Reattach the Database Using T-SQL

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

  EXEC sp_detach_db 'YourDatabaseName';

• Then try reattaching with:

  CREATE DATABASE YourDatabaseName 
  ON (FILENAME = 'C:\SQLData\YourDatabaseName.mdf')
  FOR ATTACH;

• If the error persists, this confirms the header is genuinely unreadable rather than a locking issue.

Step 3: Restore From the Last Known Good Backup

• Navigate to your backup repository (commonly C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\).
• Run a restore using:

  RESTORE DATABASE YourDatabaseName 
  FROM DISK = 'C:\SQLData\Backup\YourDatabaseName.bak' 
  WITH REPLACE;

• Ensure the SQL Server Service Account has Read/Write permissions on the backup folder (right-click folder > Properties > Security tab).

Step 4: Rebuild Database Using Emergency Mode (If No Backup Exists)

• Set the database to emergency mode to attempt data recovery:

  ALTER DATABASE YourDatabaseName SET EMERGENCY;
  DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

• Note this step is a last resort, as REPAIR_ALLOW_DATA_LOSS may result in permanent loss of unrecoverable pages.

Alternative or Verification Steps

• After reattaching or restoring, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there are no further consistency errors.
• If corruption persists, use a disk-level tool (e.g., CHKDSK on Windows Server 2025) to rule out underlying storage/filesystem issues before retrying the SQL Server attach process.

Troubleshooting Guide: SQL Server Error 5171 – “%.*ls is not a primary database file”

Why This Error Happens

This error occurs when SQL Server attempts to attach or restore a database but the file specified as the primary data file (.mdf) is either corrupted, mislabeled, or is actually a secondary (.ndf) or log (.ldf) file being passed in the wrong parameter. It can also happen if the file header has been damaged due to improper shutdown, disk corruption, or if a non-SQL file (with an .mdf extension) is being referenced. Additionally, this can surface if the database file was edited or truncated by an external process, corrupting the file header signature that SQL Server uses to identify it as a primary file.

Step-by-Step Resolution Guide

Step 1: Verify the File Being Attached is Correct

• Confirm you are pointing to the actual primary .mdf file and not an .ndf or .ldf file in your CREATE DATABASE ... FOR ATTACH or sp_attach_db command.
• Check the file path, e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDB.mdf.
• Right-click the file in Windows Explorer and confirm the file size is non-zero and matches expected backups.

Step 2: Inspect the File Header for Corruption

• Use a hex editor or PowerShell to check the first few bytes of the file for the standard SQL Server page header signature.
• Run the following command to attempt a header-only validation:

  DBCC CHECKPRIMARYFILE ('C:\Path\To\YourDB.mdf', 2)

• If the command returns errors or fails silently, the file header is likely corrupted and the file cannot be used as-is.

Step 3: Restore from a Known Good Backup

• Navigate to your backup directory and locate the most recent .bak file.
• Use SQL Server Management Studio (SSMS) or T-SQL to restore the database:

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

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

Step 4: Rebuild Using Emergency Mode (If No Backup Exists)

• As a last resort, attempt to bring the database online using emergency mode repair:

  ALTER DATABASE YourDB SET EMERGENCY;
  DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS);

• Be aware this step may result in data loss and should only be used when no valid backups are available.

Step 5: Validate File System and Disk Integrity

• Run chkdsk C: /f (replace with the appropriate drive letter) to rule out underlying disk corruption on Windows Server 2025 or Windows 11.
• Check Windows Event Viewer under Windows Logs > System for any disk-related warnings (Event ID 7 or 11) that may indicate hardware-level issues affecting the file.

Alternative or Verification Steps

• After attaching or restoring, run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDB'; to confirm the database state reads ONLINE.
• If attachment still fails, consider using the sp_attach_single_file_db stored procedure as an alternative, which forces SQL Server to rebuild the transaction log while using only the primary data file.

Troubleshooting Guide: SQL Server Error 5170 – “Cannot Create File Because It Already Exists”

Why This Error Happens

Error 5170 occurs when SQL Server attempts to create a new database file (.mdf, .ndf, or .ldf) at a specified path, but a file with that exact name already exists in the target directory. This typically happens during database creation, restore operations, or file attachment when a previous database wasn’t cleanly dropped, leaving orphaned files behind. It can also occur if two databases are mistakenly configured to use the same physical file path, or if a prior failed operation left residual files that were never cleaned up.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting File Path

• Open the full error message in SQL Server Management Studio (SSMS) or the SQL Server Error Log to locate the exact file path referenced (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf).
• Navigate to that directory using File Explorer or PowerShell:

  Get-ChildItem "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\"

• Confirm whether the file physically exists on disk.

Step 2: Verify the File Is Not In Use by Another Database

• Run the following query to check if the file path is already registered to an active database:

  SELECT name, physical_name, state_desc 
  FROM sys.master_files 
  WHERE physical_name = 'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf';

• If a database is actively using this file, choose a different file name or path for your new database/restore operation instead of overwriting it.

Step 3: Remove or Rename the Orphaned File

• If the file is confirmed orphaned (not tied to any active database in sys.master_files), stop the SQL Server service temporarily:

  net stop MSSQLSERVER

• Rename or delete the orphaned .mdf/.ldf file from the DATA directory.
• Restart the SQL Server service:

  net start MSSQLSERVER

Step 4: Specify a Unique File Path in Your Script

• When creating a new database or restoring a backup, explicitly define a new, non-conflicting file name using the MOVE option (for restores) or a custom path in CREATE DATABASE:

  RESTORE DATABASE MyDatabase 
  FROM DISK = 'C:\Backups\MyDatabase.bak'
  WITH MOVE 'MyDatabase' TO 'C:\SQLData\MyDatabase_New.mdf',
       MOVE 'MyDatabase_log' TO 'C:\SQLData\MyDatabase_New_log.ldf';

• Ensure the new path does not already contain files with matching names.

Step 5: Check and Correct NTFS Permissions

• Verify the SQL Server service account has read/write/delete permissions on the target DATA folder via right-click → Properties → Security tab.
• Grant Full Control to the SQL Server service account (e.g., NT Service\MSSQLSERVER) if permissions are restrictive, as insufficient permissions can sometimes prevent proper file cleanup and cause stale file locks.

Alternative or Verification Steps

• After applying the fix, re-run the CREATE DATABASE or RESTORE command and confirm it completes without Error 5170; then validate the database is online using SELECT name, state_desc FROM sys.databases WHERE name = 'MyDatabase';.
• If the error persists, use Process Explorer or Resource Monitor to check if another process (such as an antivirus scanner or backup agent) has a lock on the file, and temporarily exclude the SQL Server DATA directory from real-time scanning.

Troubleshooting Guide: SQL Server Error 5169 – FILEGROWTH Cannot Be Greater Than MAXSIZE

Why This Error Happens

This error occurs when a database or log file’s autogrowth increment (FILEGROWTH) is configured to a value larger than the maximum size limit (MAXSIZE) set for that same file. SQL Server 2025 validates these two parameters whenever a file definition is created or altered, and it will reject the command if growth settings conflict with the ceiling you’ve imposed. This commonly happens when administrators set a fixed growth value (e.g., 500MB) on a file but forget to update or raise the MAXSIZE accordingly, or when migrating scripts from an older server where limits were different.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting File and Current Settings

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to your instance.
• Run the following query to inspect current file configurations:

SELECT name, size, growth, max_size, is_percent_growth 
FROM sys.database_files;

• Note the file name referenced in the error message (%.*ls) and compare its growth and max_size columns.

Step 2: Adjust MAXSIZE to Accommodate Growth

• If the file needs to continue growing, increase the MAXSIZE value to be equal to or greater than the FILEGROWTH setting.
• Execute an ALTER DATABASE command, for example:

ALTER DATABASE YourDatabaseName 
MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = 10GB);

• Alternatively, set MAXSIZE to UNLIMITED if the underlying disk volume on Windows Server 2025 has sufficient free space:

ALTER DATABASE YourDatabaseName 
MODIFY FILE (NAME = YourLogicalFileName, MAXSIZE = UNLIMITED);

Step 3: Alternatively, Lower the FILEGROWTH Increment

• If disk space is constrained and you cannot raise MAXSIZE, reduce the FILEGROWTH value instead.
• Run the following to set a smaller, fixed growth increment:

ALTER DATABASE YourDatabaseName 
MODIFY FILE (NAME = YourLogicalFileName, FILEGROWTH = 50MB);

• Avoid using percentage-based growth on large files, as it can unexpectedly outpace a low MAXSIZE cap over time.

Step 4: Verify Disk Capacity on the Host Server

• On Windows Server 2025/Windows 11, open File Explorer or run Get-PSDrive in PowerShell to confirm the target volume has enough free space to support the new MAXSIZE value.
• Ensure the SQL Server service account has write permissions to the directory where the .mdf/.ldf files reside, typically under C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\.

Alternative or Verification Steps

• Re-run the SELECT name, size, growth, max_size FROM sys.database_files; query to confirm that growth no longer exceeds max_size, and attempt the original operation (e.g., manual growth or insert) that triggered the error.
• If the issue persists, check SQL Server Error Logs via EXEC sp_readerrorlog; to rule out underlying disk I/O or permission errors masking as a growth/size conflict.

Troubleshooting Guide: SQL Server Error 5161 – Unexpected File ID Mismatch

Why This Error Happens

Error 5161 occurs when SQL Server reads the header of a database file and finds that its internal file ID does not match the ID recorded in the sys.master_files system catalog. This mismatch is typically caused by manually copying, renaming, or restoring .mdf/.ndf/.ldf files outside of proper SQL Server procedures (such as copying a file from another database or an older backup over an existing file). It can also occur after improper SAN snapshot restores, failed storage migrations, or when files are swapped between environments without updating the corresponding database metadata.

Step-by-Step Resolution Guide

Step 1: Take the Database Offline and Identify the Mismatch

• Open SQL Server Management Studio (SSMS) or a query window connected to the SQL Server 2025 instance.
• Run the following to confirm the expected file mappings:

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

• Note the exact file_id and physical_name values reported in the error message versus what SQL Server expected.

Step 2: Verify the Physical File on Disk

• Navigate to the file path (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) using File Explorer or PowerShell on Windows Server 2025/Windows 11.
• Use PowerShell to confirm file properties and check for accidental overwrites:

  Get-Item "C:\...\DATA\YourFile.ndf" | Select-Object Name, Length, LastWriteTime

• If the file was mistakenly replaced (e.g., a file from a different database copied into this path), restore the correct original file from a verified backup.

Step 3: Correct the File Mapping Using ALTER DATABASE

• If the physical file is correct but the metadata is wrong, take the database offline:

  ALTER DATABASE YourDatabaseName SET OFFLINE;

• Update the file mapping to point to the correct physical file:

  ALTER DATABASE YourDatabaseName 
  MODIFY FILE (NAME = LogicalFileName, FILENAME = 'C:\...\DATA\CorrectFile.ndf');

• Bring the database back online:

  ALTER DATABASE YourDatabaseName SET ONLINE;

Step 4: Restore from Backup if Mapping Cannot Be Corrected

• If the original file is missing, corrupted, or unrecoverable, restore the full database (and relevant differential/log backups) to a consistent point:

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

• Ensure the WITH MOVE option is used if file paths have changed since the backup was taken.

Alternative or Verification Steps

• After applying the fix, run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no further file-level corruption and that the database is fully consistent.
• If the error persists after remapping, detach the database entirely (sp_detach_db) and reattach it using CREATE DATABASE ... FOR ATTACH with the correct file list to force SQL Server to rebuild the file ID associations from scratch.

Troubleshooting Guide: SQL Server Error 5159

Why This Error Happens

Error 5159 occurs when the SQL Server service account lacks the necessary file system permissions to read, write, or create a database file (MDF, NDF, or LDF) at the specified path. This can also result from the underlying disk or volume being offline, disconnected, or experiencing a hardware-level I/O failure. Additionally, antivirus software or Windows file locking mechanisms may be intercepting the operating system call before SQL Server can complete the operation.

Step-by-Step Resolution Guide

Step 1: Verify File and Folder Permissions

• Navigate to the folder containing the database file (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• 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 the service account, and grant Full Control permissions, then click Apply.

Step 2: Confirm the Disk Volume Is Online and Healthy

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 / Windows 11.
• Check that the volume hosting the database file shows a status of Online and Healthy.
• If the disk shows Offline or Unknown, right-click the disk and select Online, then run chkdsk C: /f from an elevated Command Prompt to repair file system errors.

Step 3: Exclude SQL Server Directories from Antivirus Scanning

• Open Windows Security or your third-party antivirus console.
• Add exclusions for the SQL Server data, log, and backup directories (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\*.mdf, *.ldf, *.ndf).
• Restart the SQL Server (MSSQLSERVER) service via services.msc to apply the changes.

Step 4: Check for File Locks or Orphaned Handles

• Use Process Explorer (Sysinternals) to search for any process holding a lock on the affected .mdf or .ldf file.
• Terminate any non-SQL process holding the handle, or reboot the server if the lock cannot be released safely.
• Attempt to bring the database back online using ALTER DATABASE [YourDB] SET ONLINE; in SQL Server Management Studio (SSMS).

Step 5: Validate SQL Server Service Account Configuration

• Open SQL Server Configuration Manager, select SQL Server Services, and confirm the correct service account is configured under the Log On tab.
• If the account was recently changed (e.g., from Local System to a domain account), ensure it has been re-granted NTFS permissions on the data/log directories as described in Step 1.
• Restart the SQL Server service for changes to take effect.

Alternative or Verification Steps

• Run sqlcmd -S localhost -Q "DBCC CHECKDB('YourDatabase')" to confirm the database is accessible and free of corruption after applying the fix.
• If the issue persists, restore the affected database file from the most recent backup to a healthy volume and reattach it using CREATE DATABASE [YourDB] ON (FILENAME = 'NewPath\YourDB.mdf') FOR ATTACH;.

Why This Error Happens

Error 5153 occurs because the OVERFLOW clause introduced for memory-optimized filegroup elasticity in SQL Server 2025 is incompatible with the legacy SQL Data Warehouse (SQL DW) emulation layer, informally called the “SQL DW back door,” which is used for backward compatibility with older Parallel Data Warehouse (PDW) workloads. When a database is running under this legacy compatibility mode, the engine blocks any ALTER DATABASE ... MODIFY FILEGROUP statement that includes OVERFLOW, since the DW emulation layer does not recognize the new storage allocation flag. This is typically triggered by an outdated compatibility level, a misconfigured Database Scoped Configuration, or a script ported from an older DW-tier instance that still references deprecated syntax.

Step-by-Step Resolution Guide

Step 1: Confirm the Database Compatibility Mode

• Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your SQL Server 2025 instance.
• Run the following query to check if the database is in legacy DW emulation mode:

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

• If is_dw_compat_mode returns 1, this confirms the root cause.

Step 2: Disable the SQL DW Back Door Compatibility Flag

• Run the following T-SQL command to disable legacy DW emulation for the affected database:

  ALTER DATABASE YourDatabaseName
  SET COMPATIBILITY_LEVEL = 170;

• Then explicitly turn off the DW back door flag using the new Database Scoped Configuration option:

  ALTER DATABASE SCOPED CONFIGURATION SET DW_LEGACY_MODE = OFF;

• This step must be run in single-user mode if the database was originally restored from a PDW backup file (.bak with DW headers).

Step 3: Re-run the ALTER DATABASE MODIFY Statement

• Once legacy mode is disabled, retry the original command:

  ALTER DATABASE YourDatabaseName
  MODIFY FILEGROUP [PRIMARY]
  SET OVERFLOW = ON;

• Ensure the SQL Server service account has write permissions on the underlying filegroup path (typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).

Step 4: Restart the SQL Server Service

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• Alternatively, use PowerShell:

  Restart-Service -Name 'MSSQLSERVER' -Force

• This clears any cached compatibility flags held in the Resource Database.

Alternative or Verification Steps

• Verify the fix by re-querying sys.databases to confirm is_dw_compat_mode = 0 and re-running the ALTER DATABASE ... SET OVERFLOW = ON statement without error.
• If the error persists, drop and recreate the filegroup without the OVERFLOW clause first, then apply it in a separate statement after confirming the database is fully out of DW compatibility mode.

Troubleshooting Guide: Error 5152 – “This Operation Is Not Supported for URL”

Why This Error Happens

Error 5152 occurs when SQL Server 2025 attempts to perform a BACKUP or RESTORE operation against a URL (Azure Blob Storage) target using an unsupported method, such as attempting to append to an existing backup file or using WITH FORMAT on a URL destination. This typically stems from a mismatch between the backup command syntax and the restrictions imposed by URL-based backups, which do not support all the legacy options available for local disk or network share backups. It can also occur due to an outdated or misconfigured SQL Credential tied to the storage account, causing SQL Server to misinterpret the operation type being requested against the blob endpoint.

Step-by-Step Resolution Guide

Step 1: Review and Correct the T-SQL Backup/Restore Syntax

• Confirm your script does not use WITH APPEND, WITH FORMAT, or SKIP options, as these are explicitly unsupported for URL backup destinations.
• Ensure every backup to a URL uses the WITH FORMAT, INIT only on the first backup to a blob, and subsequent backups use a new, uniquely named file rather than appending to an existing one.
• Example of corrected syntax:

  BACKUP DATABASE [YourDB] 
  TO URL = 'https://youraccount.blob.core.windows.net/container/YourDB_20250615.bak'
  WITH CREDENTIAL = 'YourCredentialName', COMPRESSION, STATS = 10;

Step 2: Validate the SQL Server Credential and Shared Access Signature (SAS)

• Navigate to Object Explorer > Security > Credentials in SQL Server Management Studio (SSMS) 21.
• Verify the credential mapped to your storage container uses a valid, non-expired Shared Access Signature (SAS) token or Storage Account Key.
• Re-create the credential if necessary using the following syntax:

  CREATE CREDENTIAL [https://youraccount.blob.core.windows.net/container]
  WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = 'sv=2025-01-01&ss=b&srt=sco...';

• Ensure the SAS token has Read, Write, Create, and List permissions enabled on the Storage Account.

Step 3: Check Blob Storage Tier and File Existence

• Verify that the target blob container is set to Hot or Cool access tier; Archive tier blobs will throw unsupported operation errors because they cannot be written to directly.
• In the Azure Portal, check if a blob with the exact same name already exists in the container. If it does, delete it or rename your target file, since SQL Server cannot overwrite a page blob with a block blob (or vice versa).
• Confirm the blob storage account has not been configured with immutability policies or WORM (Write Once, Read Many) locks that restrict overwrite operations.

Step 4: Update SQL Server 2025 Cumulative Update (CU)

• Open SQL Server Configuration Manager and verify the current build number via SELECT @@VERSION;.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early builds of SQL Server 2025 had a known bug mishandling URL backup validation logic.
• Restart the SQL Server (MSSQLSERVER) service via Windows Services (services.msc) after patching.

Alternative or Verification Steps

• Run a test backup to a local disk path (e.g., C:\Temp\test.bak) to confirm the database and syntax are otherwise healthy; if the local backup succeeds, the issue is isolated to the URL/credential configuration.
• As an alternative, use Azure Storage Explorer to manually verify connectivity and write permissions to the target container using the same SAS token configured in the SQL Credential.

Troubleshooting Guide: SQL Server Error 5150 — “The size of a single log file must not be greater than 2 TB”

Why This Error Happens

This error occurs because SQL Server enforces a hard architectural limit of 2 TB on the maximum size of a single transaction log file (VLF container), regardless of available disk space. It is typically triggered when auto-growth settings, a manually specified file size, or FILEGROWTH/MAXSIZE configurations attempt to expand or create a log file (.ldf) beyond this ceiling. This commonly happens on databases with heavy transactional activity, unchecked log growth due to infrequent log backups, or during a restore/create operation where the size parameter was set incorrectly.

Step-by-Step Resolution Guide

Step 1: Verify the Current Log File Size and Database Settings

• Open SQL Server Management Studio (SSMS) and connect to the SQL Server 2025 instance.
• Run the following query to check current log file sizes and growth settings:

SELECT name, size/128.0 AS CurrentSizeMB, max_size, growth, is_percent_growth 
FROM sys.master_files 
WHERE database_id = DB_ID('YourDatabaseName');

• Identify if max_size is set to -1 (unlimited) or if the current size is already approaching 2,097,152 MB (2 TB).

Step 2: Implement Log File Management (Instead of Infinite Growth)

• Since a single log file cannot exceed 2 TB, you cannot simply “add more space” to the same file. Instead, break the log backup chain dependency by managing VLF growth.
• Switch the database recovery model temporarily to Simple (if business logic allows) to truncate the log, or ensure transaction log backups are running frequently to prevent unchecked growth:

BACKUP LOG [YourDatabaseName] TO DISK = 'E:\Backups\YourDatabaseName_Log.trn';

• Shrink the log file after backup to reclaim internal space without needing to exceed the 2 TB physical limit:

DBCC SHRINKFILE (YourDatabaseName_Log, 500000);

Step 3: Add a Secondary Log File (If Capacity is Still Required)

• If the business requirement genuinely needs more than 2 TB of total log capacity, you cannot extend file #1; you must add a second log file to the same filegroup.
• Navigate to the data drive path (e.g., F:\SQLData\Logs\) and ensure there is sufficient disk space for a new file.
• Execute the following T-SQL to add a secondary log container:

ALTER DATABASE [YourDatabaseName] 
ADD LOG FILE 
(
    NAME = YourDatabaseName_Log2, 
    FILENAME = 'F:\SQLData\Logs\YourDatabaseName_Log2.ldf', 
    SIZE = 500GB, 
    FILEGROWTH = 500MB
);

Step 4: Audit and Correct Auto-Growth Settings

• To prevent future triggers of Error 5150, change the auto-growth setting from a “percentage” growth to a “fixed MB” growth to avoid erratic spikes.
• In SSMS, right-click the Database -> Properties -> Files -> Click the (…) button next to the Log file.
• Set Autogrowth/Maxsize to a fixed value (e.g., Growth: 512MB, Maximum File Size: 1,900,000 MB) to leave a safety buffer below the 2 TB hard limit.

Alternative or Verification Steps

• Verification: Re-run the sys.master_files query from Step 1. Confirm that max_size is now capped below the 2TB limit (2,097,152 MB) and that the state_desc for the file shows ONLINE.
• Alternative: If the log continues to grow uncontrollably even after backups, check for orphaned replication or Change Data Capture (CDC) jobs that might be preventing the log from truncating, as these can cause rapid consumption toward the 2 TB ceiling even with regular backups.

Troubleshooting Guide: Error 5149 – MODIFY FILE Operating System Error During File Expansion

Why This Error Happens

Error 5149 occurs when SQL Server 2025 attempts to grow a database file (via ALTER DATABASE ... MODIFY FILE or autogrowth) but the underlying Windows operating system rejects the request. This is typically caused by insufficient disk space on the target volume, the SQL Server service account lacking write/modify permissions on the file or folder, or the physical disk/LUN being offline, disconnected, or corrupted. In virtualized or cloud-hosted instances of Windows Server 2025, this can also stem from a thin-provisioned disk hitting its underlying storage limit despite appearing to have free space in Windows.

Step-by-Step Resolution Guide

Step 1: Verify Available Disk Space

• Open File Explorer or run Get-PSDrive -PSProvider FileSystem in PowerShell to check free space on the volume hosting the .mdf/.ndf/.ldf file.
• If using thin-provisioned storage (SAN/VM), confirm with your storage/virtualization admin that the underlying physical pool isn’t exhausted.
• Clear unnecessary files or extend the volume using Disk Management (diskmgmt.msc) if space is low.

Step 2: Confirm File and Folder Permissions

• Locate the SQL Server service account via SQL Server Configuration Manager under SQL Server Services.
• Navigate to the data file’s parent directory (e.g., D:\SQLData\), right-click → Properties → Security.
• Ensure the service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Full Control or at minimum Modify permissions.
• Apply changes and restart the SQL Server service if permissions were altered.

Step 3: Check Disk Health and Connectivity

• Run Get-Disk and Get-Volume in PowerShell to confirm the disk is Online and Healthy.
• For SAN-attached or iSCSI storage, verify the connection hasn’t dropped using Get-IscsiConnection or your vendor’s management console.
• Check Windows Event Viewer under System logs for Disk or Ntfs warnings (Event IDs 7, 51, or 153) that indicate underlying hardware issues.

Step 4: Validate File System Integrity

• Run chkdsk D: /f (replace D: with the actual drive letter) during a maintenance window, as this may require a reboot if the volume is in use.
• Review the CHKDSK output for bad sectors or file system corruption that could prevent file extension operations.

Step 5: Manually Test File Growth

• In SQL Server Management Studio (SSMS), run a manual ALTER DATABASE [YourDB] MODIFY FILE (NAME = 'YourLogicalFileName', SIZE = XXXMB) command with a conservative size increase.
• Check the SQL Server Error Log (EXEC xp_readerrorlog) immediately after to confirm whether error 5149 recurs with more specific OS error details (e.g., Error 112 = disk full).

Alternative or Verification Steps

• After applying fixes, force an autogrowth event by inserting bulk test data or running DBCC SHRINKFILE followed by growth, then monitor the SQL Error Log for successful completion without Error 5149.
• If the issue persists on shared/virtual storage, consider moving the database files to a dedicated local SSD volume or a different storage pool to rule out contention or quota-based restrictions.

Troubleshooting Guide: SQL Server Error 5145 – Autogrow Duration Exceeded

Why This Error Happens

This informational warning occurs when SQL Server 2025 has to automatically expand a data or log file, and that operation takes an unusually long time to complete, blocking active transactions in the process. The root cause is typically a large FILEGROWTH increment (either a large fixed MB value or a percentage-based growth on an already large file) combined with slow underlying disk I/O. On Windows Server 2025, this is frequently compounded when Instant File Initialization (IFI) is not enabled for data files, forcing SQL Server to zero-initialize every new byte of disk space before it can be used.

Step-by-Step Resolution Guide

Step 1: Enable Instant File Initialization (IFI)

• Open the Local Security Policy editor (secpol.msc) on the Windows Server 2025 host.
• Navigate to Local Policies > User Rights Assignment and locate “Perform volume maintenance tasks.”
• Add the SQL Server service account (e.g., NT Service\MSSQLSERVER or your custom domain service account) to this policy.
• Restart the SQL Server service for the change to take effect; note that this only benefits data files (.mdf/.ndf), not log files (.ldf).

Step 2: Adjust Autogrowth Settings via ALTER DATABASE

• Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
• Run a query to switch from percentage-based growth to a fixed, smaller MB increment:

  ALTER DATABASE [YourDatabaseName]
  MODIFY FILE (NAME = N'YourLogicalFileName', FILEGROWTH = 256MB);

• Avoid using a growth increment larger than 1GB for data files unless storage specifically supports high-throughput writes.

Step 3: Proactively Pre-Size Database Files

• Calculate the expected database size for the next 6–12 months based on current growth trends.
• Use ALTER DATABASE... MODIFY FILE (SIZE = ...) to manually expand the file during a maintenance window (off-peak hours) rather than relying on an unexpected autogrow event during production traffic.
• This moves the “pain” of file expansion to a scheduled time rather than an unpredictable, high-traffic moment.

Step 4: Audit Underlying Storage Performance

• Use Windows Performance Monitor (PerfMon) to check the Avg. Disk sec/Write counter on the volume hosting the .mdf/.ldf files.
• If latency consistently exceeds 15-20ms, the physical storage (SAN/vDisk) may be the bottleneck rather than the SQL configuration itself.

Alternative or Verification Steps

• Verify the Fix: Check the SQL Server Error Log or query sys.dm_io_virtual_file_stats after the next natural growth event to confirm the duration (in milliseconds) has significantly decreased.
• Alternative: If IFI cannot be enabled due to security compliance policies (common in high-security government or finance environments), mitigate the impact by scheduling manual off-hours growth via a SQL Agent Job to avoid triggering the event during business hours.

Troubleshooting Guide: SQL Server Error 5144 – Autogrow Cancelled or Timed Out

Why This Error Happens

This error occurs when SQL Server attempts to automatically grow a database file, but the operation is cancelled by a user action (such as killing the session or query timeout) or exceeds the internal autogrow timeout threshold. The root cause is typically a large FILEGROWTH increment combined with slow disk I/O, insufficient disk space, or Instant File Initialization (IFI) not being enabled for data files, causing the zero-initialization process to take too long. In some cases, concurrent autogrow requests on the same file or Transparent Data Encryption (TDE) can also extend the growth duration beyond the timeout window.

Step-by-Step Resolution Guide

Step 1: Verify Disk Space and Storage Health

• Check the underlying volume (e.g., D:\SQLData\) using Get-PSDrive in PowerShell to confirm sufficient free space exists for the requested growth.
• Review Windows Event Viewer under Windows Logs > System for disk latency or hardware warnings (Event ID 153 or 129) that indicate slow I/O.
• If using SAN/NAS storage on Windows Server 2025, confirm multipathing (MPIO) is configured correctly and not causing intermittent timeouts.

Step 2: Enable Instant File Initialization (IFI)

• Open Local Security Policy (secpol.msc) and navigate to Local Policies > User Rights Assignment.
• Grant “Perform Volume Maintenance Tasks” permission to the SQL Server service account.
• Restart the SQL Server service via SQL Server Configuration Manager for the change to take effect.
• Confirm IFI is active by running:

  SELECT instant_file_initialization_enabled FROM sys.dm_server_services;

Step 3: Adjust FILEGROWTH and Initial File Size

• Open SQL Server Management Studio (SSMS) and connect to the affected instance.
• Run the following command to set a smaller, fixed-size growth increment instead of a percentage-based one:

  ALTER DATABASE [YourDatabaseName]
  MODIFY FILE (NAME = N'YourLogicalFileName', FILEGROWTH = 256MB);

• Pre-size the data and log files to their expected production size to minimize the frequency of autogrow events:

  ALTER DATABASE [YourDatabaseName]
  MODIFY FILE (NAME = N'YourLogicalFileName', SIZE = 20480MB);

Step 4: Review and Increase Timeout Thresholds (If Applicable)

• Check if any monitoring tools, load balancers, or application connection strings have aggressive command timeout settings that may be prematurely cancelling the growth operation.
• Adjust the Connection Timeout and Command Timeout values in your application’s connection string (e.g., increase from 30 to 120 seconds) to allow large growth operations to complete.

Alternative or Verification Steps

• To verify the fix, monitor the SQL Server Error Log (via EXEC sp_readerrorlog) for subsequent autogrow events and confirm they complete without triggering Error 5144 again.
• If the error persists despite these changes, consider implementing a maintenance job that manually pre-grows files during off-peak hours using ALTER DATABASE ... MODIFY FILE to avoid relying on autogrow during high-traffic periods entirely.

Troubleshooting Guide: SQL Server Error 5143 – Concurrent Operation Failed on File

Why This Error Happens

Error 5143 occurs when SQL Server 2025 attempts a concurrent I/O operation (such as sparse file allocation, instant file initialization, or snapshot operations) on a database file, but the underlying Windows I/O subsystem rejects the request. This is typically caused by the SQL Server service account lacking proper NTFS permissions on the file/volume, antivirus or backup software locking the file during the operation, or the underlying storage (especially on ReFS/Storage Spaces Direct volumes) not supporting the specific sparse/concurrent I/O flag being used. In some cases, this is also triggered by disk corruption or a mismatch between the file system block size and SQL Server’s expected sector alignment.

Step-by-Step Resolution Guide

Step 1: Verify File and Folder Permissions

• Open File Explorer and navigate to the SQL Server data directory (e.g., D:\SQLData\MSSQL16.MSSQLSERVER\MSSQL\DATA).
• Right-click the .mdf/.ndf/.ldf file in question, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Alternatively, run this PowerShell command to grant permissions:

  icacls "D:\SQLData\MSSQL16.MSSQLSERVER\MSSQL\DATA" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T

Step 2: Exclude SQL Server Files from Antivirus/Backup Scanning

• Open your antivirus console (e.g., Windows Defender) and add exclusions for the SQL data, log, and tempdb directories.
• For Windows Defender via PowerShell:

  Add-MpPreference -ExclusionPath "D:\SQLData\MSSQL16.MSSQLSERVER\MSSQL\DATA"
  Add-MpPreference -ExclusionExtension ".mdf",".ldf",".ndf"

• Temporarily pause any third-party backup agents (e.g., Veeam, Commvault) and retry the operation to rule out file-locking conflicts.

Step 3: Check Disk Health and File System Compatibility

• Run chkdsk D: /f /r on the volume hosting the database files (requires a reboot if the volume is in use).
• If using ReFS, confirm it is formatted with 64K cluster size, as SQL Server 2025 strict-alignment checks may conflict with default 4K cluster ReFS volumes.
• Review the Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > Storage for correlating disk-level I/O errors.

Step 4: Update Storage Drivers and SQL Server Cumulative Update

• Open Device Manager, locate your storage controller, and update to the latest manufacturer driver.
• Install the latest SQL Server 2025 Cumulative Update (CU) from Microsoft, as several concurrent I/O fixes for Windows Server 2025 storage stacks are addressed in CU patches.
• Restart the SQL Server service after installation: Restart-Service MSSQLSERVER.

Alternative or Verification Steps

• Re-run the operation that triggered the error (e.g., database snapshot, backup, or file growth) and confirm it completes without Error 5143 in the SQL Server error log (ERRORLOG file in the LOG directory).
• If the issue persists, disable Instant File Initialization temporarily (remove SE_MANAGE_VOLUME_NAME privilege from the service account) to test whether the error is specifically tied to sparse file handling, then re-enable once the storage/driver issue is resolved.

Troubleshooting Guide: SQL Server Error 5142 – Failed to Lock Lease Renewal Manager

Why This Error Happens

Error 5142 occurs when SQL Server’s internal Lease Renewal Manager—a component responsible for maintaining cluster or Availability Group health signaling—cannot acquire the required lock (Shared or Exclusive) within the expected timeframe. This typically happens due to resource contention from a high volume of concurrent lease renewal threads, latch starvation caused by CPU scheduling pressure, or a stalled I/O subsystem preventing the lease state from being updated. In clustered or Always On configurations, this can also stem from the lease renewal thread being blocked by another process holding the lock for an abnormally long duration.

Step-by-Step Resolution Guide

Step 1: Verify Cluster and SQL Server Service Health

• Open Failover Cluster Manager and confirm the SQL Server resource group is online and not in a “Failed” or “Pending” state.
• Check the Windows Event Viewer under Applications and Services Logs > Microsoft > Windows > FailoverClustering for correlating lease timeout warnings.
• Run Get-ClusterResource | Format-List * in PowerShell (elevated) to inspect resource status and LastTransitionTime.

Step 2: Check for CPU and I/O Resource Starvation

• Open Performance Monitor (PerfMon) and review counters for SQLServer:Wait Statistics and Processor: % Processor Time.
• Identify if PAGEIOLATCH or THREADPOOL wait types are elevated, indicating the lease renewal thread is being starved.
• If running on a VM, confirm host-level CPU ready time isn’t excessive using Hyper-V Manager or VMware performance tabs.

Step 3: Increase Lease Timeout Threshold (If Applicable)

• Navigate to SQL Server Configuration Manager > SQL Server Services.
• Right-click the SQL Server (MSSQLSERVER) instance, go to Properties > Startup Parameters.
• Review Always On Availability Group lease timeout settings via:

  ALTER AVAILABILITY GROUP [YourAGName] SET (LEASE_TIMEOUT = 40);

• Restart the SQL Server service for changes to take effect: Restart-Service MSSQLSERVER.

Step 4: Repair Corrupted System Files or SQL Binaries

• Run sfc /scannow in an elevated Command Prompt on Windows Server 2025 to rule out OS-level file corruption affecting SQL Server’s lease threads.
• If corruption is suspected in SQL binaries, repair the instance via Control Panel > Programs > Microsoft SQL Server 2025 > Uninstall/Change > Repair.

Step 5: Review and Clear Blocking Processes

• Query active locks using:

  SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'DATABASE';

• Identify any long-running transaction holding locks that may be delaying the lease renewal manager and terminate it with KILL [session_id] if safe to do so.

Alternative or Verification Steps

• After applying fixes, monitor the SQL Server Error Log (ERRORLOG file located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\LOG\) for 24–48 hours to confirm Error 5142 does not recur.
• If the issue persists, failover the Availability Group to a secondary replica (ALTER AVAILABILITY GROUP [YourAGName] FAILOVER;) to isolate whether the problem is node-specific or shared across the cluster.

Troubleshooting Guide: SQL Server Error 5141 — Failed to Lock Credential Manager

Why This Error Happens

Error 5141 occurs when SQL Server 2025 is unable to acquire an internal lock on the Credential Manager subsystem, which governs access to database-scoped credentials, encryption keys, and external resource mappings. This typically happens due to a concurrency conflict where another session, DDL operation, or background task (such as a backup or key rotation process) is already holding the lock. It can also result from a corrupted master database metadata entry or insufficient permissions on the service account used to manage Credential Manager objects.

Step-by-Step Resolution Guide

Step 1: Identify Blocking Sessions and Processes

• Run sp_who2 or query sys.dm_exec_requests to check for sessions blocking credential-related operations.
• Look specifically for wait types such as CREDENTIAL_MANAGER_LOCK or similar internal resource waits in sys.dm_os_wait_stats.
• Terminate any long-running or orphaned sessions using KILL <session_id> if they are confirmed safe to end.

Step 2: Verify Service Account Permissions

• Confirm the SQL Server service account has appropriate rights on the master database and local machine key store.
• Check that the account has CONTROL SERVER or at minimum ALTER ANY CREDENTIAL permission:

  GRANT ALTER ANY CREDENTIAL TO [DOMAIN\ServiceAccount];

• Validate that Windows Credential Manager (on Windows Server 2025 / Windows 11) isn’t being restricted by Group Policy or antivirus software intercepting credential store access.

Step 3: Restart SQL Server Services Cleanly

• Stop all dependent SQL Server services (SQL Server Agent, Reporting Services) before restarting the main engine.
• Use PowerShell to restart cleanly:

  Restart-Service -Name 'MSSQLSERVER' -Force

• This clears any stale in-memory locks tied to the Credential Manager subsystem.

Step 4: Rebuild or Repair Corrupted Credential Metadata

• If the issue persists, script out all existing credentials using sys.credentials for backup purposes.
• Drop and recreate the affected credential:

  DROP CREDENTIAL [CredentialName];
  CREATE CREDENTIAL [CredentialName] WITH IDENTITY = 'IdentityName', SECRET = 'StrongSecretValue';

• This resolves cases where metadata corruption is preventing the lock from releasing properly.

Alternative or Verification Steps

• After applying fixes, re-run the operation that originally triggered Error 5141 and confirm no entry appears in the SQL Server error log (Log_X.trc or Event Viewer under Applications and Services Logs > MSSQLSERVER).
• If the error recurs intermittently, consider enabling Trace Flag 1222 temporarily to capture deadlock/lock contention details for deeper root-cause analysis.

Troubleshooting Guide: SQL Server Error 5138

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create, rename, or reference a database file on cloud storage (such as Azure Blob Storage or an S3-compatible endpoint) using a file name that contains one or more trailing space characters. Cloud storage APIs typically normalize or strip trailing whitespace from object keys, which creates a mismatch between the name SQL Server expects and the name actually stored by the cloud provider. This validation was introduced specifically for cloud-backed file streams because local NTFS volumes tolerate trailing spaces in file names, but remote object storage does not guarantee consistent round-trip behavior for them.

Step-by-Step Resolution Guide

Step 1: Identify the Offending File Name

• Check the full error message text for the exact file name referenced in the ‘%.*ls’ placeholder.
• Run the following query to inspect current database file names and look for unintended trailing characters:

  SELECT name, physical_name, LEN(name) AS name_len, LEN(RTRIM(name)) AS trimmed_len
  FROM sys.master_files;

• Compare name_len and trimmed_len — a mismatch confirms a trailing space is present.

Step 2: Correct the File Name in the T-SQL Script or Application Code

• Open the script, ETL job, ORM mapping, or application config that generated the CREATE DATABASE, ALTER DATABASE, or BACKUP/RESTORE statement.
• Remove any trailing whitespace from the logical or physical file name string, especially in concatenated variables (e.g., @FileName + ' ').
• Use TRIM() or RTRIM() explicitly when building dynamic SQL file paths:

  SET @FileName = RTRIM(@FileName);

• Re-run the operation using the corrected name against the cloud storage container (Azure Blob, S3, etc.).

Step 3: Validate Cloud Storage Credential and Endpoint Configuration

• Confirm the CREATE CREDENTIAL or DATABASE SCOPED CREDENTIAL object used for cloud storage access does not itself contain a padded secret or URL with trailing spaces.
• Check sys.external_data_sources (if using PolyBase/External Tables) or the BACKUP URL syntax for extra spaces copy-pasted from documentation or spreadsheets.
• On Windows Server 2025, verify any automation (PowerShell scripts, scheduled tasks, or SQL Agent jobs) that builds file name strings isn’t appending a space via string formatting, such as "$fileName " in PowerShell.

Step 4: Re-test the Operation

• Execute the original CREATE DATABASE ... ON (NAME = ..., FILENAME = 'https://<account>.blob.core.windows.net/container/file.mdf') command with the cleaned file name.
• Confirm the operation completes without raising Error 5138.

Alternative or Verification Steps

• Re-run DBCC CHECKDB or query sys.master_files / sys.database_files again to confirm all file names report equal LEN() and LEN(RTRIM()) values, indicating no trailing spaces remain.
• If the error persists despite a clean name, test the same file name against the cloud storage container directly (e.g., using Azure Storage Explorer or AWS CLI ls) to rule out a provider-side naming restriction or hidden Unicode whitespace character (such as a non-breaking space) that RTRIM() alone may not remove — in that case use TRANSLATE() or REPLACE() to strip the specific character code.

Troubleshooting Guide: SQL Server Error 5139

Why This Error Happens

Error 5139 occurs when SQL Server attempts to perform a file operation (such as creating, attaching, or growing a database file) and the underlying Windows operating system rejects the request. This is most commonly caused by insufficient NTFS permissions for the SQL Server service account, a file path that no longer exists or is misspelled, or the target file/disk being locked, offline, or read-only. In clustered or storage-replica environments, this can also surface when the disk volume hosting the data file has failed over or lost connectivity.

Step-by-Step Resolution Guide

Step 1: Verify the File Path and Disk Availability

• Open File Explorer or run dir from Command Prompt to confirm the file path referenced in the error (e.g., D:\SQLData\MyDB.mdf) actually exists.
• Check Disk Management (diskmgmt.msc) to ensure the volume is online, not read-only, and has sufficient free space.
• If using mount points or SAN storage, confirm the LUN is attached and visible to Windows Server 2025.

Step 2: Correct NTFS and Service Account Permissions

• Open the properties of the folder containing the database files, go to the Security tab, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Full Control.
• Use PowerShell to quickly audit permissions:

  Get-Acl "D:\SQLData" | Format-List

• If permissions are missing, grant access via:

  icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" /T

Step 3: Check for File Locks or Conflicting Processes

• Use Resource Monitor (resmon.exe) or handle.exe (Sysinternals) to confirm no other process (antivirus, backup agent, or another SQL instance) has the file locked.
• Temporarily disable real-time antivirus scanning on the SQL data/log directories to rule out interference.
• Restart the SQL Server service after confirming no locks exist:

  net stop MSSQLSERVER
  net start MSSQLSERVER

Step 4: Review Windows Event Viewer for the Underlying OS Error Code

• Open Event Viewer > Windows Logs > System and Application to find the specific Windows error number referenced in the SQL error message (e.g., Error 5 = Access Denied, Error 32 = Sharing Violation).
• Cross-reference the OS error code with Microsoft’s documentation to pinpoint whether it’s a permissions, locking, or hardware-level issue.
• Resolve based on the specific code (e.g., re-grant permissions for Error 5, release locks for Error 32).

Alternative or Verification Steps

• After applying fixes, attempt the original operation again (e.g., CREATE DATABASE, ALTER DATABASE ... MODIFY FILE, or restore) and confirm it completes without Error 5139.
• If the issue persists, run DBCC CHECKDB and review the SQL Server Error Log (sqlerrlog) for additional context, or move the database files to a different, verified-healthy volume as a workaround.

Troubleshooting Guide: SQL Server Error 5140 – “Failed to lock credential object”

Why This Error Happens

This error typically occurs when SQL Server 2025 cannot acquire an internal lock on a credential object, often due to concurrent access attempts from multiple sessions trying to modify or use the same credential simultaneously. It can also stem from a corrupted credential entry in the system catalog (sys.credentials) or insufficient permissions on the Windows Credential Manager/DPAPI store used by the SQL Server service account. In some cases, this is triggered when a Managed Identity or proxy account credential becomes orphaned after a service account password change or domain policy update.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Credential and Active Sessions

• Run SELECT * FROM sys.credentials; to list all credential objects and locate the one referenced in the error (the %.*ls placeholder in the message).
• Query sys.dm_tran_locks to check for blocking sessions holding locks on credential-related resources.
• Use KILL <session_id> cautiously if a stale session is holding the lock indefinitely.

Step 2: Restart the SQL Server Service to Clear Stale Locks

• Open Services.msc on Windows Server 2025 and locate SQL Server (MSSQLSERVER) or the named instance.
• Right-click and select Restart (ensure this is done during a maintenance window).
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
• This clears any in-memory lock table corruption tied to the credential object.

Step 3: Drop and Recreate the Corrupted Credential

• Script out the existing credential identity using sp_helpcredential before deletion.
• Drop the credential: DROP CREDENTIAL [CredentialName];
• Recreate it with the correct identity and secret:
  CREATE CREDENTIAL [CredentialName] WITH IDENTITY = 'DomainOrAccount', SECRET = 'StrongPassword';
• Re-link the credential to any associated logins using ALTER LOGIN [LoginName] WITH CREDENTIAL = [CredentialName];

Step 4: Verify Windows-Level Permissions and DPAPI Access

• Open Local Security Policy (secpol.msc) and confirm the SQL Server service account has the “Log on as a service” right.
• Check that the service account profile under C:\Users\<ServiceAccount>\AppData\Roaming\Microsoft\Protect is not corrupted or missing (DPAPI master key storage).
• If using Group Managed Service Accounts (gMSA), run Test-ADServiceAccount <gMSAName> in PowerShell to confirm connectivity to Active Directory.

Alternative or Verification Steps

• To verify the fix, re-run the operation that triggered Error 5140 (e.g., executing a job or query referencing the credential) and check the SQL Server Error Log for recurrence.
• As an alternative, if the issue persists, restore the master database from a known good backup taken before the credential corruption occurred, since credential objects are stored at the instance level in master.

Troubleshooting Guide: SQL Server Error 5135 – FILESTREAM Path Cannot Be Used

Why This Error Happens

Error 5135 occurs when SQL Server 2025 attempts to create a FILESTREAM-enabled filegroup or database at a directory path that violates FILESTREAM’s structural requirements. This typically happens when the target path already exists as a non-empty directory, resides on a non-NTFS/ReFS volume, or the SQL Server service account lacks the necessary NTFS permissions to create and control the folder structure at that location. Additionally, the path may be invalid because it points to a root drive, a network share without proper configuration, or a location where the parent directory itself does not exist yet.

Step-by-Step Resolution Guide

Step 1: Verify FILESTREAM is Enabled at the Instance Level

• Open SQL Server Configuration Manager on Windows Server 2025/Windows 11.
• Navigate to SQL Server Services, right-click your instance, select Properties, then the FILESTREAM tab.
• Ensure “Enable FILESTREAM for Transact-SQL access” and “Enable FILESTREAM for file I/O streaming access” are checked.
• Confirm via T-SQL: run EXEC sp_configure 'filestream access level' and ensure the value is 2.

Step 2: Validate the Target Directory Path

• Ensure the specified path (e.g., C:\SQLData\FileStreamData) does not already exist as a folder — SQL Server must create this final directory itself.
• Confirm the parent directory (e.g., C:\SQLData\) exists and is on an NTFS or ReFS formatted volume.
• Avoid using root paths like C:\ or D:\ directly; always use a subfolder.
• Remove any trailing spaces or invalid characters from the path string in your CREATE DATABASE or ALTER DATABASE statement.

Step 3: Correct NTFS Permissions for the SQL Server Service Account

• Open File Explorer, right-click the parent folder (e.g., C:\SQLData\), go to Properties > Security.
• Add the SQL Server service account (e.g., NT SERVICE\MSSQLSERVER or your custom domain service account) with Full Control permissions.
• Apply changes recursively and restart the SQL Server service to refresh permission tokens.

Step 4: Re-run the FILESTREAM Filegroup Creation Script

• Execute a clean ALTER DATABASE command specifying the corrected path:

ALTER DATABASE YourDB
ADD FILE (NAME = FSGroup1, FILENAME = 'C:\SQLData\FileStreamData')
TO FILEGROUP FileStreamGroup;

• Confirm no error 5135 is returned and the folder is auto-created by SQL Server.

Alternative or Verification Steps

• Verify success by checking that the new FILESTREAM folder now contains a filestream.hdr file, confirming SQL Server has initialized the container correctly.
• If the error persists, test using a local disk path instead of a mapped network drive or UNC path, as FILESTREAM has stricter validation against remote storage without explicit SMB 3.0 continuous availability configuration.

Why This Error Happens

Error 5136 occurs when SQL Server attempts to create or attach a FILESTREAM filegroup container at a directory path that is already nested inside—or is the parent of—an existing FILESTREAM container. SQL Server enforces a strict rule that each FILESTREAM container must occupy its own isolated directory structure, because the filestream.hdr header file and garbage-collection logic cannot safely manage overlapping directory trees. This typically happens when a DBA reuses a parent folder for multiple databases or accidentally points a new FILEGROUP at a subdirectory of an already-provisioned container.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Directory Structure

• Open SQL Server Management Studio (SSMS) and run sys.database_files to list current FILESTREAM paths for all databases on the instance.
• Check the physical folder structure in Windows Explorer (e.g., D:\SQLData\FileStreamData\) to see if the new path you specified is a child or parent of an existing container folder.
• Look for a hidden filestream.hdr file in the suspected parent folder — its presence confirms that folder is already a container.

Step 2: Choose a Fully Isolated Path

• Create a brand-new, empty directory for the new FILESTREAM container, ensuring it is not nested inside or above any existing container (e.g., use D:\SQLData\FS_Container2\ instead of D:\SQLData\FileStreamData\Sub1\).
• Confirm the new folder does not already exist prior to running the CREATE DATABASE or ALTER DATABASE ... ADD FILE statement — SQL Server must create the final directory itself.
• Example corrected syntax:

ALTER DATABASE MyDB
ADD FILE (NAME = FS_Data2, FILENAME = 'D:\SQLData\FS_Container2')
TO FILEGROUP FileStreamGroup2;

Step 3: Validate NTFS Permissions and Folder Ownership

• Ensure the SQL Server service account has Full Control NTFS permissions on the new parent drive/folder (right-click folder → Properties → Security tab).
• Remove any leftover orphaned FILESTREAM folders from failed previous attempts using rmdir /s in an elevated Command Prompt, provided they are not currently referenced by any active database.

Step 4: Restart the FILESTREAM Configuration if Needed

• Open SQL Server Configuration Manager → right-click the SQL Server instance → Properties → FILESTREAM tab, and confirm “Enable FILESTREAM for Transact-SQL access” is checked.
• Restart the SQL Server service after any configuration changes to ensure the new path registers cleanly.

Alternative or Verification Steps

• Verify the fix by querying sys.master_files and confirming the new FILESTREAM path shows state_desc = ONLINE with no errors in the SQL Server Error Log.
• If the error persists, drop and recreate the FILEGROUP entirely using a completely new drive letter or root-level folder to eliminate any residual directory nesting conflicts.

Troubleshooting Guide: Error 5137 — Snapshot and Base Database Storage Mismatch

Why This Error Happens

Error 5137 occurs because SQL Server requires that a database snapshot’s sparse files reside on the exact same storage volume type as the base database’s data files. This typically happens when the base database uses a specific storage configuration (such as a ReFS/NTFS volume, a Storage Spaces Direct volume, or a mounted VHD) while the snapshot creation command points to a different or incompatible storage medium (like a network share, a different tiered storage pool, or a local disk when the base is on cluster shared storage). SQL Server 2025 enforces this check strictly to prevent I/O inconsistencies and performance degradation between the copy-on-write sparse file and its source data file.

Step-by-Step Resolution Guide

Step 1: Identify the Storage Type of the Base Database

• Open SQL Server Management Studio (SSMS) and run sp_helpfile against the base database to list all physical file paths.
• Check the underlying volume properties using PowerShell: Get-Volume -DriveLetter <X> to confirm if it is NTFS, ReFS, or a mapped network path.
• Note whether the base database resides on local SSD/HDD, a SAN LUN, or Storage Spaces Direct (S2D).

Step 2: Match the Snapshot File Path to the Same Storage Type

• Rewrite your CREATE DATABASE ... AS SNAPSHOT OF T-SQL statement to point the snapshot’s file path to a directory on the identical volume as the base database file.
• Example correction:

  CREATE DATABASE SalesDB_Snapshot ON
  ( NAME = SalesDB_Data, FILENAME = 'D:\SQLData\SalesDB_Data.ss' )
  AS SNAPSHOT OF SalesDB;

• Ensure the drive letter D: matches the physical volume type (e.g., NTFS) confirmed in Step 1, not a UNC path or a different disk tier.

Step 3: Verify Windows Server 2025 Storage Tiering Settings

• If using Storage Spaces Direct, run Get-StoragePool and Get-PhysicalDisk in PowerShell to confirm both the base and intended snapshot location belong to the same storage pool and resiliency tier.
• Disable any automatic Storage Tiering policies that might silently move snapshot files to a secondary (e.g., HDD capacity) tier while the base remains on the performance (SSD) tier.

Step 4: Restart the SQL Server Service and Retry

• Restart the SQL Server (MSSQLSERVER) service via services.msc or run: Restart-Service -Name 'MSSQLSERVER' -Force.
• Re-execute the CREATE DATABASE AS SNAPSHOT command to confirm the error no longer appears.

Alternative or Verification Steps

• Verify the fix by querying sys.master_files for both the base and snapshot databases, confirming the physical_name paths resolve to volumes with identical FileSystemType values (checked via fsutil fsinfo volumeinfo <path>).
• As an alternative, if mixed storage is unavoidable, use Storage Replica or move the entire base database to a unified storage volume before attempting the snapshot operation again.

Troubleshooting Guide: SQL Server Error 5133

Why This Error Happens

Error 5133 occurs when SQL Server 2025 cannot resolve the directory path specified for a database file during creation, attachment, or restore operations. This is typically caused by the target folder not existing on disk, the SQL Server service account lacking NTFS permissions on the path, or the path containing an invalid drive letter/UNC reference. In some cases, this also surfaces when antivirus or endpoint protection software is locking the target directory before SQL Server can validate it.

Step-by-Step Resolution Guide

Step 1: Verify the Target Directory Exists

• Open File Explorer or Command Prompt on the Windows Server 2025 / Windows 11 host and confirm the exact path referenced in the error (e.g., D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• If the folder is missing, create it manually using mkdir "D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA".
• Double-check for typos in the CREATE DATABASE, ATTACH, or RESTORE statement, especially trailing slashes or incorrect drive letters.

Step 2: Validate SQL Server Service Account Permissions

• Open Services.msc and note the account running “SQL Server (MSSQLSERVER)” or your named instance.
• Right-click the target folder (e.g., D:\SQLData\) → Properties → Security tab.
• Grant Full Control to the SQL Server service account (typically NT Service\MSSQLSERVER or a domain service account).
• Apply permissions recursively using: icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F /T

Step 3: Check for Disk, Mount Point, or UNC Path Issues

• If using a UNC path or mapped network drive, confirm SQL Server supports it (local service accounts cannot resolve mapped drives reliably).
• For UNC paths, ensure the SQL Server service account has been granted share-level and NTFS permissions, and that the path is reachable via Test-Path \\server\share\folder in PowerShell.
• If using Storage Spaces or a mounted volume, confirm the volume is online via Disk Management (diskmgmt.msc) and not in a “Reserved” or “Offline” state.

Step 4: Temporarily Disable Antivirus/EDR Scanning on SQL Paths

• Add exclusions in Windows Defender or third-party AV for SQL Server data/log directories (.mdf, .ldf, .ndf extensions and folder paths).
• Restart the SQL Server service after applying exclusions: Restart-Service MSSQLSERVER.

Step 5: Review SQL Server Error Log for OS-Level Detail

• Open the SQL Server Error Log via SSMS (Management → SQL Server Logs) or directly from C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG.
• Match the embedded Windows OS error code (e.g., “Access is denied” or “The system cannot find the path specified”) to pinpoint whether this is a permissions or path-existence issue.

Alternative or Verification Steps

• Re-run the original CREATE DATABASE, ATTACH DATABASE, or RESTORE DATABASE command and confirm it completes without Error 5133.
• As an alternative, use SSMS’s “Browse” button in the file path dialog (instead of typing the path manually) to let SQL Server validate directory access interactively before executing the script.

Troubleshooting Guide: SQL Server Error 5134 — FILESTREAM Path Not Supported

Why This Error Happens

This error occurs because FILESTREAM requires the underlying volume to be formatted with NTFS or ReFS, as these are the only file systems that support the extended attributes and transactional semantics FILESTREAM relies on. In most cases, the specified path points to a FAT32-formatted drive, a network share (UNC path) without proper configuration, or a mapped drive that Windows Server 2025 does not recognize as a local supported volume. Additionally, this error can surface if the target directory resides on a compressed or encrypted volume, which FILESTREAM explicitly disallows.

Step-by-Step Resolution Guide

Step 1: Verify the File System Format of the Target Volume

• Open File Explorer, right-click the target drive (e.g., D:\), and select Properties.
• Confirm the File System field reads NTFS or ReFS. If it shows FAT32 or exFAT, this is the root cause.
• Alternatively, run this command in PowerShell or Command Prompt:

fsutil fsinfo volumeinfo D:

Step 2: Reformat or Relocate to a Supported Volume

• If the drive is FAT32/exFAT, back up all data, then reformat using:

format D: /FS:NTFS /Q

• If reformatting is not an option, choose a different NTFS/ReFS volume for the FILESTREAM data path.
• Update the SQL Server FILESTREAM configuration path to the new supported volume (e.g., E:\SQLData\FilestreamContainer).

Step 3: Check for Compression, Encryption, or UNC Path Issues

• Right-click the target folder, go to Properties > Advanced, and ensure “Compress contents” and “Encrypt contents” are both unchecked.
• If using a network path, ensure it is a validated UNC path with proper FILESTREAM share permissions (\\ServerName\ShareName), not a mapped drive letter.
• Confirm the SQL Server service account has Full Control NTFS permissions on the target folder.

Step 4: Restart SQL Server and Re-Attempt the FILESTREAM Operation

• Restart the SQL Server service via SQL Server Configuration Manager to apply path changes.
• Re-run the CREATE DATABASE or ALTER DATABASE statement that references the FILESTREAM filegroup path.

Alternative or Verification Steps

• Verify success by running sys.database_filestream_options or checking SQL Server Error Log for confirmation that the FILESTREAM container was created without errors.
• If the issue persists, use fsutil fsinfo drives to list all available volumes and cross-check that your chosen path isn’t accidentally pointing to a removable or virtual disk still using FAT32.

Why This Error Happens

Error 5131 occurs when SQL Server attempts to rebuild a transaction log—typically during an ALTER DATABASE... REBUILD LOG operation or an emergency-mode repair via DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS—but detects that the database metadata references multiple log files. The log rebuild process is hardcoded to only work when exactly one .ldf file is registered to the database, as the engine cannot safely reconstruct a Virtual Log File (VLF) chain across multiple physical log files. This usually happens on databases where a DBA previously added a second log file for performance or space reasons, and the primary log subsequently became corrupted or inaccessible.

Step-by-Step Resolution Guide

Step 1: Identify All Log Files Attached to the Database

• Open SQL Server Management Studio (SSMS) or connect via sqlcmd on Windows Server 2025.
• Run the following query to list every file associated with the affected database:

  USE [YourDatabaseName];
  GO
  SELECT name, physical_name, type_desc 
  FROM sys.database_files 
  WHERE type_desc = 'LOG';

• Confirm whether more than one row with type_desc = 'LOG' is returned.

Step 2: Remove the Extra Log File(s) from Database Metadata

• Set the database to EMERGENCY mode or SINGLE_USER mode first if it is not already accessible:

  ALTER DATABASE [YourDatabaseName] SET EMERGENCY;
  ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Attempt to remove the secondary log file(s) using the logical file name identified in Step 1:

  ALTER DATABASE [YourDatabaseName] REMOVE FILE [SecondaryLogFileLogicalName];

• If the command fails because the file is required for recovery, proceed to the detach/reattach method in Step 3.

Step 3: Detach and Reattach Using Only the Primary Data File

• Stop the SQL Server service via services.msc or net stop MSSQLSERVER on Windows Server 2025.
• Rename or move the secondary .ldf file out of the C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ directory (adjust path/version as needed).
• Reattach the database using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, referencing only the primary .mdf file:

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

• This forces SQL Server to generate a brand-new single log file since only one data file is referenced.

Step 4: Run DBCC CHECKDB to Validate Integrity

• Once the log rebuild succeeds, immediately run:

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

• Review the output for any lingering consistency errors introduced by the forced rebuild.

Alternative or Verification Steps

• Verify the fix by querying sys.database_files again to confirm exactly one log file now exists and that the database state in sys.databases shows ONLINE.
• If the ATTACH_REBUILD_LOG method fails due to corruption in the primary data file itself, restore from your most recent full backup plus transaction log backups instead of attempting further repairs, as rebuilding the log discards all unrecoverable transactions.

Troubleshooting Guide: SQL Server Error 5132 — FILESTREAM Raw Device Path Error

Why This Error Happens

This error occurs when SQL Server attempts to create a FILESTREAM-enabled database or filegroup using a path that points directly to a raw, unformatted volume rather than a valid NTFS directory. FILESTREAM requires a proper folder structure on an NTFS (or ReFS, depending on version support) volume because it relies on the underlying file system’s directory and security attributes to manage BLOB storage. If the specified drive letter or mount point has not been initialized with a file system, SQL Server cannot create the container folder it needs, triggering Error 5132.

Step-by-Step Resolution Guide

Step 1: Verify the Target Volume Has a Valid File System

• Open Disk Management (diskmgmt.msc) on Windows Server 2025 / Windows 11.
• Check if the target drive or mount point shows as “Unallocated” or “Raw” — this confirms the volume lacks a file system.
• If raw, right-click the volume and select Format, choosing NTFS as the file system type.

Step 2: Create a Proper Directory Path for FILESTREAM

• Do not point FILESTREAM directly to a drive root (e.g., F:\) — instead, create a dedicated subfolder such as F:\SQLData\FileStreamData.
• Ensure the folder does not already exist before running the CREATE DATABASE or ALTER DATABASE statement — SQL Server needs to create this directory itself.
• Example corrected T-SQL:

  ALTER DATABASE MyDB
  ADD FILE (NAME = FSGroup1, FILENAME = 'F:\SQLData\FileStreamData')
  TO FILEGROUP FileStreamGroup;

Step 3: Confirm FILESTREAM Is Enabled at the Instance Level

• Open SQL Server Configuration Manager → SQL Server Services → right-click your instance → Properties → FILESTREAM tab.
• Ensure “Enable FILESTREAM for Transact-SQL access” is checked.
• Restart the SQL Server service after making changes.

Step 4: Validate NTFS Permissions on the Target Folder

• Confirm the SQL Server service account has Full Control on the parent directory (e.g., F:\SQLData).
• Use icacls F:\SQLData /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" to apply correct permissions if needed.

Alternative or Verification Steps

• Re-run the CREATE DATABASE or ALTER DATABASE command after formatting the volume and confirming the path is a subfolder (not a raw drive root) — the operation should complete without Error 5132.
• If the issue persists, test FILESTREAM creation on a different NTFS-formatted local disk to isolate whether the original volume has deeper disk-level corruption requiring chkdsk /f or re-initialization in Disk Management.

Troubleshooting Guide: Error 5129 – Log Cannot Be Rebuilt When Primary File Is Read-Only

Why This Error Happens

Error 5129 occurs when SQL Server 2025 attempts to rebuild a missing or corrupted transaction log but cannot write to the primary data file (.mdf) because it is marked as read-only. This typically happens when the file resides on a read-only volume, has restrictive NTFS permissions, or the database was set to READ_ONLY state before the log file was lost or detached. SQL Server requires write access to the primary file to update header information during log rebuild operations, so the operation fails immediately when this access is denied.

Step-by-Step Resolution Guide

Step 1: Verify the Read-Only Status of the Primary File

• Navigate to the data file location, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\
• Right-click the .mdf file, select Properties, and check the Read-only checkbox under the General tab.
• Alternatively, run PowerShell: Get-ItemProperty -Path "D:\SQLData\YourDB.mdf" | Select-Object IsReadOnly

Step 2: Remove the Read-Only Attribute

• Uncheck the Read-only box in file Properties, or run: attrib -r "D:\SQLData\YourDB.mdf"
• If the file resides on a read-only mounted volume (e.g., a SAN snapshot or DVD-based storage), move it to a writable NTFS volume instead.
• Confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Modify and Write permissions on the folder via the Security tab.

Step 3: Check and Reset the Database READ_ONLY Property

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

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

• If is_read_only returns 1, switch it off with:

  ALTER DATABASE YourDB SET READ_WRITE WITH ROLLBACK IMMEDIATE;

Step 4: Rebuild the Transaction Log

• With the file now writable, attempt the rebuild using:

  ALTER DATABASE YourDB REBUILD LOG ON (NAME = YourDB_Log, FILENAME = 'D:\SQLData\YourDB_log.ldf');

• If the database was in EMERGENCY mode prior to this, ensure it is set using:

  ALTER DATABASE YourDB SET EMERGENCY;

• Restart SQL Server service if file locks persist: Restart-Service MSSQLSERVER

Step 5: Validate File System and Storage Policies

• On Windows Server 2025, check Storage Replica or ReFS configurations that may enforce read-only states on replicated volumes.
• Use fsutil fsinfo volumeinfo D: to confirm the volume isn’t flagged with read-only media attributes.

Alternative or Verification Steps

• After rebuilding, run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS to confirm database integrity and ensure no residual corruption remains.
• If the issue persists, restore the database from the most recent full backup and apply transaction log backups instead of attempting an in-place rebuild, especially if the file permissions issue stems from a corrupted ACL that cannot be easily reset.

Troubleshooting Guide: Error 5130 – Log Cannot Be Rebuilt When Database Mirroring Is Enabled

Why This Error Happens

Error 5130 occurs because SQL Server will not allow a transaction log rebuild on a database that is currently configured for database mirroring, as rebuilding the log would break the Log Sequence Number (LSN) chain required for the mirror to stay in sync with the principal. This typically happens when an administrator attempts to run ALTER DATABASE ... REBUILD LOG or use DBCC log rebuild operations while mirroring endpoints are still active. The operation is blocked by design to prevent data divergence between the principal and mirror databases, rather than being caused by a file corruption or permissions issue.

Step-by-Step Resolution Guide

Step 1: Verify the Current Mirroring State

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

  SELECT name, mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_state_desc IS NOT NULL;

• Confirm the database in question shows a state such as SYNCHRONIZED or SUSPENDED rather than NULL (not mirrored).

Step 2: Remove Database Mirroring Before Rebuilding the Log

• Break the mirroring session using:

  ALTER DATABASE [YourDatabaseName] SET PARTNER OFF;

• Confirm mirroring has been fully removed by re-running the query from Step 1 and checking that mirroring_state_desc returns NULL.
• Ensure the mirroring endpoints are stopped via sys.database_mirroring_endpoints if the partner relationship persists.

Step 3: Rebuild the Transaction Log

• With mirroring disabled, set the database into emergency mode if needed:

  ALTER DATABASE [YourDatabaseName] SET EMERGENCY;

• Run the rebuild command:

  ALTER DATABASE [YourDatabaseName] REBUILD LOG ON (NAME=YourDatabaseName_log, FILENAME='C:\SQLData\YourDatabaseName_log.ldf');

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

  ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 4: Re-Establish Database Mirroring

• After the log rebuild is confirmed successful, re-configure mirroring from the principal to the mirror server using SSMS or T-SQL ALTER DATABASE ... SET PARTNER = 'TCP://MirrorServer:5022'.
• Re-synchronize the databases by taking a fresh full backup and log backup, then restoring them with NORECOVERY on the mirror instance before re-enabling the partnership.

Alternative or Verification Steps

• Verify success by running DBCC CHECKDB on the rebuilt database and confirming no consistency errors, then check sys.database_mirroring to confirm the mirroring state returns to SYNCHRONIZED.
• If mirroring cannot be cleanly removed due to network or endpoint issues, consider migrating to Always On Availability Groups as a modern alternative, since legacy database mirroring is deprecated in SQL Server 2025.

Troubleshooting Guide: SQL Server Error 5125 – “File ‘%ls'”

Why This Error Happens

Error 5125 occurs when SQL Server 2025 attempts to access a database file (.mdf, .ndf, or .ldf) that either does not exist at the specified path, is locked by another process, or cannot be read due to insufficient NTFS permissions for the SQL Server service account. This commonly happens after migrating databases between servers, restoring from a backup with an incorrect file path, or when the underlying storage volume has been disconnected or renamed. In some cases, the file header itself is corrupted, preventing SQL Server from validating it as a legitimate database file.

Step-by-Step Resolution Guide

Step 1: Verify the File Path and Existence

• Open File Explorer or PowerShell and navigate to the exact path referenced in the error message.
• Run Test-Path "D:\SQLData\YourDatabase.mdf" in PowerShell to confirm the file exists.
• Check for typos in the path if the database was recently attached or restored using a MOVE clause in RESTORE DATABASE.

Step 2: Check NTFS Permissions on the File and Folder

• Right-click the data/log folder (e.g., D:\SQLData\), go to Properties > Security.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Full Control or at minimum Read/Write/Modify permissions.
• Use icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER":(OI)(CI)F in an elevated Command Prompt to apply correct permissions recursively.

Step 3: Confirm the File Is Not Locked by Another Process

• Open Task Manager or Resource Monitor and check if another instance of SQL Server, an antivirus scanner, or a backup tool has the file open.
• Use Handle.exe (Sysinternals) with handle64.exe YourDatabase.mdf to identify the locking process.
• Stop the conflicting process or add an antivirus exclusion for the SQL Server data directory (D:\SQLData\*.mdf, *.ldf, *.ndf).

Step 4: Rebuild or Reattach the Database Correctly

• If the file path changed, run ALTER DATABASE YourDB MODIFY FILE (NAME = YourDB_Data, FILENAME = 'D:\SQLData\YourDB.mdf') to update the catalog reference.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER for changes to take effect.
• If the file is corrupted, attempt DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS to assess damage.

Alternative or Verification Steps

• Run sp_helpdb YourDB or query sys.master_files to confirm SQL Server now recognizes the correct file path and status is ONLINE.
• If permissions and paths are correct but the error persists, restore the database from the most recent verified backup using RESTORE DATABASE YourDB FROM DISK = 'D:\Backups\YourDB.bak' WITH REPLACE.

Troubleshooting Guide: SQL Server Error 5127

Why This Error Happens

Error 5127 occurs when a CREATE DATABASE ... AS SNAPSHOT OF statement does not include an entry for every data file (NDF/MDF) that belongs to the source database. SQL Server requires a one-to-one mapping between the source database’s physical files and the sparse files defined in the snapshot command, so if the source database has had files added (or a file is simply omitted by mistake) after the snapshot script was written, the engine cannot create the copy-on-write sparse file structure correctly. This is almost always a syntax/scripting oversight rather than a permissions or corruption issue, though stale scripts following a schema change are a common trigger.

Step-by-Step Resolution Guide

Step 1: Identify All Current Data Files in the Source Database

• Connect to the source database using SQL Server Management Studio (SSMS) or sqlcmd.
• Run the following query to pull an accurate, current list of logical names and physical paths:

SELECT name AS LogicalName, physical_name AS PhysicalPath, type_desc
FROM sys.master_files
WHERE database_id = DB_ID('YourSourceDB') AND type_desc = 'ROWS';

• Note: Only data files (ROWS) are needed; log files (LOG) are never included in a snapshot definition.

Step 2: Rebuild the CREATE DATABASE Snapshot Script

• Open your existing snapshot T-SQL script located in your deployment folder (e.g., C:\Scripts\CreateSnapshot.sql).
• Ensure every logical name retrieved in Step 1 has a corresponding NAME = and FILENAME = entry pointing to a .ss file.
• Example corrected syntax:

CREATE DATABASE YourSourceDB_Snapshot ON
(
    NAME = YourSourceDB_Data,
    FILENAME = 'C:\SQLSnapshots\YourSourceDB_Data.ss'
),
(
    NAME = YourSourceDB_Data2,
    FILENAME = 'C:\SQLSnapshots\YourSourceDB_Data2.ss'
)
AS SNAPSHOT OF YourSourceDB;

• Double-check the folder C:\SQLSnapshots\ exists on the Windows Server 2025 host and that the SQL Server service account has write permissions to it.

Step 3: Account for Recently Added Files (Post-Deployment Changes)

• If a DBA added a new NDF file to the source database after the original snapshot script was created, that file will be missing from older automation scripts.
• Update any scheduled jobs (SQL Server Agent jobs) or PowerShell deployment scripts that generate snapshots to dynamically pull file lists from sys.master_files rather than using a hardcoded list.

Step 4: Re-execute the Snapshot Creation

• Run the corrected script in a new query window in SSMS.
• Confirm there are no typos in logical file names, as these are case-sensitive matches against sys.master_files.

Alternative or Verification Steps

• Verify success by querying sys.databases and sys.master_files for the new snapshot database; the source_database_id column should match your source database’s ID.
• If the error persists, generate the full script automatically using PowerShell with dbatools (New-DbaDbSnapshot cmdlet), which dynamically enumerates all data files and eliminates manual scripting errors entirely.

Troubleshooting Guide: SQL Server Error 5128 – “Write to sparse file failed due to lack of disk space”

Why This Error Happens

This error occurs when SQL Server attempts to write to a sparse file (commonly used for database snapshots or FILESTREAM/FileTable data) but the underlying volume does not have enough physical disk space to allocate the required blocks. Because sparse files only consume disk space as data is actually written, the volume can appear to have room based on logical file size while physically running out of allocation space during write operations. This is frequently seen on volumes hosting database snapshots, where growth of the source database or multiple concurrent snapshots rapidly consumes the available capacity.

Step-by-Step Resolution Guide

Step 1: Verify Actual Free Disk Space on the Volume

• Open File Explorer or run Get-PSDrive -PSProvider FileSystem in PowerShell to check free space on the volume hosting the sparse file (typically where snapshot .ss files or FILESTREAM data reside).
• Cross-check against SQL Server’s reported path using:

  SELECT physical_name, size, type_desc FROM sys.master_files;

• Confirm the volume isn’t near-full due to unrelated files, backups, or logs sharing the same drive.

Step 2: Identify and Remove Unnecessary Database Snapshots

• Since sparse files are primarily used by database snapshots, list active snapshots with:

  SELECT name, source_database_id FROM sys.databases WHERE source_database_id IS NOT NULL;

• Drop obsolete or unused snapshots to immediately free disk space:

  DROP DATABASE [SnapshotName];

• Avoid retaining long-lived snapshots on volumes with limited capacity, as they grow continuously with source database writes.

Step 3: Expand Disk Space or Relocate the Sparse File

• If using a physical server, add additional storage capacity to the volume via Disk Management (diskmgmt.msc) or extend the volume if unallocated space is available.
• On virtualized or cloud-hosted Windows Server 2025 instances, resize the virtual disk and extend the partition using:

  Resize-Partition -DriveLetter D -Size (Get-PartitionSupportedSize -DriveLetter D).SizeMax

• If expansion isn’t possible, relocate FILESTREAM data or snapshot files to a volume with more available space and update file paths accordingly.

Step 4: Check NTFS Volume Allocation and File System Limits

• Confirm the target volume uses NTFS (sparse files are not supported on ReFS in all configurations) by running fsutil fsinfo volumeinfo D:.
• Validate that NTFS cluster size and file system limits aren’t artificially restricting sparse file growth.
• Restart the SQL Server service after making storage changes to ensure file handles are refreshed.

Alternative or Verification Steps

• After freeing space or expanding storage, re-run the operation that triggered the snapshot or FILESTREAM write and monitor Event Viewer > Applications and Services Logs > SQL Server for recurrence of Error 5128.
• As an alternative, enable disk space alerts via Performance Monitor (LogicalDisk > % Free Space) to proactively prevent future sparse file write failures before they impact production workloads.

Troubleshooting Guide: SQL Server Error 5121 — “The path specified by is not in a valid directory”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to create, attach, or restore a database file to a path that either does not exist, is not accessible to the SQL Server service account, or resides on a drive/volume not recognized by the server. It commonly surfaces after restoring a database with a MOVE clause pointing to an invalid location, migrating a database between servers with different folder structures, or when the target directory was deleted/renamed after being referenced in a script. Underlying causes also include missing NTFS permissions for the SQL Server service account or the path exceeding Windows’ maximum character length.

Step-by-Step Resolution Guide

Step 1: Verify the Target Directory Exists

• Open File Explorer or Command Prompt on the Windows Server 2025 / Windows 11 host running the SQL instance.
• Navigate to the exact path specified in the error (e.g., D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• If the folder is missing, create it manually using mkdir "D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA".
• Confirm there are no typos, trailing spaces, or incorrect drive letters in the path string used in your T-SQL script.

Step 2: Check NTFS Permissions for the SQL Server Service Account

• Right-click the target folder, select Properties > Security > Edit.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER or a custom domain account) has Full Control or at minimum Modify, Read, and Write permissions.
• Apply permissions recursively if subfolders (like DATA or LOG) are involved.
• Restart the SQL Server service via SQL Server Configuration Manager after updating permissions.

Step 3: Validate the Path in Your T-SQL Command

• If using RESTORE DATABASE with MOVE, run RESTORE FILELISTONLY FROM DISK = 'BackupPath.bak' first to confirm the correct logical file names.
• Rewrite the command with the exact physical path, for example:

  RESTORE DATABASE [YourDB]
  FROM DISK = 'D:\Backups\YourDB.bak'
  WITH MOVE 'YourDB_Data' TO 'D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDB.mdf',
       MOVE 'YourDB_Log' TO 'D:\SQLData\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDB_log.ldf';

• Ensure the path does not exceed 260 characters unless long path support is explicitly enabled in Windows Server 2025.

Step 4: Confirm the Drive or Volume Is Online and Recognized

• Open Disk Management (diskmgmt.msc) and verify the target drive is online, not read-only, and properly formatted (NTFS/ReFS).
• If using a mounted volume or network share (UNC path), confirm SQL Server 2025 supports the path type — local paths are strongly recommended for data/log files.
• Reboot the volume or reattach it if it shows as “Offline” or “Unknown.”

Alternative or Verification Steps

• After applying the fix, rerun the original CREATE DATABASE, RESTORE, or ALTER DATABASE command and confirm it completes without Error 5121.
• As an alternative, use SQL Server Management Studio (SSMS)‘s GUI-based restore wizard, which auto-validates and displays available paths, reducing manual path-entry errors.

Troubleshooting Guide: SQL Server Error 5123

Why This Error Happens

Error 5123 occurs when the SQL Server service account lacks the necessary NTFS permissions to read or write to the folder path specified in the CREATE FILE or CREATE DATABASE statement. This is frequently triggered by copying database files via Windows Explorer (which strips permissions) or by attempting to create a database on a drive/path that does not actually exist or has been restricted by Windows 11/Server 2025’s enhanced security defaults. In some cases, the physical file path is simply invalid, points to a non-existent directory, or is being blocked by antivirus/EDR software locking the file handle.

Step-by-Step Resolution Guide

Step 1: Verify the Target Directory Exists

• Open File Explorer or Command Prompt and confirm the exact path referenced in the error message (e.g., D:\SQLData\MyDB.mdf) is spelled correctly and the parent folder exists.
• If the folder is missing, create it manually: mkdir D:\SQLData.
• Re-run the CREATE DATABASE or CREATE FILE statement to see if the error persists.

Step 2: Grant NTFS Permissions to the SQL Server Service Account

• Right-click the target folder (e.g., D:\SQLData) → Properties → Security → Edit.
• Add the SQL Server service account (typically NT Service\MSSQLSERVER or a custom domain service account) with Full Control.
• Apply permissions recursively and click OK, then restart the SQL Server service via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Check for File Locks or Antivirus Interference

• Use Resource Monitor (resmon.exe) → CPU tab → Associated Handles to search for the .mdf or .ldf file name and identify any process locking it.
• Temporarily disable real-time scanning in Windows Defender or third-party antivirus for the SQL data directory, then retry the operation.
• Add a permanent antivirus exclusion for D:\SQLData\*.mdf and *.ldf paths to prevent recurrence.

Step 4: Confirm Disk Space and Drive Health

• Run Get-PSDrive -PSProvider FileSystem in PowerShell to confirm the target drive has sufficient free space.
• If the drive shows errors, run chkdsk D: /f (requires a restart if the drive is in use) to repair file system issues.

Alternative or Verification Steps

• After applying permissions, verify success by running CREATE DATABASE TestDB ON (NAME = TestDB, FILENAME = 'D:\SQLData\TestDB.mdf') in SSMS — it should complete without Error 5123.
• If the issue persists, try creating the file on a different local drive (e.g., C:\Temp) to isolate whether the problem is path-specific or a broader service account permission issue.

Troubleshooting Guide: Error 5124 in Microsoft SQL Server 2025

Why This Error Happens

Error 5124 occurs when SQL Server detects a mismatch between the file header of a full-text catalog file and the metadata recorded for it in the database. This typically happens due to disk-level corruption, an improper shutdown during a write operation, or a full-text catalog file being restored or copied from a different database without its corresponding data files. It can also surface if antivirus or backup software locked or modified the catalog file while SQL Server had it open, leading to header desynchronization.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of Corruption

• Run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm whether corruption is isolated to the full-text catalog or extends to core data files.
• Check the SQL Server Error Log (Management > SQL Server Logs) for correlating I/O or disk errors around the same timestamp as Error 5124.
• Identify the exact catalog file path referenced in the error, typically located under C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\FTData\.

Step 2: Rebuild the Full-Text Catalog

• Open SSMS and navigate to Database > Storage > Full Text Catalogs.
• Right-click the affected catalog and select Rebuild (this is non-destructive to your base tables, only full-text indexes are regenerated).
• Alternatively, run the T-SQL command:

ALTER FULLTEXT CATALOG YourCatalogName REBUILD;

• Repopulate the index afterward with:

ALTER FULLTEXT CATALOG YourCatalogName START FULL POPULATION;

Step 3: Restore From a Known Good Backup (If Rebuild Fails)

• If the rebuild fails or the underlying database file itself is flagged as mismatched, restore the full database (including the full-text catalog) from the last known good backup.
• Use RESTORE DATABASE YourDatabaseName FROM DISK = 'D:\Backups\YourDatabaseName.bak' WITH REPLACE, RECOVERY;
• Ensure you are restoring the entire database, not just the catalog files individually, since full-text catalogs are tightly bound to internal GUIDs within the database.

Step 4: Exclude SQL Server Directories From Antivirus/Backup Locks

• Add exclusions in Windows Defender or your third-party AV for the MSSQL\DATA and MSSQL\FTData folders.
• Navigate to Windows Security > Virus & Threat Protection > Exclusions and add the full SQL Server instance directory.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell to clear any stale file handles.

Alternative or Verification Steps

• After rebuilding or restoring, run a test full-text query (e.g., SELECT * FROM YourTable WHERE CONTAINS(YourColumn, 'testword');) to confirm the catalog is operational and returning results.
• If the error persists, run DBCC CHECKDB with the REPAIR_ALLOW_DATA_LOSS option only as a last resort on a copy of the database, and always validate against a backup before applying destructive repairs.

Troubleshooting Guide: SQL Server Error 5118

Why This Error Happens

This error occurs when NTFS file compression has been manually applied to a database file (.mdf, .ndf, or .ldf) that belongs to a database or filegroup which is not set to READ_ONLY. SQL Server 2025 does not support running read/write operations against compressed data files because the storage engine cannot reliably manage page allocations and transaction logging on compressed NTFS volumes. This typically happens when an administrator compresses a folder on a Windows Server 2025 volume to save disk space without realizing SQL Server data files reside within it, or when a database file is moved to a drive with “Compress this drive to save disk space” enabled by default.

Step-by-Step Resolution Guide

Step 1: Identify the Compressed File and Take the Database Offline

• Open SQL Server Management Studio (SSMS) and locate the database referenced in the %ls placeholder of the error message.
• Right-click the database, navigate to Tasks > Take Offline to prevent file-lock conflicts during the decompression process.
• Alternatively, run the following T-SQL command to force the database offline:

  ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Step 2: Decompress the File via File Explorer or PowerShell

• Navigate to the physical file location, typically found at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the specific .mdf or .ldf file, select Properties > General > Advanced.
• Uncheck the box labeled “Compress contents to save disk space” and click OK, then Apply.
• For a faster method on Windows Server 2025 Core installations, use PowerShell to clear the compression attribute:

  Compact.exe /U "C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\YourDatabase.mdf"

Step 3: Verify Filegroup and Volume Settings

• Ensure the parent folder or Windows Volume itself does not have the “Compress drive to save space” attribute enabled, as this will re-compress the file automatically.
• If the goal was data compression, verify you intended to use SQL Server’s native Row or Page Compression (via ALTER TABLE...REBUILD WITH (DATA_COMPRESSION = PAGE)) rather than NTFS compression, as these are mutually exclusive methodologies.

Step 4: Bring the Database Back Online

• Return to SSMS or use the following query to restart the engine’s access to the file:

  ALTER DATABASE [YourDatabaseName] SET ONLINE;

• Monitor the SQL Server Error Log to confirm the database transitions to a healthy “ONLINE” state without re-triggering Error 5118.

Alternative or Verification Steps

• Verification: Run SELECT name, state_desc FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the state_desc column returns ONLINE.
• Alternative Fix: If you require compression for archival purposes, set the specific filegroup to READ_ONLY first using ALTER DATABASE [YourDatabaseName] MODIFY FILEGROUP [FilegroupName] READONLY;before re-applying NTFS compression, as SQL Server permits compressed files only within read-only contexts.

Why This Error Happens

Error 5119 occurs when SQL Server attempts to create a database file (typically for snapshots, FILESTREAM, or sparse file-dependent features) on a volume whose file system does not support the sparse file attribute. This commonly happens when the database files reside on a ReFS/FAT32 volume misconfigured for sparse operations, or when the NTFS volume has sparse file support disabled via policy or corruption. It can also occur if the SQL Server service account lacks the NTFS permissions required to set file attributes on the target directory.

Step-by-Step Resolution Guide

Step 1: Verify the File System Type

• Open PowerShell as Administrator and run:
  Get-Volume -DriveLetter D | Select FileSystemType
• Confirm the drive hosting your database files is formatted as NTFS. Sparse files are not supported on FAT32 or exFAT volumes.
• If the volume is not NTFS, you must migrate the database files to an NTFS-formatted volume.

Step 2: Check and Enable Sparse File Support on NTFS

• Run fsutil fsinfo ntfsinfo D: to confirm the volume is healthy and NTFS version is current.
• Manually test sparse file creation using:
  fsutil sparse setflag C:\Temp\test.txt
• If this command fails with an error, the volume may have corruption or be mounted with restrictive mount options; run chkdsk D: /f and reboot to repair the file system.

Step 3: Validate NTFS Permissions for the SQL Service Account

• Navigate to the data directory (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA).
• Right-click the folder > Properties > Security tab, and ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• Apply permissions recursively using:
  icacls "D:\SQLData" /grant "NT Service\MSSQLSERVER:(OI)(CI)F" /T

Step 4: Recreate the Snapshot or Database File

• Drop any existing failed database snapshot using DROP DATABASE [SnapshotName];
• Re-run the CREATE DATABASE ... AS SNAPSHOT OF command pointing to the verified NTFS path.
• Monitor the SQL Server Error Log for successful file creation confirmation.

Alternative or Verification Steps

• Run SELECT name, physical_name FROM sys.master_files; to confirm the new sparse file was created successfully in the target directory.
• If the issue persists on a cluster or SAN-attached volume, check with your storage administrator to ensure the LUN is formatted/mounted correctly, as some third-party deduplication storage filters can block sparse file flags.

Troubleshooting Guide: SQL Server Error 5120 – “Unable to Open the Physical File”

Why This Error Happens

Error 5120 occurs when SQL Server’s service account lacks the necessary NTFS permissions to read or write to the specified .mdf, .ndf, or .ldf file on disk. This commonly happens after moving a database file to a new drive, restoring a database from another server, or when Windows security policies strip inherited permissions during a file copy operation. Additionally, the underlying operating system error code (such as Error 5 – Access Denied, or Error 32 – File in Use) provides the specific reason the file handle could not be obtained.

Step-by-Step Resolution Guide

Step 1: Verify the SQL Server Service Account

• Open Services.msc on Windows Server 2025 and locate “SQL Server (MSSQLSERVER)” or your named instance.
• Note the exact account listed under the “Log On As” column (e.g., NT Service\MSSQLSERVER or a domain service account).
• Ensure this is the same account you grant permissions to in the next step; do not assume it is LocalSystem on modern installations.

Step 2: Grant NTFS Permissions on the Data Directory

• Navigate to the folder containing the database files, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the specific .mdf or .ldf file (or the parent DATA folder for broader access), select Properties > Security > Edit.
• Add the SQL Server service account identified in Step 1 and grant it Full Control.
• Apply the changes and allow Windows to propagate permissions to child objects if applying to a folder.

Step 3: Check for File Locks or Exclusive Use

• If the OS error number returned is 32 (The process cannot access the file because it is being used by another process), the file is likely locked by an antivirus scanner or a backup utility.
• Open Resource Monitor (resmon.exe), go to the “CPU” tab, and use “Search Handles” to type the database filename and identify the locking process.
• Add an exclusion for the SQL Server DATA and LOG directories in Windows Defender or your third-party antivirus software to prevent future locking.

Step 4: Validate File Path and Existence

• Run sp_helpdb 'YourDatabaseName' in SQL Server Management Studio (SSMS) to confirm the exact path SQL Server is expecting.
• Verify there are no hidden extensions (e.g., the file is actually data.mdf.txt due to Windows “Hide extensions” settings) or typos in the path if you recently attached the database manually.

Step 5: Restart the SQL Server Service

• Once permissions are corrected, restart the service via PowerShell to ensure the file handle is re-initialized cleanly:
• Restart-Service -Name 'MSSQLSERVER' -Force

Alternative or Verification Steps

• Verification: Attempt to bring the database online via SSMS (Right-click Database > Tasks > Bring Online) or run ALTER DATABASE [YourDB] SET ONLINE; in a new query window to confirm the fix was successful.
• Alternative: If permissions are correct but the error persists, the disk sector or file header may be corrupted; attempt to detach the database and run CHECKDB on a copy of the file, or restore the database from the last known good backup.

Microsoft SQL Server 2025 Error 5114 Troubleshooting Guide

Why This Error Happens

Error 5114 occurs when SQL Server cannot properly access, read, or write to the transaction log files associated with a database, typically during a backup, restore, or consistency check operation. This is most commonly caused by file-level corruption, incorrect file placement during a restore operation, or insufficient NTFS permissions granted to the SQL Server service account on the log file directory. In some cases, this error also surfaces when the log file (.ldf) is locked by another process, orphaned after a failed migration, or if the file path referenced in the database metadata no longer matches the physical location on the Windows Server 2025 or Windows 11 file system.

Step-by-Step Resolution Guide

Step 1: Verify File Paths and Existence

• Open SQL Server Management Studio (SSMS) and run SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName'); to confirm the exact expected path of the .ldf file.
• Navigate to the referenced directory (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and confirm the log file physically exists and is not renamed or moved.
• If the file is missing, locate the last known good backup copy or check the Windows Recycle Bin on the server.

Step 2: Correct NTFS Permissions on the Log Directory

• Right-click the DATA folder containing the log file, select Properties > Security > Edit.
• Ensure the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control permissions.
• Restart the SQL Server service via services.msc or run net stop MSSQLSERVER && net start MSSQLSERVER in an elevated Command Prompt to apply changes.

Step 3: Run DBCC CHECKDB for Corruption

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; in SSMS to scan for structural corruption in the log or data files.
• If corruption is detected, attempt ALTER DATABASE YourDatabaseName SET EMERGENCY; followed by DBCC CHECKDB('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS); as a last resort.
• Take a full backup immediately after repair to prevent further data loss.

Step 4: Detach and Reattach the Database

• Run EXEC sp_detach_db 'YourDatabaseName', 'true'; to safely detach the database without dropping files.
• Manually copy or move the .ldf file to the correct path if it was misplaced.
• Reattach using CREATE DATABASE YourDatabaseName ON (FILENAME = 'C:\Path\To\YourDB.mdf'), (FILENAME = 'C:\Path\To\YourDB_log.ldf') FOR ATTACH;

Alternative or Verification Steps

• After applying fixes, run DBCC CHECKDB('YourDatabaseName') again and confirm zero allocation or consistency errors are returned.
• If the error persists, restore the database from the most recent verified backup using the Restore Database wizard in SSMS, selecting “Overwrite the existing database (WITH REPLACE)” to bypass file path conflicts.

Why This Error Happens

Error 5115 occurs when SQL Server’s CREATE DATABASE…AS SNAPSHOT OF statement encounters a source file that does not match the internal SQL Server file header signature. This typically happens when the file path points to a renamed, copied, or corrupted .mdf/.ndf file, or when a non-database file (such as a .txt, .bak, or system file) is mistakenly referenced in the snapshot command. It can also occur if the file was created by an incompatible or much older version of SQL Server and lacks the expected header structure.

Step-by-Step Resolution Guide

Step 1: Verify the Source File Path and Extension

• Open SQL Server Management Studio (SSMS) and check the exact file path used in your CREATE DATABASE snapshot statement.
• Confirm the file has a valid .mdf or .ndf extension — not .bak, .trn, or .ldf.
• Run the following to list actual database files:

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

Step 2: Validate File Integrity Using DBCC

• Run DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm the source database file is not corrupted.
• If corruption is detected, restore the database from a verified backup before attempting another snapshot.
• Check Event Viewer under Windows Logs > Application for any disk-level I/O warnings tied to the file.

Step 3: Confirm File Permissions and Location

• Navigate to the file location (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\) and verify the SQL Server service account has Read/Write NTFS permissions.
• Ensure the file is not located on a network share or mapped drive, as snapshots require local NTFS volumes.
• Re-check that no antivirus or backup software has locked or altered the file header during a scan.

Step 4: Reissue the Snapshot Command with Correct Syntax

• Rewrite the CREATE DATABASE statement, explicitly pointing to a valid .mdf file as the source:

CREATE DATABASE YourDB_Snapshot ON
( NAME = YourDB_Data, FILENAME = 'C:\SQLData\YourDB_Snapshot.ss' )
AS SNAPSHOT OF YourDB;

• Double-check there are no extra spaces, incorrect quotes, or mismatched logical file names (NAME value must match sys.master_files).

Alternative or Verification Steps

• After reissuing the command, confirm the snapshot was created successfully by querying SELECT * FROM sys.databases WHERE source_database_id IS NOT NULL;.
• If the error persists, detach and reattach the source database in SSMS to refresh its file header metadata, then retry the snapshot operation.

Troubleshooting Guide: SQL Server Error 5111 – File Activation Failure

Why This Error Happens

Error 5111 occurs when SQL Server 2025 cannot locate or access the physical database file (.mdf, .ndf, or .ldf) specified in the file path during database attachment or startup. This is typically caused by the file being moved, renamed, or deleted, incorrect permissions on the file or parent directory for the SQL Server service account, or the file path referencing a drive/location that is no longer accessible (such as a disconnected network share or offline disk). In some cases, this error also surfaces when the file is held exclusively by another process, including antivirus software or a conflicting SQL Server instance.

Step-by-Step Resolution Guide

Step 1: Verify the Physical File Path and Existence

• Open File Explorer or PowerShell and navigate to the directory specified in the error message (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Confirm the exact file name and extension match what SQL Server expects; watch for case sensitivity or extra spaces.
• Run Get-ChildItem -Path "C:\SQLData\" -Filter "*.mdf" in PowerShell to list actual files present.
• If the file is missing, restore it from backup or locate it on a different drive/volume.

Step 2: Validate NTFS Permissions for the SQL Server Service Account

• Right-click the database folder (e.g., D:\SQLData\) and go to Properties > Security.
• Ensure the SQL Server service account (commonly NT Service\MSSQLSERVER or a custom domain account) has Full Control or at minimum Modify, Read, and Write permissions.
• Use icacls "D:\SQLData" /grant "NT SERVICE\MSSQLSERVER:(OI)(CI)F" in an elevated Command Prompt to reset permissions if needed.
• Restart the SQL Server service via SQL Server Configuration Manager after applying changes.

Step 3: Check for File Locks or Conflicting Processes

• Open Resource Monitor (resmon.exe) and search the CPU tab for the file name under “Associated Handles” to identify locking processes.
• Temporarily disable real-time antivirus scanning on the SQL data directory to rule out interference.
• Ensure no other SQL Server instance or external backup tool (like VSS writers) is holding an exclusive lock on the .mdf/.ldf file.

Step 4: Reattach the Database Using Correct Syntax

• Open SQL Server Management Studio (SSMS) and run the following, updating paths as needed:

  CREATE DATABASE YourDB ON 
  (FILENAME = 'D:\SQLData\YourDB.mdf'), 
  (FILENAME = 'D:\SQLData\YourDB_log.ldf') 
  FOR ATTACH;

• If the log file is missing or corrupted, use FOR ATTACH_REBUILD_LOG instead (only for non-production, trusted databases).

Step 5: Confirm Drive and Volume Availability

• If the database resides on a mapped network drive or SAN volume, verify connectivity using Test-Path "\\NetworkShare\SQLData\" in PowerShell.
• Ensure the drive letter assignment hasn’t changed after a Windows Server 2025 update or reboot via Disk Management (diskmgmt.msc).

Alternative or Verification Steps

• After applying fixes, run sp_helpdb YourDB or check SQL Server Logs (via SSMS Object Explorer > Management > SQL Server Logs) to confirm the database status shows ONLINE with no further 5111 errors.
• If reattachment continues to fail, restore the database from the most recent valid backup using RESTORE DATABASE YourDB FROM DISK = 'D:\Backups\YourDB.bak' WITH RECOVERY as a fallback solution.

Troubleshooting Guide: SQL Server Error 5112 — FCB::SetSize

Why This Error Happens

This message is logged whenever a database file (data or log) automatically grows or is manually resized by SQL Server, triggered by Auto-Growth settings or an explicit ALTER DATABASE MODIFY FILE command. It is purely informational in nature and is not indicative of corruption or failure, but it can flood the SQL Server Error Log if Auto-Growth events occur frequently due to undersized initial file allocations. The root cause is typically a database file that is too small for its workload, causing constant incremental growth events rather than failure of the storage subsystem itself.

Step-by-Step Resolution Guide

Step 1: Verify the Message is Benign

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Confirm the full text matches: FCB::SetSize: file growth failed... or the standard informational variant mentioning oldSize and newSize.
• Cross-reference the timestamp with any user complaints of slowness; if none exist, this is purely a logging frequency issue.

Step 2: Right-Size the Database Files to Reduce Growth Events

• In SSMS, right-click the Database, select Properties > Files.
• Manually increase the Initial Size (MB) of the data and log files to match projected 6–12 month growth to prevent constant auto-growth triggers.
• Alternatively, run the following T-SQL to pre-allocate space immediately:

  ALTER DATABASE [YourDB] 
  MODIFY FILE (NAME = N'YourDB_log', SIZE = 8192MB);

Step 3: Adjust Auto-Growth Increments

• Avoid percentage-based growth (e.g., 10%) on large files, as this causes massive, slow I/O spikes that generate this message.
• Set Auto-Growth to a fixed MB value instead (e.g., 512MB or 1024MB) via Database Properties > Files > Autogrowth/Maxsize.
• For Windows Server 2025 environments using Storage Spaces Direct (S2D), ensure the underlying volume isn’t nearing capacity, as this can slow down the SetSize operation significantly.

Step 4: Suppress the Message (If Growth is Expected and Optimized)

• If the file sizes are already optimized and the message is simply noisy logging, trace flag 1807 can be used to suppress this specific informational message.
• Add the startup parameter via SQL Server Configuration Manager: -T1807.
• Restart the SQL Server (MSSQLSERVER) service for the trace flag to take effect.

Alternative or Verification Steps

• Verify: Monitor the SQL Server Error Log for 24–48 hours; the frequency of Error 5112 entries should drop significantly if file sizing was the root cause.
• Alternative: If running on Windows Server 2025 with Instant File Initialization (IFI) disabled, enable the “Perform Volume Maintenance Tasks” security policy for the SQL Service account via secpol.msc to drastically speed up data file growth operations (Note: this does not apply to Log files).

Troubleshooting Guide: SQL Server Error 5113

Why This Error Happens

Error 5113 occurs when SQL Server attempts to rebuild a transaction log file but detects that the database was not cleanly shut down — specifically, there were open transactions or active user connections at the time of shutdown. This typically happens after a forced service stop, an unexpected server crash, or a hardware failure that prevents SQL Server from properly flushing and closing the log file. Because the log file’s LSN (Log Sequence Number) chain is incomplete, SQL Server refuses to rebuild it automatically to avoid data corruption, and manual intervention using ATTACH_REBUILD_LOG or emergency mode is required.

Step-by-Step Resolution Guide

Step 1: Verify the Scope of the Problem

• Open SQL Server Management Studio (SSMS) or a query window and attempt to bring the database online using ALTER DATABASE [YourDB] SET ONLINE;
• Check the SQL Server Error Log (default path: C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for confirmation of Error 5113 alongside the database name.
• Confirm whether a valid, recent full backup and transaction log backup exist before proceeding — rebuilding the log breaks the backup chain.

Step 2: Attempt Database Attach with Log Rebuild

• Detach the database first if it is currently attached but inaccessible, using EXEC sp_detach_db 'YourDB';
• Run the following T-SQL command to force a rebuild of the log:

  CREATE DATABASE YourDB ON 
  (FILENAME = 'C:\SQLData\YourDB.mdf') 
  FOR ATTACH_REBUILD_LOG;

• Ensure the SQL Server service account has full Read/Write/Modify permissions on the folder containing the .mdf file (right-click folder → Properties → Security → Edit).

Step 3: Use Emergency Mode if Attach Fails

• If ATTACH_REBUILD_LOG fails, place the database into emergency mode:

  ALTER DATABASE YourDB SET EMERGENCY;
  ALTER DATABASE YourDB SET SINGLE_USER;
  DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS);
  ALTER DATABASE YourDB SET MULTI_USER;

• Run this only after confirming no usable backups exist, as REPAIR_ALLOW_DATA_LOSS can discard corrupted transactions.

Step 4: Restore From Backup (Preferred if Available)

• If a valid backup chain exists, restore the full backup followed by differential/log backups using RESTORE DATABASE and RESTORE LOG commands with NORECOVERY, finishing with WITH RECOVERY.
• This avoids the risk of data loss associated with forced log rebuilds.

Alternative or Verification Steps

• After recovery, run DBCC CHECKDB (YourDB) WITH NO_INFOMSGS, ALL_ERRORMSGS; to confirm there is no remaining corruption.
• If the database comes online but data appears inconsistent, immediately take a full backup and compare row counts on critical tables against your last known-good backup to assess data loss.

Troubleshooting Guide: SQL Server Error 5105 – File Activation Error

Why This Error Happens

Error 5105 occurs when SQL Server cannot locate, access, or create the physical file specified in a database operation, typically because the file path is invalid or the file has been moved or deleted. This is frequently caused by the SQL Server service account lacking NTFS permissions on the target folder, or because the drive/directory referenced in the command no longer exists. It can also surface during database attach operations when the specified .mdf or .ldf path doesn’t match the actual file location on disk.

Step-by-Step Resolution Guide

Step 1: Verify the Physical File Path

• Open File Explorer and navigate to the directory referenced in the error (e.g., C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Confirm the exact file name and extension (.mdf, .ndf, .ldf) match what SQL Server is expecting.
• If the file was renamed or moved, update your T-SQL command to reflect the correct path.

Step 2: Check NTFS Permissions on the Folder

• Right-click the data folder, go to 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 Read, Write, and Modify permissions.
• Restart the SQL Server service afterward via services.msc or by running net stop MSSQLSERVER && net start MSSQLSERVER.

Step 3: Confirm the Drive or Directory Exists

• If the error references a drive letter that no longer exists (e.g., after a storage migration), recreate the directory structure manually.
• Use PowerShell to quickly check: Test-Path "D:\SQLData\".
• If using a SAN or mounted volume, ensure it is online via Disk Management (diskmgmt.msc).

Step 4: Correct the CREATE DATABASE or ATTACH Statement

• If attaching a database, rerun the command with the corrected file path:

  CREATE DATABASE YourDB
  ON (FILENAME = 'D:\SQLData\YourDB.mdf'),
     (FILENAME = 'D:\SQLData\YourDB_log.ldf')
  FOR ATTACH;

• Ensure both the .mdf and .ldf paths are specified correctly and both files are present.

Step 5: Review SQL Server Error Log for Additional Clues

• Open SQL Server Management Studio (SSMS) and navigate to Management > SQL Server Logs.
• Look for accompanying errors (e.g., Error 5120 or 17204) that provide more specific detail on the root cause.
• Address any secondary errors first, as they often reveal the true underlying issue (e.g., antivirus lock, disk I/O failure).

Alternative or Verification Steps

• Run RESTORE VERIFYONLY FROM DISK = 'path_to_backup.bak' to confirm backup file integrity before attempting a restore.
• If permissions and paths are correct but the error persists, try detaching and reattaching the database using sp_detach_db followed by a fresh CREATE DATABASE ... FOR ATTACH command.

Troubleshooting Guide: SQL Server Error 5108

Why This Error Happens

Error 5108 occurs when SQL Server detects that the Log Sequence Number (LSN) or database ID stored in the log file (.ldf) header does not match the identifier recorded in the primary data file (.mdf). This mismatch typically happens when a log file is manually replaced, swapped from a different database, or when the transaction log has been rebuilt using ALTER DATABASE ... REBUILD LOG, severing the internal link between the data and log files. It can also occur after restoring a data file without its corresponding log file, or when file copies are mixed up during a manual backup/restore process on the file system.

Step-by-Step Resolution Guide

Step 1: Identify the Correct Matching Log File

• Check your backup directory for the original .ldf file that was created alongside the .mdf file.
• Verify file timestamps and properties in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\ to ensure you are pairing the correct files.
• If you have a recent backup, locate the .bak file to extract a valid, matching log file instead of using the corrupted one.

Step 2: Take the Database Offline and Swap Files

• Open SQL Server Management Studio (SSMS) or use PowerShell to stop the SQL Server service via Stop-Service MSSQLSERVER.
• Navigate to the data directory and replace the mismatched .ldf file with the correct, verified log file.
• Restart the SQL Server service using Start-Service MSSQLSERVER and attempt to bring the database back online.

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

• If no matching log file is available, you must force SQL Server to rebuild the log, acknowledging potential data loss or transactional inconsistency.
• Set the database to Emergency mode: ALTER DATABASE YourDB SET EMERGENCY;
• Run a consistency check: DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS);
• Note: This step should only be used as a last resort as it discards the log chain and breaks the LSN sequence.

Step 4: Detach and Re-attach the Database

• Once the correct log file is in place, detach the database using sp_detach_db 'YourDB';
• Re-attach the database using CREATE DATABASE YourDB ON (FILENAME = 'C:\...\YourDB.mdf'), (FILENAME = 'C:\...\YourDB_log.ldf') FOR ATTACH;
• Monitor the SQL Server Error Log located in C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for any further attachment errors.

Alternative or Verification Steps

• To verify the fix, run DBCC CHECKDB to ensure there are no further allocation or consistency errors, and confirm the database status reads ONLINE in SSMS.
• If the rebuild fails entirely, the most reliable alternative is to restore the entire database (both .mdf and .ldf) from the last known good full backup rather than attempting to repair the existing mismatched files.

Troubleshooting Guide: Error 5110 – Network Path Not Supported for System Database Files

Why This Error Happens

This error occurs when SQL Server attempts to locate or create system database files (master, model, msdb, or tempdb) on a UNC network path (e.g., \\ServerName\Share) rather than a local drive letter path. SQL Server Database Engine requires direct, low-latency access to system database files for startup integrity and does not support UNC paths for these files, even if the account running SQL Server has full permissions to that share. This typically happens after a misconfigured installation, a startup parameter pointing to a mapped network drive, or an attempt to move system databases to a SAN/NAS location using an unsupported path format.

Step-by-Step Resolution Guide

Step 1: Identify the Current System Database File Locations

• Open SQL Server Configuration Manager and check the SQL Server service Startup Parameters (tab in Properties) for -d, -l, and -e flags.
• Look for any path beginning with \\ (UNC) instead of a drive letter like C:\ or D:\.
• Alternatively, check the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQLServer\Parameters.

Step 2: Move System Database Files to a Local or Supported Path

• Stop the SQL Server service via Configuration Manager or net stop MSSQLSERVER.
• Physically copy the master.mdf, mastlog.ldf, and other system database files from the network share to a local drive or a supported block-level SAN LUN (e.g., D:\SQLData\).
• Ensure the destination folder has correct NTFS permissions granted to the SQL Server service account (Full Control).

Step 3: Update Startup Parameters to Reflect the New Local Path

• In SQL Server Configuration Manager, edit the -d (master data file), -l (master log file), and -e (error log) startup parameters to point to the new local path.
• Example: -dD:\SQLData\master.mdf, -lD:\SQLLogs\mastlog.ldf.
• Save changes and close the Properties dialog.

Step 4: Restart the SQL Server Service

• Start the SQL Server service again via Configuration Manager or net start MSSQLSERVER.
• Monitor the SQL Server Error Log (ERRORLOG file) located in the MSSQL\Log folder to confirm the service starts without Error 5110.

Step 5: Reconfigure Any Dependent Jobs or Mapped Drives

• If tempdb or other databases were also pointed to network paths via ALTER DATABASE ... MODIFY FILE, update those paths using T-SQL to a local or supported storage path.
• Restart the instance again after applying file path changes for tempdb, since tempdb is rebuilt at every startup.

Alternative or Verification Steps

• Verify the fix by checking the SQL Server Error Log for a clean startup message: “SQL Server is now ready for client connections” with no Error 5110 entries.
• If local disk space is a constraint and network storage is required, use an iSCSI or Fibre Channel SAN configuration that presents the storage as a local drive letter (block-level access) rather than a UNC/SMB file share, since SQL Server fully supports block-based network storage but not file-share (UNC) paths for system databases.

Troubleshooting Guide: SQL Server Error 5103

Why This Error Happens

This error occurs when attempting to create or modify a database file where the specified MAXSIZE parameter is numerically smaller than the current SIZE parameter, violating SQL Server’s file growth logic. It commonly arises during database restores, file modification scripts (ALTER DATABASE MODIFY FILE), or automated deployment scripts where size values were hardcoded incorrectly or copied from a template without adjustment. SQL Server 2025 strictly validates these constraints before allocating file space on the Windows Server 2025 or Windows 11 file system, rejecting the operation entirely rather than auto-correcting the mismatch.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting File and Current Values

• Open SQL Server Management Studio (SSMS) and connect to the target instance.
• Run the following query to inspect current file sizes (values are in 8KB pages, divide by 128 for MB):

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

• Note the logical file name referenced in the error message (%ls).

Step 2: Correct the Script or Command Causing the Error

• Locate the T-SQL script, PowerShell deployment file, or CI/CD pipeline configuration (e.g., in C:\Scripts\DBDeploy\ or your Azure DevOps YAML) that defines the file properties.
• Ensure the MAXSIZE value is explicitly greater than the SIZE value, or set it to UNLIMITED:

  ALTER DATABASE [YourDatabaseName] 
  MODIFY FILE (NAME = N'YourFileName', SIZE = 51200KB, MAXSIZE = 102400KB, FILEGROWTH = 10240KB);

• If unlimited growth is acceptable, use:

  ALTER DATABASE [YourDatabaseName] 
  MODIFY FILE (NAME = N'YourFileName', MAXSIZE = UNLIMITED);

Step 3: Check for Disk Space Constraints on Windows

• Navigate to the physical drive hosting the .mdf or .ldf file (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Verify there is sufficient free disk space to support the new MAXSIZE value; low disk space can cause administrators to mistakenly set MAXSIZE too low.
• Use Windows Server Disk Management or Get-PSDrive in PowerShell to confirm available capacity before increasing the value.

Step 4: Re-run the Database Restore or Creation Script

• If the error occurred during a restore operation, update the MOVE clause in your RESTORE DATABASE command to match corrected size parameters.
• Execute the corrected script again:

  RESTORE DATABASE [YourDatabaseName] 
  FROM DISK = N'D:\Backups\YourDatabase.bak' 
  WITH MOVE N'YourFileName' TO N'D:\Data\YourFileName.mdf', 
  REPLACE;

Alternative or Verification Steps

• Verify the fix by re-querying sys.master_files to confirm max_size is now greater than or equal to size, and check the SQL Server Error Log (ERRORLOG in the MSSQL\Log directory) for successful completion messages.
• If the error persists in automated pipelines, set MAXSIZE = UNLIMITED as a universal default in deployment templates to prevent future hardcoded mismatches.

Troubleshooting Guide: SQL Server Error 5104 – “File ‘%.*ls’ already used”

Why This Error Happens

Error 5104 occurs when SQL Server attempts to create or attach a database file using a physical file path that is already in use by another database, filegroup, or an existing file on disk. This typically happens due to a copy-paste error in a CREATE DATABASE or ALTER DATABASE statement where the file path wasn’t updated, or when restoring/attaching a database without specifying a unique MOVE path. It can also occur if a previous database using that file wasn’t properly detached or dropped, leaving the file locked or referenced in SQL Server’s internal metadata.

Step-by-Step Resolution Guide

Step 1: Verify the File Path in Your T-SQL Statement

• Open the script that triggered the error (CREATE DATABASE, ALTER DATABASE, or RESTORE DATABASE).
• Check the exact path specified in the FILENAME parameter, e.g., D:\SQLData\MyDB.mdf.
• Confirm you are not reusing a path already assigned to another database by running: SELECT name, physical_name FROM sys.master_files;

Step 2: Check for Orphaned or Locked Files on Disk

• Navigate to the target directory (e.g., D:\SQLData\ or C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
• Use File Explorer or dir in Command Prompt to confirm if the .mdf/.ndf/.ldf file already exists.
• If the file exists but isn’t tied to an active database, stop the SQL Server service via services.msc and manually rename or delete the orphaned file.

Step 3: Update the RESTORE Command with a Unique MOVE Path

• If restoring a backup, explicitly redirect the logical file names to new physical paths using the WITH MOVE clause:

RESTORE DATABASE [NewDB]
FROM DISK = 'D:\Backups\MyDB.bak'
WITH MOVE 'MyDB_Data' TO 'D:\SQLData\NewDB.mdf',
     MOVE 'MyDB_Log' TO 'D:\SQLLogs\NewDB.ldf';

• Ensure the SQL Server service account has write permissions on the new target folder.

Step 4: Drop or Detach the Conflicting Database (If Applicable)

• Identify which database currently owns the file: SELECT DB_NAME(database_id), physical_name FROM sys.master_files WHERE physical_name = 'D:\SQLData\MyDB.mdf';
• If the database is obsolete, detach it safely: EXEC sp_detach_db 'OldDB';
• Alternatively, drop it entirely if no longer needed: DROP DATABASE [OldDB];

Alternative or Verification Steps

• After applying a fix, re-run your CREATE or RESTORE command and confirm the database appears correctly in SQL Server Management Studio (SSMS) under Databases without error.
• If the issue persists, restart the SQL Server service (via services.msc or net stop MSSQLSERVER && net start MSSQLSERVER) to release any stale file handles before retrying.

Troubleshooting Guide: Error 5098 – Container Cannot Be Dropped Due to Pending Log Backup

Why This Error Happens

This error occurs when you attempt to remove a filegroup container (file) from a database that is operating in FULL or BULK-LOGGED recovery model while unlogged or minimally-logged changes are still pending in the transaction log. SQL Server 2025 blocks the ALTER DATABASE ... REMOVE FILE operation because dropping the container before a log backup would create a gap in the log chain, making point-in-time recovery impossible. Essentially, the engine is protecting data durability by refusing structural changes until the log records referencing that file have been safely backed up and truncated.

Step-by-Step Resolution Guide

Step 1: Identify the Database Recovery Model

• Open SQL Server Management Studio (SSMS) or a query window connected to your Windows Server 2025 instance.
• Run the following to confirm the recovery model:

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

• If the result is FULL or BULK_LOGGED, proceed to Step 2.

Step 2: Take a Transaction Log Backup

• Execute a log backup to clear the pending log records tied to the container:

  BACKUP LOG [YourDatabase] 
  TO DISK = 'C:\SQLBackups\YourDatabase_Log.trn' 
  WITH INIT;

• Ensure the backup destination folder (e.g., C:\SQLBackups\) exists and SQL Server’s service account has write permissions.
• If this is the first log backup ever taken, you may first need a full database backup:

  BACKUP DATABASE [YourDatabase] 
  TO DISK = 'C:\SQLBackups\YourDatabase_Full.bak';

Step 3: Retry the ALTER DATABASE Operation

• Once the log backup completes successfully, re-run the original command to remove the file:

  ALTER DATABASE [YourDatabase] 
  REMOVE FILE [YourLogicalFileName];

• Confirm there are no active transactions or open sessions using the file by checking:

  SELECT * FROM sys.dm_tran_active_transactions;

Step 4: Configure Scheduled Log Backups (Preventative)

• To avoid recurrence, set up a SQL Server Agent Job under SQL Server Agent > Jobs in SSMS to automate log backups every 15-30 minutes.
• Alternatively, configure an Ola Hallengren maintenance script or Windows Task Scheduler job pointing to a .bat file that triggers the backup T-SQL script.

Alternative or Verification Steps

• Verify the fix: Re-run DBCC SQLPERF(LOGSPACE) to confirm the log file for YourDatabase shows a lower “Log Space Used %” after the backup, indicating truncation was successful before retrying the ALTER DATABASE command.
• Alternative workaround: If a log backup is not immediately feasible (e.g., lack of storage), temporarily switch the database to SIMPLE recovery model using ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;, drop the file, then switch back to FULL and take a fresh full backup to restart the log chain.

Troubleshooting Guide: SQL Server Error 5099

Why This Error Happens

This error occurs because SQL Server does not support enabling snapshot-based isolation levels (READCOMMITTEDSNAPSHOT or ALLOWSNAPSHOTISOLATION) on a database that contains FILESTREAM filegroups. The underlying conflict is architectural: FILESTREAM data is stored directly on the NTFS file system rather than in the database’s versioned data pages, meaning the storage engine cannot generate the row-versioning data required for snapshot isolation. As a result, the ALTER DATABASE command is blocked at the engine level before any changes are committed.

Step-by-Step Resolution Guide

Step 1: Identify the FILESTREAM Filegroups

• Open SQL Server Management Studio (SSMS) and connect to your Windows Server 2025 instance.
• Run the following query to confirm which filegroups are causing the conflict:

SELECT name, type_desc FROM sys.filegroups WHERE type = 'FD';

• Note the names of these filegroups, as you will need them for migration or removal.

Step 2: Migrate or Remove FILESTREAM Data

• If the FILESTREAM data is non-essential or can be moved, use SSMS Object Explorer to navigate to Database > Properties > Filegroups and remove the FILESTREAM filegroup reference.
• If the data must be preserved, migrate it using the FILESTREAM_ON clause during a database copy, or script the data out to standard VARBINARY(MAX) columns using a T-SQL migration script.
• Ensure no active files are being streamed during this transition to avoid I/O errors.

Step 3: Re-attempt the ALTER DATABASE Command

• Once the FILESTREAM filegroups are removed or the database is confirmed clean, re-run the isolation command:

ALTER DATABASE [YourDatabaseName] SET READ_COMMITTED_SNAPSHOT ON;

• If Error 5099 persists, restart the SQL Server service via the Services app (services.msc) on Windows Server 2025 to clear any cached metadata locks.

Step 4: Verify Instance-Level FILESTREAM Configuration

• Open SQL Server Configuration Manager and select your instance properties.
• Navigate to the FILESTREAM tab and ensure “Enable FILESTREAM for Transact-SQL access” is unchecked if you intend to permanently disable this feature for the specific database group.
• Apply the changes and restart the SQL Server (MSSQLSERVER) service for the setting to take effect.

Alternative or Verification Steps

• Verification: Run SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the value returns 1 (enabled) without triggering an error.
• Alternative: If FILESTREAM is a mandatory requirement for your application, consider using a separate, dedicated database purely for FILESTREAM storage while keeping your transactional OLTP database (which utilizes snapshot isolation) completely separate from file I/O operations.

Troubleshooting Guide: SQL Server Error 5102 — “Attempted to open a filegroup for the invalid ID %d in database”

Why This Error Happens

This error occurs when SQL Server’s internal metadata references a filegroup ID that no longer exists or does not match the current state of the system catalog views (sys.database_files or sys.filegroups), typically due to database corruption. It can also arise after an incomplete or interrupted file/filegroup operation (such as a failed ALTER DATABASE ADD FILEGROUP or a botched restore) that left the metadata in an inconsistent state. In some cases, this is triggered by a corrupted system catalog page or a mismatch introduced during a manual edit of system tables or a failed attach/detach operation.

Step-by-Step Resolution Guide

Step 1: Check Database Consistency with DBCC CHECKDB

• Open SQL Server Management Studio (SSMS) or sqlcmd on your Windows Server 2025 / Windows 11 instance.
• Run the following command to identify the scope of corruption:

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

• Review the output for allocation errors related to filegroup IDs, especially messages referencing sys.sysprufiles or sys.sysfgfiles.

Step 2: Validate Filegroup and File Metadata

• Query the current filegroup mapping to check for orphaned or invalid IDs:

SELECT * FROM sys.filegroups;
SELECT * FROM sys.database_files;
SELECT * FROM sys.master_files WHERE database_id = DB_ID('YourDatabaseName');

• Cross-reference the data_space_id values against sys.filegroups to find IDs that don’t resolve to a valid filegroup.
• If a mismatch is found, note the invalid ID reported in the error message for targeted repair.

Step 3: Restore From a Known Good Backup

• If corruption is confirmed and no repair is feasible, restore the database from the last verified clean backup:

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

• Ensure the backup predates the corruption event by checking backup history in msdb.dbo.backupset.

Step 4: Run DBCC CHECKDB with Repair Option (Last Resort)

• If no clean backup is available, set the database to single-user mode:

ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Run the repair command (note: this may result in data loss):

DBCC CHECKDB ('YourDatabaseName', REPAIR_ALLOW_DATA_LOSS);

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

ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Step 5: Validate Windows and SQL Server Storage Health

• Check the underlying disk/storage subsystem on Windows Server 2025 for hardware-level corruption using chkdsk C: /f /r and Event Viewer (Windows Logs > System) for disk I/O errors.
• Confirm there were no unexpected shutdowns or storage disconnects (SAN/NAS issues) that coincided with the error’s first occurrence.

Alternative or Verification Steps

• Re-run DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; after remediation to confirm zero allocation or consistency errors remain.
• If the error persists after repair/restore, consider exporting the data into a newly created database using the SQL Server Import/Export Wizard, as this bypasses corrupted system metadata entirely.

Why This Error Happens

This error occurs because SQL Server 2025 detects that a database file is currently in an “offline-restoring” state as part of a page-level restore sequence. The SIMPLE recovery model does not support the log-chain continuity required to complete a RESTORE PAGE operation, so the engine blocks the switch to prevent data corruption. Essentially, the database believes a restore sequence is incomplete, even if you intended to abandon it.

Step-by-Step Resolution Guide

Step 1: Verify the Current Restore State

• Open SQL Server Management Studio (SSMS) or connect via sqlcmd on your Windows Server 2025 instance.
• Run the following query to check file states: SELECT name, state_desc FROM sys.master_files WHERE database_id = DB_ID('YourDBName');
• Look specifically for a state_desc of RESTORING on any file; this confirms the page restore is still pending completion.

Step 2: Complete or Cancel the Pending Restore Sequence

• If you intend to finish the restore, run RESTORE DATABASE YourDBName WITH RECOVERY; to close out the sequence.
• If the restore was accidental or is no longer needed, you must restore the specific file from a full backup again and immediately recover it, rather than trying to force a model change: RESTORE DATABASE YourDBName FILE = 'YourFile_Name' FROM DISK = 'C:\Backups\FullBackup.bak' WITH RECOVERY;
• Avoid using WITH NORECOVERY again until you are ready to finalize the chain.

Step 3: Change the Recovery Model (Post-Verification)

• Once the file state no longer shows RESTORING, navigate to Object Explorer in SSMS.
• Right-click the Database > Properties > Options.
• Change the “Recovery Model” dropdown to Simple and click OK, or execute: ALTER DATABASE YourDBName SET RECOVERY SIMPLE;

Step 4: Restart SQL Server Services (If State Persists)

• If the sys.master_files view still incorrectly flags the file as restoring due to a cached metadata lock, restart the service.
• Open Services.msc on Windows Server 2025.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart to clear any stuck session locks associated with the failed restore handle.

Alternative or Verification Steps

• Verification: After running the ALTER DATABASE command, re-query sys.databases using SELECT recovery_model_desc FROM sys.databases WHERE name = 'YourDBName'; to confirm the output reads SIMPLE.
• Alternative: If the file remains stuck in a RESTORING state with no valid backup media available to complete the chain, you may need to take the specific file offline using ALTER DATABASE YourDBName MODIFY FILE (NAME = YourFile_Name, OFFLINE) before attempting the recovery model change.

Troubleshooting Guide: Error 5097 in SQL Server 2025

Why This Error Happens

This error occurs because the database is operating in the FULL or BULK_LOGGED recovery model, and there are transactions recorded in the transaction log that have not yet been backed up. SQL Server prevents the database from transitioning to an OFFLINE state because doing so would make it impossible to capture that pending log activity, risking data loss or breaking the log backup chain. This is a protective mechanism, not a corruption issue—it typically happens when an administrator attempts to take a database offline immediately after heavy write activity without a prior log backup job completing.

Step-by-Step Resolution Guide

Step 1: Verify the Current Recovery Model

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to check the recovery model:
• SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• If the result is FULL or BULK_LOGGED, proceed to Step 2. If SIMPLE, this error is unlikely and you should check for active transactions instead.

Step 2: Perform a Manual Transaction Log Backup

• Execute a T-SQL command to clear the pending log records before retrying the ALTER DATABASE statement.
• Use the following syntax, ensuring the backup destination path exists on your Windows Server 2025 drive:
• BACKUP LOG [YourDatabaseName] TO DISK = 'C:\SQLBackups\YourDatabaseName_Log.trn';
• Wait for the command to return “Processed” messages confirming the backup completed successfully.

Step 3: Retry the ALTER DATABASE Statement

• Once the log backup is confirmed, immediately re-run the command that triggered the original error.
• Use the specific syntax: ALTER DATABASE [YourDatabaseName] SET OFFLINE WITH ROLLBACK IMMEDIATE;
• Monitor the “Messages” tab in SSMS to ensure the command completes without throwing Error 5097 again.

Step 4: Check for Lingering Active Transactions

• If the error persists, open a new query window and check for open transactions that might be generating new log records faster than they can be backed up.
• Run DBCC OPENTRAN('YourDatabaseName'); to identify the oldest active transaction.
• Resolve or kill the blocking session ID (SPID) using KILL [SPID] if it is safe to do so.

Alternative or Verification Steps

• Verification: After the ALTER DATABASE command succeeds, confirm the status by running SELECT state_desc FROM sys.databases WHERE name = 'YourDatabaseName';—the result should return OFFLINE.
• Alternative Fix: If you do not require point-in-time recovery and simply need to force the database offline immediately, temporarily switch the recovery model to SIMPLE using ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE; prior to taking it offline (note: this will break the existing log backup chain).

Troubleshooting Guide: Error 5093 — “The Operation Cannot Be Performed on a Database Snapshot”

Why This Error Happens

Error 5093 occurs when an operation that is not supported on database snapshots is attempted, such as running ALTER DATABASE, BACKUP, DBCC CHECKDB with repair options, or attempting to shrink, expand, or restore directly onto a snapshot. Database snapshots in SQL Server 2025 are read-only, point-in-time views backed by sparse files, and many administrative operations are explicitly blocked against them by the Database Engine. This also occurs if an application or script mistakenly targets the snapshot name instead of the source database due to misconfigured connection strings or naming conventions.

Step-by-Step Resolution Guide

Step 1: Identify the Target Database Context

• Run the following query to confirm whether the database in question is actually a snapshot:

  SELECT name, source_database_id, is_read_only 
  FROM sys.databases 
  WHERE source_database_id IS NOT NULL;

• If the database name returned matches the one throwing the error, it confirms you are operating on a snapshot rather than the source database.
• Check the connection string or application config file (e.g., web.config, appsettings.json) for incorrect database references pointing to the snapshot.

Step 2: Redirect the Operation to the Source Database

• Locate the source database using:

  SELECT DB_NAME(source_database_id) AS SourceDB, name AS SnapshotName 
  FROM sys.databases 
  WHERE name = 'YourSnapshotName';

• Modify your script, job, or application connection string to point to the SourceDB instead of the snapshot.
• Re-run the intended operation (backup, alter, maintenance task, etc.) against the correct source database.

Step 3: Drop and Recreate the Snapshot (If Corrupted or Stale)

• If the snapshot itself is no longer needed or is causing conflicts with scheduled maintenance plans, drop it safely:

  DROP DATABASE YourSnapshotName;

• Recreate it only if required, using the correct sparse file path:

  CREATE DATABASE YourSnapshotName ON 
  ( NAME = YourSourceDB_Data, FILENAME = 'C:\SQLData\YourSnapshotName.ss' )
  AS SNAPSHOT OF YourSourceDB;

• Ensure the file path exists and the SQL Server service account has write permissions on the target directory.

Step 4: Audit Maintenance Plans and Scheduled Jobs

• Open SQL Server Management Studio (SSMS) and navigate to SQL Server Agent > Jobs.
• Review any backup, integrity check, or shrink jobs to ensure they explicitly exclude snapshot databases (filter using sys.databases.source_database_id IS NULL in dynamic job scripts).
• Update any foreach database loops in PowerShell or T-SQL scripts to skip snapshots automatically.

Alternative or Verification Steps

• Verify the fix by re-running the original failed operation against the source database; it should complete without Error 5093.
• As an alternative, use sys.databases to generate a permanent exclusion list for snapshots in all automated maintenance scripts, preventing recurrence across Windows Server 2025 and Windows 11 SQL Server instances.

Troubleshooting Guide: SQL Server Error 5094

Why This Error Happens

Error 5094 occurs because SQL Server 2025 blocks certain operations—such as dropping a database, changing its file structure, or restoring over it—when one or more database snapshots exist against it. This restriction also applies when an active DBCC CHECKDB internal snapshot or replica is still being used for consistency checking. Essentially, SQL Server refuses to proceed because it detects dependent snapshot objects that must be removed first to avoid data corruption or orphaned snapshot files.

Step-by-Step Resolution Guide

Step 1: Identify Existing Database Snapshots

• Open SQL Server Management Studio (SSMS) and connect to the instance.
• Run the following query to list all snapshots tied to the source database:

SELECT name, database_id, source_database_id 
FROM sys.databases 
WHERE source_database_id = DB_ID('YourDatabaseName');

• Note the names of any returned snapshot databases, as these must be removed before proceeding.

Step 2: Drop the Database Snapshots

• For each snapshot identified in Step 1, run:

DROP DATABASE [YourDatabaseName_Snapshot01];

• Repeat this command for every snapshot listed in the query results.
• Avoid dropping the source database itself—only target the snapshot copies.

Step 3: Check for Active DBCC CHECKDB Processes

• Open Activity Monitor in SSMS or run sp_who2 to check for long-running sessions.
• Look specifically for sessions executing DBCC CHECKDB, DBCC CHECKTABLE, or DBCC CHECKFILEGROUP, as these create hidden internal snapshots (visible in the TEMPDB or source directory) while running.
• If an active replica is found, allow the DBCC process to complete naturally rather than killing the session abruptly, as forcing termination can leave orphaned snapshot files on disk.

Step 4: Retry the Original Operation

• Once all snapshots are dropped and no active DBCC sessions remain, retry the original command (e.g., DROP DATABASE, RESTORE DATABASE, or ALTER DATABASE).
• Ensure you are using a sysadmin or db_owner role account to avoid permission-related failures during the retry.

Alternative or Verification Steps

• Verify the fix: Re-run the query from Step 1 against sys.databases; if it returns zero rows for the source_database_id, the database is clear of snapshots and the operation should now succeed.
• Alternative solution: If the error persists despite no visible snapshots, restart the SQL Server service (via services.msc or net stop/start MSSQLSERVER) during a maintenance window to clear any lingering internal DBCC snapshot handles that haven’t been released from memory.

Troubleshooting Guide: SQL Server Error 5095

Why This Error Happens

This error occurs because SQL Server detects that one or more database files are still marked as “restoring” due to an incomplete RESTORE PAGE sequence, typically used for page-level corruption repair. SQL Server enforces a safety check preventing any transition to READ_ONLY mode while files remain in this intermediate state, since doing so could leave the database in an inconsistent or unrecoverable condition. This commonly happens when a DBA interrupts a restore sequence, forgets to apply the tail-log backup, or attempts to change database state scripts without verifying that all page-restore operations have been finalized with a successful RESTORE WITH RECOVERY.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Database and File State

• Open SQL Server Management Studio (SSMS) or a query window connected to your Windows Server 2025 instance.
• Run the following query to check restore status:

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

• Confirm the file(s) referenced in the error message (%ls) using:

  RESTORE HEADERONLY FROM DISK = 'C:\Backups\YourFile.bak';

Step 2: Complete the Pending RESTORE PAGE Sequence

• Locate the original backup chain used for the page-level restore operation.
• Apply any remaining differential or transaction log backups needed to bring the file current:

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

• Finalize the sequence with a recovery statement to bring the database out of restoring state:

  RESTORE DATABASE YourDatabaseName WITH RECOVERY;

Step 3: Verify Database Consistency Before Setting Read-Only

• Run DBCC CHECKDB to ensure the previously corrupted pages are now fully repaired:

  DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS;

• Check the Windows Server 2025 Event Viewer (Application Log) for any lingering SQL Server I/O or corruption-related warnings tied to Event ID 5095 or related MSSQLSERVER events.

Step 4: Reattempt the Read-Only Transition

• Once recovery is confirmed complete, execute the ALTER DATABASE command again:

  ALTER DATABASE YourDatabaseName SET READ_ONLY;

• If the command succeeds without error, the restore sequence has been properly closed out.

Alternative or Verification Steps

• Verify success by re-running SELECT is_read_only FROM sys.databases WHERE name = 'YourDatabaseName'; and confirming the value returns 1.
• If the error persists, restore the entire database from a known-good full backup (bypassing page-level restore) in a test environment to confirm no underlying backup chain corruption exists before reapplying read-only mode in production.

Troubleshooting Guide: SQL Server Error 5090 – Change Tracking Cannot Be Modified for System Databases

Why This Error Happens

This error occurs because SQL Server explicitly restricts Change Tracking configuration changes on system databases such as master, model, msdb, and tempdb. The engine enforces this restriction at a core level to protect internal metadata and prevent instability in system catalogs, regardless of the permissions or server role assigned to the executing login. This is a designed behavioral guardrail in SQL Server 2025, not a bug, corruption issue, or permissions gap—meaning no amount of elevated access will bypass it.

Step-by-Step Resolution Guide

Step 1: Identify the Target Database in Your Script

• Open the T-SQL script or application code that triggered the ALTER DATABASE ... SET CHANGE_TRACKING command.
• Check the database name referenced in the error message placeholder (%.*ls) to confirm it is indeed a system database (master, model, msdb, or tempdb).
• Use the following query to confirm database type:

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

Step 2: Redirect Change Tracking to a User Database

• Determine the actual business/user database that requires change tracking (e.g., SalesDB, InventoryDB).
• Run the enable command against the correct user database instead:

ALTER DATABASE SalesDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

• Ensure the database compatibility level is at least 90 or higher before enabling.

Step 3: Audit Automated Scripts or Deployment Pipelines

• Review SSDT projects, DACPAC deployments, or PowerShell/DevOps pipelines (common in Windows Server 2025 CI/CD setups) that may be looping through all databases in sys.databases.
• Add an exclusion filter in your scripts to skip system databases:

WHERE database_id > 4

• Update any SQL Agent Jobs or Maintenance Plans that blindly apply change tracking settings instance-wide.

Step 4: Restart the SQL Server Service (If Configuration Cache is Suspected)

• In rare cases involving cached metadata on Windows Server 2025, restart the SQL Server service via SQL Server Configuration Manager.
• Navigate to Services > SQL Server (MSSQLSERVER) and select Restart to clear any stale session configurations.

Alternative or Verification Steps

• Verify the fix: Run SELECT * FROM sys.change_tracking_databases; to confirm that only the intended user database appears in the output, and that the system database was never altered.
• Alternative workaround: If you specifically need to track internal system changes for auditing purposes, use SQL Server Audit or Extended Events instead of Change Tracking, as these features are supported on system databases and are purpose-built for engine-level monitoring.

Troubleshooting Guide: Error 5091 – ALTER DATABASE Change Tracking Option Specified More Than Once

Why This Error Happens

Error 5091 occurs when a single ALTER DATABASE statement enabling or modifying change tracking includes the same option — typically CHANGE_RETENTION or AUTO_CLEANUP — more than once within the WITH clause. This is usually caused by malformed or auto-generated T-SQL scripts, often produced by third-party migration tools, ORM frameworks, or copy-paste errors when combining multiple configuration templates. SQL Server’s parser strictly validates that each change tracking parameter appears only once per statement, and it will reject the entire batch rather than silently merging or overriding duplicate values.

Step-by-Step Resolution Guide

Step 1: Locate the Offending T-SQL Script

• Identify the exact ALTER DATABASE [YourDB] SET CHANGE_TRACKING = ON (...) statement that triggered Error 5091.
• Check SQL Server Management Studio (SSMS) query history, deployment scripts, or SSDT/DACPAC publish logs for the exact line number.
• If the error came from an automated job, review the SQL Server Agent job step or the CI/CD pipeline script (e.g., Azure DevOps, Flyway, Liquibase).

Step 2: Review and Clean the Syntax

• Open the script in SSMS or Azure Data Studio and inspect the WITH clause for duplicate parameters.
• Example of the incorrect syntax causing the error:

  ALTER DATABASE MyDB
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON, CHANGE_RETENTION = 7 DAYS);

• Correct the statement by removing the duplicate option, keeping only one instance of each parameter:

  ALTER DATABASE MyDB
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

Step 3: Re-run the Corrected Statement

• Execute the corrected ALTER DATABASE command in SSMS or via sqlcmd on Windows Server 2025 / Windows 11.
• Confirm the command completes with “Command(s) completed successfully” and no errors are returned.
• If running through a deployment pipeline, update the source script file (e.g., .sql migration file) to prevent recurrence on future deployments.

Step 4: Check for Duplicate Entries in Generated Scripts

• If the script was auto-generated (e.g., by SSDT, DbForge, or a schema comparison tool), regenerate the script after fixing the source schema definition to avoid reintroducing duplicates.
• Search the entire script file for repeated CHANGE_RETENTION or AUTO_CLEANUP keywords using a text editor’s find-all function (Ctrl+F) before re-deploying.

Alternative or Verification Steps

• Verify the fix by querying sys.change_tracking_databases to confirm the correct retention period and auto-cleanup setting were applied:

  SELECT database_id, is_auto_cleanup_on, retention_period, retention_period_units_desc
  FROM sys.change_tracking_databases;

• If the error persists despite a clean script, restart the SSMS query session or SQL Server client cache, as a stale cached execution plan from a prior failed batch can sometimes resubmit duplicate parameters.

Troubleshooting Guide: Error 5092 – Invalid Change Tracking Retention Value

Why This Error Happens

This error occurs when configuring the CHANGE_RETENTION option for Change Tracking via ALTER DATABASE and the specified numeric value falls outside the valid range accepted by SQL Server 2025. The retention period must be expressed in minutes, hours, or days, but internally SQL Server converts and validates this against a strict minimum and maximum threshold (typically 1 minute to a vendor-defined upper limit). This usually happens due to a scripting error, a copy-pasted T-SQL command with an incorrect unit conversion, or an automated deployment script that passes a miscalculated integer value.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Command Causing the Error

• Locate the ALTER DATABASE [YourDB] SET CHANGE_TRACKING = ON statement in your script, migration tool, or SSMS query history.
• Check the CHANGE_RETENTION clause specifically, e.g., CHANGE_RETENTION = 2 DAYS.
• Run the following to confirm current settings:

  SELECT * FROM sys.change_tracking_databases;

Step 2: Correct the Retention Value and Unit

• Ensure the numeric value is a positive integer and paired with a valid unit: MINUTES, HOURS, or DAYS.
• Avoid values of 0 or negative numbers, as these are rejected outright.
• Example of a corrected, valid statement:

  ALTER DATABASE [YourDB]
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);

• If using PowerShell or a deployment pipeline (common in Windows Server 2025 automated builds), verify the variable passed into the script isn’t being converted incorrectly (e.g., seconds mistaken for minutes).

Step 3: Apply the Change via SSMS or sqlcmd

• Open SQL Server Management Studio (SSMS) connected to your Windows Server 2025 instance.
• Execute the corrected ALTER DATABASE script against the target database.
• Alternatively, run via command line:

  sqlcmd -S localhost -d YourDB -Q "ALTER DATABASE [YourDB] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)"

Step 4: Restart Dependent Services (If Applicable)

• If Change Tracking is used by SQL Server Integration Services (SSIS) or a CDC-based sync job, restart the associated Windows Service via services.msc on Windows Server 2025.
• This ensures no cached configuration values trigger the same error on the next scheduled job run.

Alternative or Verification Steps

• Verify the fix by querying sys.change_tracking_databases and confirming the retention_period and retention_period_units_desc columns reflect your intended values.
• If the error persists despite correct syntax, check for conflicting Group Policy or SQL Server Configuration Manager settings on Windows Server 2025 that may restrict ALTER DATABASE permissions, and confirm your login has ALTER permission on the database (db_owner role or higher).

Troubleshooting Guide: SQL Server Error 5087 – File Content Type Mismatch

Why This Error Happens

Error 5087 occurs when SQL Server detects that a file being added, attached, or referenced does not match the expected content type of its target filegroup. This typically happens when attempting to add a regular data file (.mdf/.ndf) to a FILESTREAM filegroup, or vice versa, or when a database file has become mislabeled due to metadata corruption during a migration or restore. It can also occur after manually editing file properties in system catalog views or when attaching a database with inconsistent file-to-filegroup mappings.

Step-by-Step Resolution Guide

Step 1: Verify the Filegroup Type Before Adding Files

• Run the following query to inspect filegroup types in the target database:
• SELECT name, type, type_desc FROM sys.filegroups;
• Confirm whether the filegroup is ROWS, FILESTREAM, or MEMORY_OPTIMIZED_DATA before attempting to add a file.
• Ensure you are not trying to add a standard .ndf file to a FILESTREAM or MEMORY_OPTIMIZED_DATA filegroup.

Step 2: Correct the File Addition Command

• If adding a FILESTREAM file, ensure the filegroup was created with CONTAINS FILESTREAM:
• ALTER DATABASE YourDB ADD FILEGROUP FS_Group CONTAINS FILESTREAM;
• Then add the file using a directory path, not a physical file path:
• ALTER DATABASE YourDB ADD FILE (NAME = FS_Data, FILENAME = 'C:\SQLData\FSFolder') TO FILEGROUP FS_Group;
• For standard data files, ensure you are targeting a ROWS filegroup only.

Step 3: Check for Metadata Corruption in System Catalogs

• Query sys.database_files and sys.master_files to confirm consistency between file IDs and filegroup IDs:
• SELECT file_id, type_desc, data_space_id FROM sys.database_files;
• If mismatches are found (e.g., a FILESTREAM file pointing to a ROWS filegroup ID), the database metadata may be corrupted.
• Run DBCC CHECKDB('YourDB') WITH NO_INFOMSGS, ALL_ERRORMSGS; to identify broader consistency issues.

Step 4: Rebuild or Recreate the Problematic Filegroup

• If corruption is confirmed, create a new filegroup of the correct type:
• ALTER DATABASE YourDB ADD FILEGROUP NewFSGroup CONTAINS FILESTREAM;
• Migrate data using BCP, SELECT INTO, or FILESTREAM data export tools as appropriate.
• Remove the corrupted filegroup once data migration is verified:
• ALTER DATABASE YourDB REMOVE FILEGROUP OldFSGroup;

Step 5: Restart SQL Server Service and Re-Test

• On Windows Server 2025 / Windows 11, restart the SQL Server service via:
• Services.msc → SQL Server (MSSQLSERVER) → Restart
• Alternatively use PowerShell: Restart-Service -Name 'MSSQLSERVER'
• Re-attempt the original file/filegroup operation to confirm the error no longer appears.

Alternative or Verification Steps

• Verify the fix by re-running SELECT name, type_desc FROM sys.filegroups; and confirming the new file’s type_desc in sys.database_files matches the target filegroup type exactly.
• If the issue persists after metadata correction, restore the database from a known-good backup taken before the mismatch was introduced, then reapply any pending transactions via transaction log backups.

Troubleshooting Guide: SQL Server Error 5088 – Change Tracking Already Enabled

Why This Error Happens

Error 5088 occurs when a script or application attempts to execute ALTER DATABASE [DatabaseName] SET CHANGE_TRACKING = ON against a database that already has change tracking enabled at the database level. SQL Server 2025 strictly enforces idempotency checks on this DDL operation, meaning it will not silently ignore a duplicate request and instead throws this error to prevent configuration conflicts. This is typically caused by automated deployment scripts, CI/CD pipelines, or ORM migrations that do not check the current state of sys.change_tracking_databases before attempting to enable the feature.

Step-by-Step Resolution Guide

Step 1: Verify Current Change Tracking Status

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Run the following query to check if tracking is already active for the specific database:

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

• If a row is returned, change tracking is confirmed as active, validating the root cause of the error.

Step 2: Update Deployment Scripts with Conditional Logic

• Locate the specific .sql migration file or PowerShell DSC script causing the error (often found in C:\Scripts\Deployment\ or the CI/CD pipeline’s YAML configuration).
• Modify the script to include a conditional check to prevent re-execution on an already configured database:

IF NOT EXISTS (SELECT 1 FROM sys.change_tracking_databases WHERE database_id = DB_ID('YourDatabaseName'))
BEGIN
    ALTER DATABASE [YourDatabaseName] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
END

• Save the script and re-upload it to the deployment repository or local execution path.

Step 3: Clear Cached Execution Plans (If Error Persists)

• In rare cases involving Windows Server 2025 clustering, a stale cached plan might attempt to re-run the command.
• Execute DBCC FREEPROCCACHE; in a new query window to clear the plan cache.
• Restart the SQL Server Agent service via the Services console (services.msc) to ensure any pending jobs refresh their execution context.

Step 4: Check for Conflicting Agent Jobs

• Open SQL Server Agent in SSMS and expand the Jobs folder.
• Look for duplicate jobs that might be scheduled to run the same ALTER DATABASE command simultaneously (a common issue in Windows Server 2025 failover cluster instances).
• Disable or delete the redundant job to prevent the race condition that triggers Error 5088.

Alternative or Verification Steps

• Verification: Re-run the original command (ALTER DATABASE... SET CHANGE_TRACKING = ON); if the fix (Step 2) was successful, the script should now skip the execution silently without throwing Error 5088.
• Alternative: If you need to reset the tracking configuration entirely (e.g., to update retention settings), first disable it using ALTER DATABASE [YourDatabaseName] SET CHANGE_TRACKING = OFF; before attempting to turn it back on with new parameters.

Troubleshooting Guide: SQL Server Error 5089

Why This Error Happens

Error 5089 occurs when an attempt is made to modify change tracking settings (such as altering the retention period or auto-cleanup configuration) on a database where change tracking has not been enabled at the database level. SQL Server requires change tracking to be explicitly turned on via ALTER DATABASE ... SET CHANGE_TRACKING = ON before any table-level or settings-level modifications can be applied. This typically happens when a script or application assumes change tracking is already active, or when it was previously disabled (intentionally or due to a failover/restore operation) without updating dependent scripts.

Step-by-Step Resolution Guide

Step 1: Verify Current Change Tracking Status

• Open SQL Server Management Studio (SSMS) and connect to the target instance on Windows Server 2025.
• Run the following query to check the database state:

SELECT DB_NAME(database_id) AS DatabaseName, is_auto_cleanup_on, retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;

• If the target database does not appear in the result set, change tracking is confirmed to be disabled.

Step 2: Enable Change Tracking at the Database Level

• Execute the following T-SQL command, replacing YourDatabaseName with the actual database name:

ALTER DATABASE YourDatabaseName
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

• Ensure you have ALTER DATABASE permissions or are a member of the sysadmin or dbcreator fixed server role.
• Confirm there are no active transactions blocking the schema lock by checking sys.dm_tran_locks.

Step 3: Re-Apply the Original Change Tracking Settings

• Once the database-level setting is active, re-run the original command that caused the error (e.g., updating retention period or enabling tracking on a specific table).
• Example for enabling tracking on a table:

ALTER TABLE dbo.YourTableName
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

• If working in a high-availability setup (Always On), ensure the change is synchronized across all replicas.

Step 4: Restart the Dependent Service or Application

• If the error originated from an application (e.g., a sync framework or ETL tool like SSIS), restart the Windows Service or IIS Application Pool associated with it to clear any cached connection states.
• Use services.msc on Windows Server 2025 to restart the “SQL Server Agent” service if scheduled jobs reference change tracking.

Alternative or Verification Steps

• Verify the fix by re-running the query from Step 1; the database should now appear in sys.change_tracking_databases with the correct retention values.
• If the error persists after enabling change tracking, check for database compatibility level issues using SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; and ensure it is set to 130 or higher for full change tracking support in SQL Server 2025.

Why This Error Happens

Error 5085 occurs because SQL Server 2025 was launched with undocumented (hidden) trace flags—typically via startup parameters in SQL Server Configuration Manager or command-line switches—that explicitly block the engine from toggling database versioning states (such as ALLOWSNAPSHOTISOLATION or READCOMMITTEDSNAPSHOT). These trace flags are often enabled for internal diagnostics, replication scenarios, or legacy compatibility testing, but they override standard ALTER DATABASE behavior at a low level. Since the flags are undocumented, SQL Server refuses the ALTER DATABASE command outright rather than risk inconsistent versioning states in the version store.

Step-by-Step Resolution Guide

Step 1: Identify Active Trace Flags

• Open a query window and run DBCC TRACESTATUS(-1); to list all globally active trace flags.
• Cross-reference the output against known versioning-related flags (commonly in the 3xxx or 9xxx undocumented range used internally by Microsoft).
• Check the SQL Server Error Log (via EXEC xp_readerrorlog 0, 1, 'Trace Flag';) to confirm which flags were applied at startup.

Step 2: Locate and Edit Startup Parameters

• Open SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Navigate to SQL Server Services > right-click your instance (e.g., MSSQLSERVER) > Properties > Startup Parameters tab.
• Identify any parameter formatted as -T#### where #### matches an undocumented flag number flagged in Step 1.
• Remove the suspicious trace flag entry and click Add/Remove to apply the change.

Step 3: Restart the SQL Server Service

• In Configuration Manager, right-click the instance and select Restart.
• Alternatively, use PowerShell: Restart-Service -Name 'MSSQLSERVER' -Force.
• Confirm the service restarts cleanly without rollback or recovery errors in the log.

Step 4: Re-run the ALTER DATABASE Command

• Execute the original command again, for example:
  ALTER DATABASE [YourDB] SET ALLOW_SNAPSHOT_ISOLATION ON;
• Confirm no Error 5085 is returned and the command completes successfully.

Step 5: Audit Startup Scripts and Registry for Persistence

• Check HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\MSSQLServer\Parameters in the Windows Registry to ensure the flag wasn’t re-injected via automation or a deployment script.
• Remove any SQLArg entries referencing the removed trace flag to prevent recurrence on future reboots.

Alternative or Verification Steps

• Run DBCC TRACESTATUS(-1); again post-restart to confirm the specific trace flag no longer appears in the active list.
• If the flag is required for another business reason (e.g., vendor support), contact Microsoft Support to request documentation on flag compatibility with ALTER DATABASE versioning operations before re-enabling it.

Troubleshooting Guide: Error 5086 – Cannot Disable Vardecimal Storage Format

Why This Error Happens

This error occurs because SQL Server enforces a hierarchical dependency between database-level and table-level vardecimal storage settings. When the vardecimal storage format is enabled at the database level, individual tables can independently opt into this format, but SQL Server will not allow the database-level setting to be disabled while any table within it still has the format actively enabled. Essentially, the system is blocking the operation to prevent orphaned or inconsistent storage states between the database and its underlying tables.

Step-by-Step Resolution Guide

Step 1: Identify Affected Tables

• Run a query against the system catalog views to find every table still using the vardecimal format.
• Execute the following T-SQL command to pull the list of offending objects:

SELECT OBJECT_NAME(object_id) AS TableName 
FROM sys.tables 
WHERE uses_vardecimal_storage_format = 1;

• Note down every table name returned, as these must be addressed before touching the database setting.

Step 2: Disable Vardecimal Format on Each Table

• Use the system stored procedure sp_tableoption to turn off the setting for every table identified in Step 1.
• Run the following command for each table (replace YourTableName accordingly):

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

• If you have a large number of tables, consider generating a dynamic SQL script using a WHILE loop or a cursor to automate this for all tables simultaneously.

Step 3: Disable the Database-Level Setting

• Once all tables return 0 for uses_vardecimal_storage_format (verified by re-running the query from Step 1), proceed to disable the setting at the database level.
• Execute the following procedure, replacing YourDatabaseName with your specific database:

EXEC sp_db_vardecimal_storage_format 'YourDatabaseName', 'OFF';

• Ensure no active transactions are locking the tables during this process, as locks can cause the procedure to hang or time out.

Step 4: Restart Associated Services (If Required)

• In rare cases involving Windows Server 2025 environments with high availability (AlwaysOn) configurations, changes may not propagate immediately to replicas.
• Restart the SQL Server service via services.msc or use PowerShell:

Restart-Service -Name 'MSSQLSERVER'

• Verify that the SQL Server Agent service is also running to ensure any scheduled maintenance jobs don’t conflict with the schema change.

Alternative or Verification Steps

• Verification: Re-run the query SELECT * FROM sys.databases WHERE name = 'YourDatabaseName' and check the is_vardecimal_storage_format_enabled column to confirm it now reads False (0).
• Alternative: If sp_tableoption fails due to replication constraints, you may need to temporarily disable replication or Change Data Capture (CDC) on the specific tables before retrying the toggle, then re-enable it once the format change is successful.

Troubleshooting Guide: SQL Server Error 5082

Why This Error Happens

This error occurs when an ALTER DATABASE statement attempts to modify the system-versioning state (enabling or disabling temporal tables) at the same time as another database-level state change, such as altering the database’s READ_ONLY, ONLINE/OFFLINE, or compatibility level settings, within the same batch. SQL Server 2025 requires that versioning state transitions be isolated from other ALTER DATABASE operations because the engine needs to process schema and transaction log metadata for temporal tables in a dedicated transactional context. Combining these operations in a single statement creates a conflict in the internal state machine that governs database option changes, causing the command to be rejected outright.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting ALTER DATABASE Statement

• Review the T-SQL script or application code that triggered Error 5082.
• Look specifically for ALTER DATABASE [YourDB] SET ... WITH ... statements that combine multiple SET options in one call.
• Check SQL Server Error Logs via C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG to confirm the exact timestamp and statement context.

Step 2: Split the ALTER DATABASE Commands

• Separate the versioning state change into its own standalone transaction.
• Run the versioning command independently, for example:
• ALTER DATABASE [YourDB] SET SYSTEM_VERSIONING = ON;
• Execute any other pending state change (e.g., SET READ_ONLY or SET OFFLINE) as a separate, subsequent batch only after the first command completes successfully.

Step 3: Verify No Other Sessions Are Modifying Database State

• Open SQL Server Management Studio (SSMS) and run sp_who2 or query sys.dm_exec_requests to ensure no other session is holding a pending ALTER DATABASE lock.
• On Windows Server 2025, confirm no automated maintenance plans or scheduled jobs (via SQL Server Agent) are issuing concurrent ALTER DATABASE commands against the same database.

Step 4: Re-run the Corrected Script

• Execute the corrected, isolated T-SQL batch in SSMS or via sqlcmd.
• Confirm the command completes without returning Error 5082.

Alternative or Verification Steps

• Verify the fix by querying sys.databases and checking the is_temporal_history_table_enabled and state_desc columns to confirm the versioning state was applied cleanly.
• If the error persists, restart the SQL Server service (net stop MSSQLSERVER && net start MSSQLSERVER) to clear any stale internal state locks, then retry the isolated ALTER DATABASE commands.

Troubleshooting Guide: SQL Server Error 5083 — Termination Option Not Supported During Versioning State Changes

Why This Error Happens

This error occurs when an ALTER DATABASE command attempts to set the database to a new system-versioning or availability state (such as OFFLINE, RESTRICTED_USER, or EMERGENCY) while simultaneously passing a termination clause like WITH ROLLBACK IMMEDIATE or WITH ROLLBACK AFTER n SECONDS. SQL Server 2025 blocks termination options specifically when the state change intersects with temporal table versioning operations, because forcibly killing active transactions mid-versioning-change can corrupt history table consistency. This is typically triggered by legacy scripts or maintenance jobs that haven’t been updated for the new versioning engine constraints introduced in SQL Server 2025.

Step-by-Step Resolution Guide

Step 1: Identify the Offending T-SQL Statement

• Check SQL Server Agent job history or the application’s deployment scripts for ALTER DATABASE statements.
• Look specifically for syntax combining versioning state changes (e.g., SET SYSTEM_VERSIONING = ON/OFF) with WITH ROLLBACK IMMEDIATE or WITH ROLLBACK AFTER.
• Review the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for the exact timestamp and query context tied to Error 5083.

Step 2: Separate the Termination Clause from the Versioning Command

• Rewrite the script to execute the state change and the termination option as two distinct, sequential statements.
• First, run the termination/isolation command alone:

  ALTER DATABASE [YourDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

• Once the database is in the desired state, run the versioning change separately without any termination clause:

  ALTER TABLE [dbo].[YourTable] SET (SYSTEM_VERSIONING = OFF);

Step 3: Verify Active Sessions and Locks

• Before retrying, query sys.dm_exec_sessions and sys.dm_tran_active_transactions to confirm no long-running transactions are holding locks on the temporal table or its history table.
• Manually terminate blocking sessions using KILL <session_id> if necessary, rather than relying on the ROLLBACK clause to do it implicitly.
• Re-run the versioning state change command now that the termination has already been handled in Step 2.

Step 4: Update Automation Scripts and Maintenance Plans

• Audit SQL Server Agent jobs, SSIS packages, or PowerShell deployment scripts (Invoke-Sqlcmd) for the deprecated combined syntax.
• Update them to follow the two-step pattern (isolate database, then alter versioning) to prevent recurrence in future deployment cycles.

Alternative or Verification Steps

• Verify the fix: Run SELECT name, temporal_type_desc FROM sys.tables WHERE temporal_type_desc <> 'NON_TEMPORAL_TABLE'; alongside SELECT state_desc FROM sys.databases WHERE name = 'YourDB'; to confirm both the database state and versioning status updated successfully without throwing Error 5083.
• Alternative approach: If the two-step method still fails, place the database in SINGLE_USER mode first (without a termination clause attached to the versioning command), perform the versioning change, then revert to MULTI_USER mode.

Troubleshooting Guide: SQL Server Error 5084

Why This Error Happens

Error 5084 occurs when SQL Server 2025 attempts to change a database option (such as AUTO_CLOSE, READ_ONLY, SINGLE_USER, or COMPATIBILITY_LEVEL) but fails to complete the operation. This is typically caused by the database being in an inconsistent state, active connections preventing an exclusive lock required for the ALTER DATABASE statement, or insufficient permissions on the service account. Corrupted system catalog metadata or a pending database recovery process can also block the option change from committing successfully.

Step-by-Step Resolution Guide

Step 1: Verify Active Connections and Session Locks

• Open SQL Server Management Studio (SSMS) and run SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('YourDatabaseName'); to identify blocking sessions.
• If connections exist, set the database to single-user mode first using ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• Terminate orphaned sessions manually using KILL [session_id] if rollback does not clear them.

Step 2: Check and Repair Service Account Permissions

• Navigate to SQL Server Configuration Manager on Windows Server 2025 / Windows 11.
• Confirm the SQL Server service account has db_owner or sysadmin rights on the target database.
• Validate NTFS permissions on the database files located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\, ensuring the service account has Full Control.

Step 3: Run Database Consistency Checks

• Execute DBCC CHECKDB('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS; to detect metadata or page-level corruption.
• If errors are returned, run DBCC CHECKDB('YourDatabaseName', REPAIR_REBUILD); in single-user mode.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER in PowerShell after repairs complete.

Step 4: Reapply the Database Option Change

• Reconnect via SSMS and re-run the original ALTER DATABASE statement that triggered Error 5084.
• Monitor the SQL Server Error Log at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for confirmation messages or recurring failures.
• Set the database back to multi-user mode using ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Alternative or Verification Steps

• Confirm the fix by querying SELECT name, is_read_only, is_auto_close_on FROM sys.databases WHERE name = 'YourDatabaseName'; to verify the option was successfully applied.
• If the issue persists, restore the database from the most recent clean backup using RESTORE DATABASE [YourDatabaseName] FROM DISK = 'BackupPath.bak' WITH REPLACE; and reapply configuration changes.

Troubleshooting Guide: Error 5080 – Vardecimal Storage Format Cannot Be Disabled

Why This Error Happens

This error occurs because SQL Server 2025 requires a database to be in the Simple Recovery Model before it will allow the vardecimal storage format to be disabled. The restriction exists because disabling vardecimal storage is a metadata-only operation that conflicts with how the Full or Bulk-Logged recovery models handle transaction log sequencing for row-format changes. Essentially, the engine blocks the command to prevent inconsistencies between the data page format and the active transaction log chain.

Step-by-Step Resolution Guide

Step 1: Verify the Current Recovery Model

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

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

• Confirm the recovery_model_desc column shows FULL or BULK_LOGGED rather than SIMPLE.

Step 2: Back Up the Transaction Log (Critical Pre-Check)

• Before switching recovery models, take a final transaction log backup to avoid breaking your log backup chain (especially important in production environments on Windows Server 2025).
• Navigate to your backup directory (e.g., D:\SQLBackups\TransactionLogs\) and run:

BACKUP LOG [YourDatabaseName] 
TO DISK = 'D:\SQLBackups\TransactionLogs\YourDatabaseName_Final.trn';

• Skip this step only if the database is non-production or log history is not required.

Step 3: Change the Recovery Model to Simple

• Execute the following T-SQL command to switch the model:

ALTER DATABASE [YourDatabaseName] 
SET RECOVERY SIMPLE;

• Wait for the “Command(s) completed successfully” confirmation message in the query execution pane.

Step 4: Reissue the Vardecimal Disable Command

• Run the original command that triggered Error 5080:

EXEC sp_db_vardecimal_storage_format 'YourDatabaseName', 'OFF';

• The command should now execute without throwing the 5080 error since the database is compliant with the Simple Recovery Model requirement.

Step 5: Restore Recovery Model (If Required)

• If your business continuity requirements mandate Full Recovery (e.g., for Point-in-Time restores), revert the setting immediately after the vardecimal operation completes:

ALTER DATABASE [YourDatabaseName] 
SET RECOVERY FULL;

• Take an immediate full backup afterward to restart the log chain properly.

Alternative or Verification Steps

• Verify the Fix: Re-run SELECT name, is_db_vardecimal_storage_format_on FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the value returns 0 (disabled).
• Alternative Approach: If changing the recovery model is not feasible due to compliance policies, consider leaving vardecimal storage format enabled, as it is deprecated in favor of native data compression in modern SQL Server versions and generally does not need to be manually disabled.

Troubleshooting Guide: SQL Server Error 5081 – Invalid Change Tracking Option Value

Why This Error Happens

This error occurs when the CHANGE_RETENTION or related change tracking parameter is supplied with a non-numeric, negative, zero, or improperly formatted value during an ALTER DATABASE or ALTER TABLE statement. SQL Server 2025 strictly validates these parameters at parse time, and any value that isn’t a positive integer—including values passed as strings, variables with incorrect data types, or NULL—will trigger Error 5081. This is commonly caused by dynamic SQL scripts that concatenate user input or configuration values without proper type casting or validation.

Step-by-Step Resolution Guide

Step 1: Identify the Exact Statement Causing the Error

• Check SQL Server Management Studio (SSMS) or the application log for the specific T-SQL statement that triggered Error 5081.
• Look for ALTER DATABASE [YourDB] SET CHANGE_TRACKING = ON or ALTER TABLE [YourTable] ENABLE CHANGE_TRACKING commands.
• Review any stored procedures or ORM-generated scripts (e.g., Entity Framework migrations) that build these statements dynamically.

Step 2: Validate and Correct the CHANGE_RETENTION Value

• Ensure the value passed to CHANGE_RETENTION is a positive integer greater than zero, such as 2 DAYS or 7 DAYS.
• Avoid passing variables of type NVARCHAR or VARCHAR without explicit conversion—use CAST() or CONVERT() to INT before use.
• Example of a corrected statement:

  ALTER DATABASE [YourDB]
  SET CHANGE_TRACKING = ON
  (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)

Step 3: Check for Hidden Whitespace or Encoding Issues in Config Files

• If the value originates from an external configuration file (e.g., appsettings.json, XML config, or PowerShell script), open the file in a plain text editor to check for trailing spaces, tabs, or invisible Unicode characters.
• On Windows Server 2025, use Get-Content -Encoding UTF8 in PowerShell to inspect the raw file content for corruption.
• Re-save the file using UTF-8 encoding without BOM to eliminate parsing inconsistencies.

Step 4: Update SQL Server Cumulative Updates

• Navigate to Control Panel > Programs > Programs and Features on Windows Server 2025 and confirm the current SQL Server 2025 build number.
• Download and apply the latest Cumulative Update (CU) from the Microsoft Update Catalog, as early 2025 builds had a known parsing bug affecting numeric validation in change tracking options.
• Restart the SQL Server service via services.msc or Restart-Service MSSQLSERVER after installing the update.

Alternative or Verification Steps

• Run sys.change_tracking_databases and sys.change_tracking_tables system views to confirm the retention value was applied correctly without errors.
• If the issue persists after validation, disable and re-enable change tracking on the affected table/database using hardcoded literal values instead of variables to isolate whether the problem is in your application’s parameter-passing logic.

Troubleshooting Guide: SQL Server Error 5077

Why This Error Happens

This error occurs when attempting to execute an ALTER DATABASE statement with a MODIFY FILE or file state-changing clause against a file that belongs to the primary filegroup or is a non-data file (such as a log file). SQL Server restricts state changes—like taking files OFFLINE or marking them READONLY—on primary filegroup members because doing so would compromise access to system metadata and core database objects. This typically happens when a script intended for a secondary filegroup is mistakenly pointed at the primary filegroup, or when automation/maintenance scripts fail to filter out log and primary data files before issuing file-state commands.

Step-by-Step Resolution Guide

Step 1: Identify the Target File and Filegroup

• Open SQL Server Management Studio (SSMS) or a query window connected to the affected instance.
• Run the following query to inspect file and filegroup metadata:

  SELECT name, type_desc, data_space_id, state_desc 
  FROM sys.database_files;

• Confirm whether the file you are trying to modify has data_space_id = 1 (primary filegroup) or type_desc = 'LOG'.

Step 2: Correct the Target of Your ALTER DATABASE Command

• If your intent was to change the state of a secondary filegroup, update your script to target the correct data_space_id or filegroup name.
• Example of a valid command on a non-primary filegroup:

  ALTER DATABASE [YourDB] 
  MODIFY FILEGROUP [SecondaryFG] READONLY;

• Avoid issuing OFFLINE, READONLY, or similar state-changing commands against files where data_space_id = 0 (log files) or data_space_id = 1 (primary).

Step 3: Review Maintenance Scripts and Automation Jobs

• Check SQL Server Agent jobs or PowerShell/dbatools scripts that loop through all database files for file-state maintenance (e.g., bulk READONLY operations for data archiving).
• Insert a filter to exclude primary filegroup and log files:

  WHERE data_space_id NOT IN (0, 1)

• Save and re-test the modified script on a non-production instance first.

Step 4: Restart the Operation on the Correct Scope

• If the goal was to make the entire database read-only (not just a filegroup), use the database-level command instead:

  ALTER DATABASE [YourDB] SET READ_ONLY;

• Ensure no active connections are blocking the change by setting SINGLE_USER mode temporarily if needed.

Alternative or Verification Steps

• Re-run SELECT name, state_desc FROM sys.database_files; after applying changes to confirm the targeted secondary filegroup now reflects the desired state (e.g., READONLY or OFFLINE), while the primary filegroup remains ONLINE.
• If bulk automation continues to trigger Error 5077, add a TRY...CATCH block around the ALTER DATABASE statement to log and skip primary/log files automatically instead of halting the entire job.

Troubleshooting Guide: SQL Server Error 5078 – “Cannot Alter Database Options Because It Is READONLY”

Why This Error Happens

This error occurs when SQL Server 2025 attempts to modify database configuration options while the database is in READONLY mode, which locks the system catalog and metadata from write operations. This typically happens when a database was restored from a read-only backup, placed in READONLY mode intentionally for reporting workloads, or when the underlying filegroup containing the primary data file has its READONLY attribute set at the Windows filesystem level. Additionally, on Windows Server 2025, this can be triggered by NTFS permission inheritance issues or if the database file resides on a storage volume mounted as read-only.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or use sqlcmd to check the database status.
• Run the following query: SELECT name, is_read_only, state_desc FROM sys.databases WHERE name = 'YourDatabaseName';
• Confirm that is_read_only returns 1, which validates the root cause before proceeding.

Step 2: Check Filesystem-Level Read-Only Attributes

• Navigate to the physical data file location, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .mdf and .ldf files, select Properties, and ensure the “Read-only” checkbox under the General tab is unchecked.
• Alternatively, run this command in an elevated PowerShell window: Get-ItemProperty -Path "C:\...\YourDatabase.mdf" | Select-Object IsReadOnly

Step 3: Set the Database to READ_WRITE Mode

• Ensure no active connections are using the database before proceeding.
• Execute the following T-SQL command in SSMS with sysadmin privileges:

ALTER DATABASE YourDatabaseName SET READ_WRITE WITH ROLLBACK IMMEDIATE;

• This forces all transactions to roll back and immediately switches the database to a writable state.

Step 4: Validate Windows Service Account Permissions

• Confirm that the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Modify and Write permissions on the data directory.
• Open services.msc, locate the SQL Server service, and verify the “Log On As” account matches the account with correct NTFS permissions.
• Restart the SQL Server service if permissions were changed: Restart-Service -Name 'MSSQLSERVER'

Step 5: Rebuild Database Options via SSMS GUI (If T-SQL Fails)

• Right-click the database in Object Explorer and select Properties.
• Navigate to the “Options” page and manually change the “Database Read-Only” dropdown from “True” to “False.”
• Click OK to apply changes; SSMS will internally execute the ALTER DATABASE command.

Alternative or Verification Steps

• Re-run the verification query from Step 1 to confirm is_read_only now returns 0, then attempt the original ALTER DATABASE command that triggered Error 5078.
• If the issue persists, check for Availability Group or Log Shipping configurations that may be forcing the database into a read-only secondary state, as these require failover or configuration changes at the AG level rather than direct ALTER DATABASE commands.

Why This Error Happens

Error 5079 occurs because the vardecimal storage format setting is a database-level property that conflicts with the current state of the database or its compatibility level. This typically happens when attempting to enable vardecimal storage on a database that is in an incompatible state (such as being offline, in single-user mode, or already in a transitional status), or when the database compatibility level does not support this legacy feature. Since vardecimal storage format is deprecated in modern SQL Server versions (replaced by native data compression), attempting to toggle it on newer engine versions like SQL Server 2025 often triggers this informational or blocking message.

Step-by-Step Resolution Guide

Step 1: Verify Database State and Compatibility Level

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

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

• Ensure the database state is ONLINE before proceeding.

Step 2: Check Vardecimal Feature Deprecation Status

• Confirm whether vardecimal storage is still applicable, as it has been deprecated since SQL Server 2016 in favor of row/page compression.
• Run sp_db_vardecimal_storage_format to check the current setting:

  EXEC sp_db_vardecimal_storage_format 'YourDatabaseName';

• If the database compatibility level is 130 or higher, consider migrating to standard data compression instead.

Step 3: Set Database to Single-User Mode (If Required)

• If the error indicates the database is “in transition” or locked by another process, force single-user mode:

  ALTER DATABASE YourDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

• Retry the vardecimal storage format change:

  EXEC sp_db_vardecimal_storage_format 'YourDatabaseName', 'ON';

• Revert back to multi-user mode once the operation completes:

  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 4: Apply Native Data Compression as an Alternative

• Since vardecimal is a legacy feature, use ALTER TABLE with REBUILD and DATA_COMPRESSION options instead:

  ALTER TABLE YourTableName REBUILD WITH (DATA_COMPRESSION = ROW);

• This achieves similar storage savings without relying on deprecated functionality.

Alternative or Verification Steps

• Verify the fix by re-running EXEC sp_db_vardecimal_storage_format 'YourDatabaseName'; and confirming the output shows ON or OFF without error.
• If the error persists, check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for underlying blocking sessions, and consider migrating the table schema to use DATA_COMPRESSION as a long-term replacement for vardecimal storage.

Troubleshooting Guide: SQL Server Error 5074

Why This Error Happens

Error 5074 occurs when you attempt to modify or drop a column, default, or constraint that another database object—such as a computed column, index, statistics object, or CHECK constraint—explicitly depends on. SQL Server 2025 enforces strict dependency validation to prevent orphaned references, so the ALTER or DROP statement is blocked until the dependent object is addressed. This commonly happens during schema migrations or default/constraint changes when the dependency chain wasn’t mapped out beforehand.

Step-by-Step Resolution Guide

Step 1: Identify the Dependent Objects

• Run sys.dm_sql_referencing_entities or sys.sql_expression_dependencies against the target object to list all dependencies.
• Example query: SELECT * FROM sys.dm_sql_referencing_entities('dbo.YourTable', 'OBJECT');
• Note whether the dependency is a computed column, index, default constraint, or check constraint.

Step 2: Drop the Dependent Object Temporarily

• Script out the definition of the dependent object (e.g., index or constraint) using SSMS 21’s “Generate Script” feature before dropping it.
• Execute DROP INDEX, DROP CONSTRAINT, or ALTER TABLE ... DROP COLUMN for the dependent object only, not the base object.
• Confirm no other objects reference this dependent object using the same DMV query from Step 1.

Step 3: Apply the Original Schema Change

• Re-run your original ALTER TABLE, ALTER COLUMN, or DROP COLUMN/DEFAULT statement that originally triggered Error 5074.
• Verify the statement completes without error in SSMS 21’s Messages pane.

Step 4: Recreate the Dependent Object

• Use the script saved in Step 2 to recreate the index, constraint, or computed column against the newly modified schema.
• Adjust the definition if the underlying column name, data type, or expression changed.
• Execute the script in a transaction (BEGIN TRAN ... COMMIT) so you can roll back if recreation fails.

Alternative or Verification Steps

• Re-run sys.sql_expression_dependencies against the modified object to confirm all dependencies resolve correctly and no broken references remain.
• If recreating the dependent object fails, use sp_helptext or Extended Events to capture the exact original definition before retrying the schema change.

Troubleshooting Guide: SQL Server Error 5075 – Database Collation Dependency Conflict

Why This Error Happens

This error occurs when you attempt to change a database’s collation while schema-bound objects—such as views, functions, or computed columns created with the SCHEMABINDING option—still reference it. SQL Server 2025 enforces strict dependency checks because schema-bound objects lock in metadata (including collation behavior) at creation time to guarantee deterministic query results. Until every dependency is removed or altered, the engine refuses the ALTER DATABASE ... COLLATE statement to prevent breaking object integrity.

Step-by-Step Resolution Guide

Step 1: Identify the Dependent Objects

• Run the following query to list schema-bound objects tied to the database collation:

  SELECT referencing_schema_name, referencing_entity_name, referencing_class_desc
  FROM sys.dm_sql_referencing_entities('YourDatabaseName', 'DATABASE');

• Cross-check with sys.sql_expression_dependencies for views and functions using WITH SCHEMABINDING.
• Note down every object name and type (view, function, computed column, check constraint).

Step 2: Remove or Modify Schema-Bound Dependencies

• Script out the definitions of all dependent objects before dropping them (use SSMS “Script Object as DROP and CREATE To”).
• Drop the schema-bound objects using:

  DROP VIEW dbo.YourSchemaBoundView;
  DROP FUNCTION dbo.YourSchemaBoundFunction;

• For computed columns referencing collation-sensitive expressions, use ALTER TABLE ... DROP COLUMN temporarily.

Step 3: Change the Database Collation

• Execute the collation change once all dependencies are cleared:

  ALTER DATABASE YourDatabaseName COLLATE SQL_Latin1_General_CP1_CI_AS;

• Ensure no active connections are using the database during this operation (set to SINGLE_USER mode if needed).
• Confirm the change completed without error messages in SSMS or the SQL Server Error Log.

Step 4: Recreate the Dropped Objects

• Re-run the CREATE scripts saved in Step 2 to restore views, functions, and computed columns.
• Verify the objects now inherit the new collation setting correctly.
• Test application queries that depend on these objects to confirm expected behavior.

Alternative or Verification Steps

• Verify the fix by querying sys.databases and confirming the collation_name column reflects the new collation value for your target database.
• If recreating objects fails due to lingering references, use sp_refreshsqlmodule on each dependent object to force SQL Server to re-validate and align metadata with the new collation.

Troubleshooting Guide: SQL Server Error 5076 – “Warning: Changing Default Collation for Database”

Why This Error Happens

This warning occurs when an ALTER DATABASE statement attempts to change the default collation of a database, which SQL Server 2025 flags because it impacts how string comparisons, sorting, and indexing behave for all objects within that database. The root cause is typically a mismatch between the collation of the model database, tempdb, or the instance-level collation versus the target database, often triggered during a restore, migration, or manual schema change. This is compounded when existing indexes, computed columns, or constraints on character-based columns were built using the original collation, making the switch structurally risky rather than purely cosmetic.

Step-by-Step Resolution Guide

Step 1: Identify the Current and Target Collations

• Run SELECT name, collation_name FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the active collation.
• Run SELECT SERVERPROPERTY('Collation'); to check the instance-level default collation for comparison.
• Document any mismatch between the database, instance, and tempdb collations before proceeding.

Step 2: Back Up the Database Before Making Changes

• Open SQL Server Management Studio (SSMS) 21 or use PowerShell/T-SQL on Windows Server 2025.
• Execute BACKUP DATABASE [YourDatabaseName] TO DISK = 'C:\Backups\YourDatabaseName_PreCollationChange.bak' WITH INIT, COMPRESSION;
• Verify backup integrity using RESTORE VERIFYONLY FROM DISK = 'C:\Backups\YourDatabaseName_PreCollationChange.bak';

Step 3: Drop Dependent Objects That Block the Collation Change

• Identify dependencies using sp_help or querying sys.indexes and sys.foreign_keys tied to character columns.
• Script out and temporarily drop indexes, computed columns, full-text catalogs, and constraints referencing affected columns.
• Keep the generated DROP/CREATE scripts in a .sql file for rollback purposes.

Step 4: Apply the Collation Change

• Run the command: ALTER DATABASE [YourDatabaseName] COLLATE SQL_Latin1_General_CP1_CI_AS; (replace with your intended target collation).
• Acknowledge Error 5076 as an informational warning rather than a blocking error if dependencies were cleared in Step 3.
• Recreate the indexes, constraints, and computed columns using the scripts saved earlier.

Step 5: Rebuild tempdb Alignment (If Applicable)

• Since tempdb inherits the instance collation, confirm compatibility by running SELECT collation_name FROM sys.databases WHERE name = 'tempdb';
• If temp tables or stored procedures reference the changed database, add explicit COLLATE DATABASE_DEFAULT clauses in join conditions to prevent collation conflict errors (Error 468) post-change.

Alternative or Verification Steps

• Verify the fix by re-running SELECT name, collation_name FROM sys.databases WHERE name = 'YourDatabaseName'; and confirming the output matches your intended target collation, then test a sample query joining temp tables to ensure no collation conflict errors arise.
• If the ALTER DATABASE command fails due to active connections, set the database to single-user mode first using ALTER DATABASE [YourDatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;, perform the change, then revert with SET MULTI_USER.

Troubleshooting Guide: Error 5072 — ALTER DATABASE Default Collation Cannot Be Set

Why This Error Happens

This error occurs because SQL Server 2025 cannot apply the requested collation to the database, typically due to an invalid or unsupported collation name, a mismatch between the collation and the database’s current Unicode/version compatibility level, or because active connections/schema-bound objects are referencing collation-dependent data. It can also occur if the collation name was mistyped or references a Windows collation that is deprecated or not installed on the current OS build of Windows Server 2025 / Windows 11.

Step-by-Step Resolution Guide

Step 1: Validate the Collation Name Syntax

• Run SELECT name FROM sys.fn_helpcollations(); to confirm the exact, case-sensitive collation name exists on this instance.
• Compare your target collation string character-by-character against the query output — a common cause is a typo (e.g., SQL_Latn1_General instead of SQL_Latin1_General).
• Confirm you are not mixing a Windows collation with a binary suffix that is no longer supported in SQL Server 2025.

Step 2: Close Active Connections and Remove Dependent Objects

• Collation changes fail if other sessions hold a connection to the database; run ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; before retrying.
• Check for schema-bound views, indexed computed columns, or full-text indexes tied to string columns — these must be dropped or altered first using sp_helptext and sys.sql_expression_dependencies.
• Re-run the ALTER DATABASE statement immediately after cleanup, since SINGLE_USER mode will be reverted by other connections otherwise.

Step 3: Verify Compatibility Level and Database State

• Confirm the database compatibility level supports the target collation: SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB';
• If running an older compatibility level (e.g., 120 or below), run ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 160; to align with SQL Server 2025 features.
• Ensure the database is not in RESTORING, SUSPECT, or OFFLINE state by checking sys.databases.state_desc.

Step 4: Apply the Collation Change and Reset Multi-User Mode

• Execute ALTER DATABASE [YourDB] COLLATE SQL_Latin1_General_CP1_CI_AS; (replace with your validated collation name from Step 1).
• Once successful, restore normal access with ALTER DATABASE [YourDB] SET MULTI_USER;.
• Monitor SQL Server Error Log via EXEC sp_readerrorlog 0, 1, 'collation'; to confirm no secondary warnings were logged.

Alternative or Verification Steps

• Verify the fix by running SELECT DATABASEPROPERTYEX('YourDB', 'Collation'); and confirming the output matches your intended collation string.
• If the ALTER still fails, create a new database with the desired collation and migrate data using the Import/Export Wizard or bcp, since this bypasses in-place collation conflicts entirely.

Why This Error Happens

Error 5073 occurs when SQL Server 2025 attempts to modify a database’s collation while the database is in a READONLY state, which prevents any schema-level or metadata changes. This commonly happens because the database was explicitly set to READONLY for reporting/archival purposes, or because the underlying storage (such as an Azure file share, replica, or secondary availability group node) enforces read-only access. Collation changes require write access to system catalog views, so the operation is blocked at the engine level before any actual collation logic is executed.

Step-by-Step Resolution Guide

Step 1: Verify the Current Database State

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and connect to the instance.
• Run the following query to confirm READONLY status:

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

• Confirm is_read_only returns 1, validating this is the root cause.

Step 2: Check for Active Connections or AG Role Restrictions

• If the database is part of an Always On Availability Group, confirm whether it is a secondary replica (secondaries are read-only by design and cannot be altered directly).
• Use sys.dm_hadr_database_replica_states to check is_primary_replica.
• If this is a standalone instance, ensure no other session has forced READONLY via application intent connection strings (ApplicationIntent=ReadOnly).

Step 3: Set the Database to READ_WRITE Mode

• Switch the database into single-user mode to avoid conflicts, then change its state:

  ALTER DATABASE YourDatabaseName 
  SET READ_WRITE WITH ROLLBACK IMMEDIATE;

• This forcibly terminates other connections and flips the database into a writable state.

Step 4: Apply the Collation Change

• Once the database reports READ_WRITE, run the intended collation command:

  ALTER DATABASE YourDatabaseName 
  COLLATE SQL_Latin1_General_CP1_CI_AS;

• Confirm no errors are returned and the command completes successfully.

Step 5: Restore Original READONLY Setting (If Required)

• If the database must remain READONLY for compliance or reporting reasons, revert it back after the collation change:

  ALTER DATABASE YourDatabaseName 
  SET READ_ONLY WITH ROLLBACK IMMEDIATE;

• Ensure applications depending on read-only access resume normal connectivity.

Alternative or Verification Steps

• Re-run SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation'); to confirm the new collation value has been applied correctly.
• If the database is part of an Availability Group and cannot be altered directly, perform the collation change on the primary replica only, then allow the change to propagate naturally to secondaries via log synchronization.

Troubleshooting Guide: SQL Server Error 5069 – ALTER DATABASE Statement Failed

Why This Error Happens

Error 5069 is a generic “wrapper” error raised by SQL Server 2025 when an underlying statement within the ALTER DATABASE command fails to execute. The actual root cause is almost always reported in a preceding error message (such as insufficient permissions, a file already in use, an invalid file path, or a database currently in a state that prevents modification). On Windows Server 2025 / Windows 11, this is frequently triggered by locked database files, a database stuck in SINGLE_USER mode, or the SQL Server service account lacking NTFS permissions on the target directory.

Step-by-Step Resolution Guide

Step 1: Identify the Preceding Error Message

• Open SQL Server Management Studio (SSMS) and re-run the ALTER DATABASE command.
• Check the Messages tab for the error immediately above 5069 (e.g., Error 5070, 5172, or 1105) — this identifies the true root cause.
• Review the SQL Server Error Log located at C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG for additional context.

Step 2: Verify Database State and Active Connections

• Run the following query to check if the database is locked in a restrictive state:

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

• If the database is stuck in SINGLE_USER or RESTRICTED_USER mode, kill blocking sessions using KILL <session_id>.
• Set the database back to multi-user mode if needed:

  ALTER DATABASE YourDatabaseName SET MULTI_USER;

Step 3: Check File Permissions and Service Account Rights

• Navigate to the data/log file directory, typically C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\.
• Right-click the .mdf or .ldf file, go to Properties > Security, and confirm the SQL Server service account (e.g., NT Service\MSSQLSERVER) has Full Control.
• If permissions are missing, click Edit > Add, enter the service account name, and grant Full Control, then click Apply.

Step 4: Restart the SQL Server Service to Release File Locks

• Open Services.msc on Windows Server 2025 / Windows 11.
• Locate SQL Server (MSSQLSERVER), right-click, and select Restart.
• This releases any orphaned file handles caused by crashed sessions or external backup tools (e.g., antivirus scanning the .mdf file).

Alternative or Verification Steps

• Verify the fix by re-running the original ALTER DATABASE statement; a successful execution returns “Command(s) completed successfully” with no preceding error codes.
• If the issue persists, run DBCC CHECKDB('YourDatabaseName') to rule out file corruption as the underlying cause of the failed alteration.

Why This Error Happens

SQL Server Error 5070 occurs when an administrator attempts to change a database’s state (such as setting it to SINGLE_USER, OFFLINE, or restoring it) while active connections or open transactions still exist against that database. The ALTER DATABASE command requires exclusive access to apply state changes, and SQL Server will not forcibly terminate sessions unless explicitly instructed to do so. This is commonly triggered by lingering application connection pools, open SSMS query windows, or background services (like SQL Server Agent jobs or reporting services) that maintain a live session to the target database.

Step-by-Step Resolution Guide

Step 1: Identify Active Connections to the Database

• Open a new query window in SQL Server Management Studio (SSMS) connected to the master database.
• Run the following query to view current sessions:

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

• Note any session_id values other than your own; these are blocking the state change.

Step 2: Terminate Blocking Sessions Manually

• For each listed session_id, run a KILL command to forcibly disconnect the session:

KILL <session_id>;

• Confirm in Task Manager or Services (services.msc) that no local application (IIS, SSRS, or a custom Windows Service) is configured to auto-reconnect immediately after being killed.

Step 3: Force Single-User Mode with Rollback

• Instead of manually killing each session, use the WITH ROLLBACK IMMEDIATE clause to let SQL Server handle disconnection and transaction rollback automatically:

ALTER DATABASE [YourDatabaseName] 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;

• This command will terminate all open transactions and connections instantly, allowing the state change to proceed without needing to find each session manually.

Step 4: Apply Your Original State Change

• Once the database is in SINGLE_USER mode, execute the restore, detach, or configuration change you originally intended.
• Example for taking the database offline:

ALTER DATABASE [YourDatabaseName] SET OFFLINE;

• Revert to MULTI_USER mode once the maintenance task is complete:

ALTER DATABASE [YourDatabaseName] SET MULTI_USER;

Alternative or Verification Steps

• Verify the fix by re-running the sys.dm_exec_sessions query from Step 1; if the result set is empty (aside from your own connection), the database is fully accessible for state changes.
• If the error persists even after using ROLLBACK IMMEDIATE, check for an orphaned connection from a linked server or a stuck Windows Service in services.msc—restarting the specific service (e.g., SQL Server Agent) often releases a connection handle that SSMS cannot otherwise detect.

Why This Error Happens

Error 5071 occurs when the ALTER DATABASE ... REBUILD LOG statement is executed with more than one file specified in the FILE clause. SQL Server’s rebuild log operation is designed to recreate a single transaction log file to resolve a missing or corrupted log, and it strictly enforces a one-file-per-command syntax rule. This typically happens when administrators copy multi-file syntax templates (used for data file rebuilds) and mistakenly apply them to log rebuild scenarios, or when a database originally had multiple log files and the script attempts to recreate all of them in a single statement.

Step-by-Step Resolution Guide

Step 1: Review the Current ALTER DATABASE Statement

• Open SQL Server Management Studio (SSMS) or Azure Data Studio and locate the script that triggered Error 5071.
• Check the FILE clause inside the REBUILD LOG command — if you see multiple (NAME = ..., FILENAME = ...) blocks separated by commas, this is the root cause.
• Example of the incorrect syntax:

  ALTER DATABASE YourDB REBUILD LOG ON
  (NAME = YourDB_log1, FILENAME = 'D:\Logs\YourDB_log1.ldf'),
  (NAME = YourDB_log2, FILENAME = 'D:\Logs\YourDB_log2.ldf');

Step 2: Modify the Script to Specify Only One File

• Remove all but one file definition from the FILE clause.
• Corrected syntax should look like this:

  ALTER DATABASE YourDB REBUILD LOG ON
  (NAME = YourDB_log, FILENAME = 'D:\Logs\YourDB_log.ldf');

• Ensure the NAME value matches the logical log file name registered in sys.master_files (query this view first if unsure).

Step 3: Set the Database to Emergency Mode (If Required)

• If the log is missing or corrupted, put the database into EMERGENCY mode before attempting the rebuild:

  ALTER DATABASE YourDB SET EMERGENCY;
  ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DBCC CHECKDB(YourDB, REPAIR_ALLOW_DATA_LOSS);

• Confirm no other active log files exist in the file path (D:\Logs\) that could conflict with the recreated file.

Step 4: Re-run the Rebuild Log Command

• Execute the corrected single-file ALTER DATABASE ... REBUILD LOG statement.
• Once successful, revert the database back to multi-user mode:

  ALTER DATABASE YourDB SET MULTI_USER;

Alternative or Verification Steps

• Run SELECT name, physical_name, type_desc FROM sys.master_files WHERE database_id = DB_ID('YourDB'); to confirm only one log file now exists and the database is online.
• If you need multiple log files restored, rebuild with one file first to bring the database online, then use ALTER DATABASE YourDB ADD LOG FILE separately to add additional log files afterward.

Troubleshooting Guide: Error 5066 – Conflicting Database Options (SINGLEUSER / DBOUSE_ONLY)

Why This Error Happens

This error occurs because SINGLE_USER and DBO_USE_ONLY are mutually exclusive database access options in SQL Server, and both cannot be applied in the same ALTER DATABASE statement or transaction. Typically, this happens when an administrator or an automated maintenance script attempts to restrict database access for maintenance tasks (like restores or integrity checks) but incorrectly chains both options together. Since SINGLE_USER already restricts access to one connection, SQL Server rejects the simultaneous request to also limit access strictly to the dbo user, resulting in Error 5066.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Script or Command

• Open SQL Server Management Studio (SSMS) on your Windows Server 2025 or Windows 11 machine.
• Review the specific T-SQL script, Maintenance Plan, or Agent Job that triggered the error.
• Look specifically for a statement structured similarly to: ALTER DATABASE [YourDB] SET SINGLE_USER, DBO_USE_ONLY WITH ROLLBACK IMMEDIATE;

Step 2: Correct the T-SQL Syntax

• Remove one of the two conflicting options from the command. You must choose only one restriction mode per transaction.
• If restricting to a single connection for maintenance, use only:
  ALTER DATABASE [YourDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
• If restricting access to only the dbo user (while allowing multiple dbo connections), use only:
  ALTER DATABASE [YourDB] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
  (Note: DBO_USE_ONLY is generally paired with RESTRICTED_USER, not SINGLE_USER.)

Step 3: Execute the Commands Sequentially (If Both States Are Required)

• If your workflow genuinely requires moving through both states, execute them as separate, sequential statements rather than a single comma-separated list.
• First, run the SINGLE_USER command to kick out other connections.
• Once connected, if you specifically need dbo restrictions, you must first set the database back to MULTI_USER before applying RESTRICTED_USER or DBO_USE_ONLY, as SINGLE_USER mode already implies a stricter lockdown.

Step 4: Apply Changes via SQL Server Configuration Manager (If GUI-based)

• If using the SSMS GUI (Properties > Options > State), ensure you are not toggling both the “Restrict Access” dropdown to SINGLE_USER and checking a DBO_USE_ONLY box simultaneously in a custom script generated by the GUI.
• Click “Script” instead of “OK” to view the generated T-SQL and manually remove the redundant option before execution.

Alternative or Verification Steps

• Verification: Run SELECT name, user_access_desc FROM sys.databases WHERE name = 'YourDB'; to confirm the current state is either SINGLE_USER or RESTRICTED_USER/MULTI_USER as intended, without conflicting flags.
• Alternative: If the database is stuck in a transitional state due to a failed script, restart the SQL Server service via services.msc on Windows Server 2025, then reapply only the SINGLE_USER option to regain exclusive access before retrying your maintenance task.