October 7, 2014 at 1:52 pm
A week a go I created a new SQL box (2012 standard) and migrated our sharepoint databases over to this as part of our SP migration to 2013.
This morning I was checking the server... disk space, events etc so make sure all was well and I hadn't overlooked anything.
Alongside the .mdf files are .snap files for certain DBs - certainly not all of them. I checked in management studio and under snapshots I see them listed again.
I thought database snapshots were an enterprise feature, not standard?
That aside, where did they come from? It is not something I have knowingly switch on.
There are no jobs like this in sql agent. There are a few of them and they are suffixed with a date time. One of these DBs is 250GB so I need to find what is taking these .SNAPs and stop it before we run out of disk space!
October 7, 2014 at 2:21 pm
You are correct that database snapshots are enterprise only.
DBCC checkdb does take a database snapshot but the file is hidden (not hidden in 2014, but does not have .snap extension)
you can check if there are any database snapshots with the following:
SELECT *
FROM sys.databases
where source_database_id is not null
you can also check if these .snap files are in fact associated with any database with:
SELECT *
FROM sys.master_files
where physical_name like '%.snap'
October 7, 2014 at 2:42 pm
Thanks, these are not hidden as I can see them on the file system and under snapshots in SSMS.
Do you know what is taking them, how it could have done so and how I can stop it from happening?
They are being created/modified outside of hours. I wondered if there is a new option in 2012 that I have overlooked?
I ran both your statements and they show the DBs involved and the actual .snap files
October 7, 2014 at 2:58 pm
October 7, 2014 at 3:00 pm
Just did and was on my way back to report 🙂
Seems the sys engineer priovided the incorrect key when installing - this is indeed enterprise!
So now that mystery is solved.
How, why and how to stop (snapshots)?
October 7, 2014 at 3:30 pm
That may be a good thing that it is enterprise as there are some Sharepoint features require it.
As for what s creating these snapshots, i don't believe this is something Sharepoint can do so either you have an agent job ( which you said you have checked) or a third party is creating them.
maybe setup an extended events session or server trigger(less desirable) to capture who/what created snapshot.
October 7, 2014 at 3:34 pm
We have commvault simpana which apparently has a SQL Connector for backups.
Perhaps this has detected the enterprise version and is utilising snapshots in order to do it's thing.
We haven't bothered with the SQL connector before as we do our own SQL backups and just have commvault pick the .baks from the filesystem.
I need to check with the network admins.
I have just read up on the downgrade path from enterprise to standard - eek!
October 8, 2014 at 1:22 am
Jay@Work (10/7/2014)
We have commvault simpana which apparently has a SQL Connector for backups.Perhaps this has detected the enterprise version and is utilising snapshots in order to do it's thing.
Snapshots can't be used for backups, so won't be that.
SQL doesn't create them automatically, so some tool, job or person is creating those.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 2:46 pm
Yep, picking it is Simpana from Commvault.
Going to get the network admin to call to confirm with them.
Maybe it has always worked this way but I'm only seeing them in SSMS on this server as it is Enterprise?
October 8, 2014 at 2:58 pm
Other editions can't create explicit snapshots, so no. And snapshots are useless for backup purposes, they can't be used for backups, they have no advantages when there are backups running, they can't be backed up.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 3:02 pm
I don't think this is being used for backup purposes in the traditional sense. I do SQL backups and commvault picks these up off the system.
In this instance, this server is part of a sharepoint farm and my guess is (awaiting confirmation) that snapshots are being used to see what changes have been made in sharepoint (to documents for example) and this info then used to take granular sharepoint backups using the commvault sharepoint connector widget - possible using the VSS to do so </guess>
February 2, 2016 at 10:37 am
.
February 2, 2016 at 11:37 am
Did you ever find out how the snap files were being created? We have the same situation occurring now in our SharePoint test environment (call it SPTest.) One of the databases (call it DB1) is getting a snapshot created every evening on SPTest. The test environment was created by copying the SharePoint production environment ("SPProd") databases to the new test db server. The strange thing for us is that it looks like the maintenance job on the production server is causing the .snap to be created. I played with the schedule on the SPProd maintenance job and the .snap gets created on SPTest approximately 2 minutes after the SPProd maintenance job starts, regardless of what time I schedule it for. The maintenance job is very simple - it cleans up old .bak and .trn files and then does a backup of the db's. That is it - nothing in the job that I can see that would create a .snap on SPTest. Any ideas would be appreciated.
May 20, 2016 at 12:11 pm
I am facing the same issue as mentioned above. When my engineer is reconfiguring drive for optimization Hard Disk Number changed and thus causing generating .snap files.
Can any one suggest how to fix these? Can we delete them with out any impact to Databases?
Thanks.
PeaceMaker.
September 20, 2016 at 4:09 pm
Same issue here, guys. Someone has found an solution?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply