Database Snapshot Failed Due to Disk Space Issue

Introduction

Database snapshot is really a useful feature. As we know, Snapshot database file will keep increasing parallel to the DML operations on database. But if disk containing snapshot file runs out of space, then what will be the impact on new transactions, database & snapshot?

Microsoft design snapshot in very well manner. If Snapshot failed to records the data changed by DML transaction due to disk space issue, then your snapshot will become unusable & reaches to suspect mode and on the other hand your actual database will keep running un-impacted.

Snapshot database cannot be recovered from suspect state so you need to drop & recreate the snapshot database.

1. While running the below transaction, we got an error that we are unable to write on snapshot file due to disk space issue.

   

2. We check data in table to check id transaction completed successfully or failed due to snapshot issue. We found transaction is completed & database is running fine.

   

3. After that, when we try to select snapshot database, we get an error that database is suspect mode.

   

4. We also try to bring snapshot database in emergency mode to see if we can troubleshoot it but we get an error that this operation cannot be performed on snapshot database.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference: Rohit Garg (http://mssqlfun.com/)