3

One of our divisions is running SQL Server 2012 SP1 Enterprise edition for no particular reason (I know, I know... the decision was made before my time and they are resistant to change). What I'm curious about, since I have a lack of experience with Enterprise ed., is why database snapshots would be created when no one at the division has any idea why either. Unbeknownst to me, they've been manually deleting these for years.

As I understand it database snapshots can only be created via T-SQL (though I believe DBCC CHECKDB may create them behind the scenes) so what exactly is happening here? Is there a good way to track down the process that is creating the snapshots? Is there a setting I've overlooked?

The instance in question is a dedicated SharePoint instance.

The server itself has space constraints making the snapshots that much more of an issue.

  • 1
    SharePoint can automatically create snapshots when publishing document changes, and can be configured to create snapshots as part of its backup strategy. See duckduckgo.com/… for more. – Max Vernon Jan 8 '17 at 3:12
3

When you create a database snapshot, it logs a message in the Error Log that says 'starting up database...' and the database name. You could filter the error log on those messages.

CREATE DATABASE test_snap ON
(
NAME = StackOverflow,
FILENAME ='D:\Data\stack_snap.mdf'
) AS SNAPSHOT OF StackOverflow;

This is your database on snapshots

This is your error log on snapshots

When you run checkdb, the filename looks like this, and it's not listed under the Database Snapshots folder.

This is your database on CHECKDB

As for tracking the process down, I'd probably create a DDL Trigger, and use EVENTDATA() to figure it out.

CREATE TRIGGER database_create
ON ALL SERVER
FOR CREATE_DATABASE ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.