Cannot Drop Snapshot Which Doesn’t Exist

Here was an interesting problem we ran into recently.  We use SQL Server database snapshots to help load our data warehouse.  To do this, we have an automated process which drops and re-creates snapshots as needed.  Not too long ago, we started running into a problem in which the process failed to run, giving me two separate and contradictory errors.  The step to create a new snapshot failed because “Database ‘[my database snapshot name]’ already exists. Choose a different database name”

This seems pretty clear and straightforward; however, when I tried to drop the snapshot, I got an error message indicating that the database did not exist.  I checked sys.databases and no reference of that snapshot was found.  Nevertheless, the snapshot file itself existed on the Windows server hosting this SQL Server instance.  When I tried to delete that file, I got an error message stating that the file is in use.  And, even stranger, we were able to run USE [my database snapshot name] and perform queries and DBCC CHECKDB against the snapshot itself.  So this snapshot existed in some land between existence and non-existence. This problem matches this post from a few years back, which had no resolution.

The resolution we came up with is as follows:  take the primary database (the one upon which the snapshot was built) offline.  Once you do that, you should be able to rename the snapshot file in Windows.  After doing that, bring the database back online and you should be able to drop the database snapshot.  There must have been some kind of error during the snapshot drop process which prevented the snapshot from being dropped cleanly, but going through these steps fixed our problem.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s