Forum Discussion
How to verify of all the records are populated on a snapshot volume when snapshot is captured.
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?