Questions About SQL Server Database Snapshots

sharepointsnapshotsql serversql-server-2012

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.

Best Answer

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;

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

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 ...

Related Solutions

Sql-server – How to query for existing database snapshots in SQL server

A snapshot database will have an entry in sys.databases.source_database_id, so you can use something like the following to check for a current snapshot. Similarly, you could use the same check to DROP an existing snapshot before creating new.

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

Sql-server – Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Add BOTH Domain\Servername$ login and NT AUTHORITY\Network Service login to SQL Server instance giving you above problem and give both logins the right permissions.

If you are working in a Spanish environment or another language try adding NT AUTHORITY\Servicio de Red or whatever the equivalent is in your language.

For steps on adding logins see links: http://support.microsoft.com/kb/889646/en-us and http://www.c-sharpcorner.com/UploadFile/specialhost/how-to-fix-login-failed-for-user-nt-authority-iusrs-in-share/ and https://stackoverflow.com/questions/2806438/login-failed-for-user-domain-machinename

If you have a SQL Server Instance with logins that have been working for you when accessed by application pools and the like, you might want to check which logins are not in the troublesome SQL Server instance and that you might need.

Other comments:

Ok, even though the error said: Login Failed for user 'Domain\Servername$' and I added that login to the problematic SQL Server instance per what I'd seen described on the internet, it seems that the login I ALSO needed to add was NT AUTHORITY\Network Service.

I basically decided to add any logins that were in my normal SQL Server instance that I was using for the regular SharePoint databases and that weren't in the SQL Server instance I was using for SharePoint Reporting Services to the latter. Something else that tripped me up when I was looking originally to add the NT AUTHORITY\Network Service login was that I couldn't find it. It turns out I had to add it in Spanish as NT AUTHORITY\Servicio de red, which is wierd because the app pool has its Identity in English. I guess this happens when some systems have English as default language and some others have Spanish

Best Answer

Related Solutions

Sql-server – How to query for existing database snapshots in SQL server

Sql-server – Login Failed for Domain\ServerName$ when trying to create a new SQL Server Reporting Services Application in SharePoint 2013

Related Question