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

by

Troubleshooting Guide: SQL Server Error 3709

Why This Error Happens

This error occurs because SQL Server maintains a dependency link between a source database and any database snapshots created from it, which rely on the original database’s data pages to resolve unmodified records. When you attempt to drop, detach, or take the source database offline, SQL Server blocks the operation because the snapshot(s) still reference the original file structure. This is a protective mechanism rather than a corruption issue, and it will persist until every dependent snapshot is explicitly removed first.

Step-by-Step Resolution Guide

Step 1: Identify All Dependent Snapshots

  • Open SQL Server Management Studio (SSMS) and connect to the instance reporting the error.
  • Run the following query to list all snapshots tied to the source database:
SELECT 
    d.name AS SnapshotName,
    d.create_date,
    sm.physical_name AS SnapshotFile,
    sd.name AS SourceDatabase
FROM sys.databases d
JOIN sys.master_files sm ON d.database_id = sm.database_id
JOIN sys.databases sd ON d.source_database_id = sd.database_id
WHERE sd.name = 'YourSourceDatabaseName';
  • Note the exact SnapshotName values returned — you will need these for the next step.

Step 2: Drop the Identified Database Snapshot(s)

  • For each snapshot name returned in Step 1, execute a DROP DATABASE command:
DROP DATABASE [YourSnapshotName];
  • If multiple snapshots exist on the same source, repeat this command for every snapshot individually; SQL Server 2025 does not support dropping multiple databases in a single statement if any are snapshots.
  • Confirm no error is returned in the Messages pane of SSMS after each drop.

Step 3: Verify No Orphaned File Locks Remain

  • On Windows Server 2025 / Windows 11, open File Explorer and navigate to the snapshot’s sparse file location (commonly C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\DATA\).
  • Ensure the .ss (snapshot sparse file) associated with the dropped snapshot no longer appears; if it does, restart the SQL Server (MSSQLSERVER) service via services.msc to release any lingering file handles.
  • Re-attempt the original operation (DROP, DETACH, or RESTORE) on the source database.

Step 4: Re-Run the Original Database Operation

  • Execute your original intended command again, for example:
DROP DATABASE [YourSourceDatabaseName];
  • Confirm the command completes without triggering Error 3709.

Alternative or Verification Steps

  • Verify the fix: Re-run the query from Step 1 — it should return zero rows, confirming no snapshots remain linked to the source database before you proceed with your original operation.
  • Alternative approach: If you cannot locate the snapshot via SSMS (e.g., it was created by a third-party backup tool), query sys.databases directly for any database where source_database_id is not NULL, as this guarantees you catch all snapshot dependencies regardless of naming conventions.