December 2, 2015 at 10:21 am
I'm running SQL Server 2008 R2 on Windows Server 2008 R2, and I'm having trouble using CHECKDB in one of my environments. CHECKDB works fine on tempdb, but not on databases where a snapshot will be created.
I can create a snapshot manually, and it can be created in the same directory as the original database file. I can also run CHECKDB successfully on a snapshot that I've created manually. This appears to be a situation where the O/S isn't allowing the file extension used by CHECKDB.
The command "DBCC CHECKDB ([Tools])" generates the output below. The Windows application event log contains the same system error 5 message as displayed by SQL Server.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'M:\TEST\Tools.mdf:MSSQL_DBCC39'.
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.
DBCC results for 'Tools'.
DBCC CHECKDB will not check SQL Server catalog or Service Broker consistency because a database snapshot could not be created or because WITH TABLOCK was specified.
Although the error message suggests there is a permission problem, I don't believe that to be the case. If I try to create a snapshot manually using the filename that CHECKDB is attempting to use, I get the error output below. If I change the file extension to remove the colon and suffix, the snapshot can be created. I experimented with the file extension, and found I could use standard extensions like ".mdf" and ".txt", but not something like ".txt2", or even ".mdb".
create database [Tools-Snapshot] on (name='Tools', filename='M:\TEST\Tools.mdf:MSSQL_DBCC39') as snapshot of [Tools];
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5123, Level 16, State 1, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'M:\TEST\Tools.mdf:MSSQL_DBCC39'.
December 3, 2015 at 1:07 am
Check that the SQL account has permissions to write to the M:\TEST folder.
December 3, 2015 at 6:25 am
I don't believe it's a permission issue; the database is using that path for user databases, and snapshots can be created in the path as long as the file extension for the snapshot fits into the general naming scheme I mentioned in my post.
December 3, 2015 at 6:49 am
Are there anti virus rules for unknown file extensions?
December 3, 2015 at 7:08 am
I just checked McAfee On-Access scan statistics, and it doesn't show any file actions blocked. Using Windows Explorer I was able to create a file with the extension ".mdf:MSSQL" in the directory without any problem. I suspect the problem has something to do with the file stream approach used by SQL Server to create the snapshot, but I haven't found anything related to Group Policy that deals with this.
December 15, 2015 at 1:08 pm
Check to make sure the file doesn't already exist. CHECKDB failures can leave the snapshot files still on the disk. They seem to always be created with the same name, so the snapshot can't be created because the file already exists.
December 15, 2015 at 1:27 pm
I've checked, and the file doesn't already exist. I'm pretty sure it's an issue with naming the file with a ":" in the file extension.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply