Forum Discussion

aindrilla_das's avatar
aindrilla_das
Copper Contributor
Nov 09, 2026

How to verify of all the records are populated on a snapshot volume when snapshot is captured.

am seeing a consistent 1-record count discrepancy when recovering a MS-SQL database from a volume snapshot onto a secondary server. The target database recovers and attaches cleanly, but the total record count on the recovered instance is off by exactly one record compared to the snapshot checkpoint on the source.

Step-by-Step Procedure Used:

  1. Started a continuous write workload inserting data into the source database.
  2. Suspended I/O on the source database.
  3. Flushed the underlying storage volumes hosting the data and log files.
  4. Recorded the baseline row count on the source database while frozen.
  5. Resumed / unfroze I/O on the source database.
  6. Scanned and mounted the snapshot volumes on the recovery host.
  7. Attached the data and log files (.mdf and .ldf) to SQL Server on the recovery host.
  8. Queried table row counts on the recovered database (observed a 1-record mismatch).
  9. Executed DBCC CHECKDB on the recovered database (completed with 0 allocation errors and 0 consistency errors).

Verification Queries Executed:

Initially, row counts were gathered using partition metadata:

SQL

SELECT 
    t.name AS TableName, 
    SUM(s.row_count) AS RecordCount FROM sys.tables t JOIN sys.dm_db_partition_stats s ON t.object_id = s.object_id WHERE s.index_id IN (0, 1) 
  AND t.name IN ('timestamp', 'emp_id', 'emp_name') GROUP BY t.name ORDER BY t.name;

 

To eliminate the possibility of stale cached metadata in sys.dm_db_partition_stats, I verified directly using a full table scan:

SQL

SELECT COUNT(*) FROM dbo.<TableName>;

 

The exact same 1-record mismatch persisted between the source check and the recovered instance.

1 Reply

  • MW_DEV's avatar
    MW_DEV
    Brass Contributor

    Hi, I must sat that this is very interesting case. I would first verify the snapshot timing and transaction boundaries. A one-row discrepancy does not necessarily indicate that the storage snapshot is missing data.

    One important detail I cannot see from your procedure: at which exact step is the storage snapshot actually created? 

    According to Microsoft's VSS documentation, the expected sequence is Prepare → Freeze → Create Snapshot → Thaw. The storage snapshot must be captured while database I/O is frozen, before normal write operations resume.

    If the snapshot is captured after I/O resumes, even a very small timing difference could explain the discrepancy under a continuous insert workload. But there is also another factor worth investigating: transaction recovery.  When the recovered database is attached, SQL Server performs recovery, including REDO of committed transactions and UNDO of incomplete transactions. Depending on the exact timing and transaction isolation used for the source query, the observed row counts may represent different transactional states.

    Your successful DBCC CHECKDB result is encouraging, but it only validates database consistency. It does not establish that the recovered database contains exactly the same committed rows as those observed by your source query.

    I would suggest the following verification approach:

    First, temporarily stop the insert workload and allow all outstanding transactions to complete. Insert and commit a known test record, and record its primary key along with the source row count.

    Next, create an application-consistent storage snapshot using SQL Server VSS Writer or the supported SQL Server snapshot backup workflow. Ensure all data and transaction log volumes are included in the same coordinated snapshot set and that the snapshot is completed before I/O resumes.

    Then recover the database and verify the known record, the row count, and ideally the actual primary key values rather than relying only on aggregate counts. If the counts match when the workload is stopped but differ during continuous inserts, I would investigate the timing of the snapshot operation and any transactions active around that boundary.

    Would be nice also to know a few additional details:

    - Which SQL Server version are you using?

    - How exactly are you suspending I/O: SQL Server VSS Writer, SUSPEND_FOR_SNAPSHOT_BACKUP, or some storage-level mechanism?

    - Which storage snapshot technology are you using?

    - Is the actual storage snapshot created before I/O is resumed?

    - What transaction isolation level is used for your source-side COUNT(*) query?