Dropping a Database Snapshot

  • Article

Dropping a database snapshot deletes the database snapshot from SQL Server and deletes the sparse files that are used by the snapshot. When you drop a database snapshot, all user connections to it are terminated.

Any user with DROP DATABASE permissions can drop a database snapshot.

Dropping a database snapshot clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachestore in the plan cache, the SQL Server error log contains the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to some database maintenance or reconfigure operations". This message is logged every five minutes as long as the cache is flushed within that time interval.

To drop a database snapshot

See Also

Tasks

How to: View a Database Snapshot (SQL Server Management Studio)

Concepts

Creating a Database Snapshot
Database Snapshots

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

12 December 2006
New content:
  • Added information about clearing the plan cache.

5 December 2005
Changed content:
  • Documented that an use database snapshot can be dropped.