Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Server: Windows Server 2008 R2 X64 Enterprise

SQL: SQL Server 2008 R2 Enterprise X64

I have a default SQL Server instance, the SQL Server service account is running as a domain user.

I am trying to create a database snapshot in the directory where the mdf files are stored. The T-SQL syntax is correct. The file system is NTFS.

The error message I get is:

Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.

Msg 5119, Level 16, State 1, Line 1
Cannot make the file "e:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestDB.ss" a  sparse file. Make sure the file system supports sparse files.

The local SQLServerMSSQLUser$db$MSSQLSERVER group has Full Control permission on the folder where I am trying to create the snapshot.

I can fix the problem in two ways, neither of which are suitable.

  1. Add the SQL Server service (domain) account to the local Administrators group and restart the SQL service.
  2. Grant the local SQLServerMSSQLUser$db$MSSQLSERVER group Full control on E:\

I have tried to change the owner of the DATA directory to SQLServerMSSQLUser$db$MSSQLSERVER to no avail. I have no issue creating a new database

Why can I not create a snapshot by giving permission only on the DATA folder?

Update 23/09/2010:

I have tried mrdenny's suggestion with no luck (but learned something new in the process), I suspect the problem may be due to the fact that the domain is a windows 2000 domain running in mixed mode. I had to install hotfix KB976494 for Server 2008 R2, as the SQL Server 2008 R2 installer would not verify the service account correctly with the domain.

I noticed that Server 2000 isn't a supported operating system for SQL 2008 R2 but cannot find anything that would suggest it shouldn't work in a 2000 domain.

I dis-joined the test server from the domain and changed the service accounts to the local service account and I still have the same issue. I will try to re-install the server without joining the domain and without the hotfix and see if the issue persists.

share|improve this question

1 Answer 1

That's a low level NTFS error. It that file the one that you are trying to create for the snapshot? Does the SQL Server have the instant file initialization right? (Its about the only thing I can think of that might cause this.)

share|improve this answer
    
Yes that is the file I am trying to create. I have tried to give the service account the SE_MANAGE_VOLUME_NAME permission so it can use instant file initalization but it made no difference. –  Chris Diver Sep 22 '10 at 9:57
    
Can you verify that the volume is NTFS? Was this volume originally formatted by Windows NT 4 or Windows 2000? –  mrdenny Sep 22 '10 at 21:16
    
Yes it is NTFS, it is a new server formatted by Windows 2008 R2 and I have replicated the problem using virtual machine. Also when I apply full control permissions to the service account on 'e:\' the snapshot works fine. –  Chris Diver Sep 23 '10 at 11:23
    
What permission did the SQL Account have before granting full control? –  mrdenny Sep 25 '10 at 19:06
1  
Have you used Process Monitor (technet.microsoft.com/en-us/sysinternals/bb896645.aspx) to trace file-system activity when attempting to create the snapshot? –  Charles Hepner Jul 12 '12 at 19:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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