June 8, 2015 at 2:42 pm
I recently set up a SQL 2012 FCI with a NetApp fileshare to store the data files. The install worked just fine, but I can't run an integrity check for any of my databases. Whenever I try, I get these error messages:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5120, Level 16, State 104, Line 1
Unable to open the physical file "\\path-to-fileshare\MSSQL11.MSSQLSERVER\MSSQL\DATA\model.mdf:MSSQL_DBCC12". Operating system error 1: "1(Incorrect function.)".
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
The error message suggests SQL had a problem creating the snapshot, but I checked through some NetApp documentation for configuring SMB 3.0 for SQL and couldn't find anything specifically about what is needed for running snapshots or CHECKDB. Has anyone else run into this or have ideas for other settings to check?
June 30, 2015 at 8:02 am
Hi DBA_Freddie, we're having the same issue with our new NetApp SMB shares. Did you ever get a resolution to the problem?
June 30, 2015 at 1:30 pm
Hi Neil,
I should have posted an update sooner once I found out. There is actually a NetApp KB that states it doesn't support CHECKDB for databases on an SMB fileshare.
https://kb.netapp.com/support/index?page=content&id=2017129&locale=en_US
You might need a NetApp account to access the link so here is the relevant text I was given from the KB:
Cause
The SQL Server attempts to create a hidden database snapshot in order to create a point-in-time consistent state of the database. However, since an SQL Server database snapshot cannot be created, DBCC CHECKDB will fail.
We are trying to move away from using a NetApp fileshare for SQL because of this. But there are some potential workarounds that you could use if you are ok with the downsides.
Workaround 1:
Run the following command to set the database in the Read-Only mode:
USE [master]
GO
ALTER DATABASE [Adventureworks] SET READ_ONLY WITH NO_WAIT
GO
use Adventureworks
go
DBCC CHECKDB
Workaround 2:
First, create a SQL Server database snapshot that resides on the local storage and then run the DBCC CHECKDB command against that SQL snapshot.
Run the following commands:
Use master?go
CREATE DATABASE Adventureworks_ss
ON (NAME = Broken, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Adventureworks.ss'
) AS SNAPSHOT OF Advnetureworks
use Adventureworks_ss
go
DBCC CHECKDB
The downside for #1 is that turning a db read only could be forcing a downtime that you don't want to take for your application. #2 can't be used for the system databases
June 30, 2015 at 3:51 pm
Thanks for the info! I guess we'll have to decide what the best course is going to be 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply