Sparse files (MS SQL Server Database Snapshots)

[Daniel]

I was able to successfully configure a system so that Microsoft SQL Server uses the ramdisk for databases. These do not have to be persistent, they are created, used for a short time and then deleted again.

Unfortunately, a database snapshot cannot be created. The command CREATE DATABASE xx AS SNAPSHOT OF yy shows the following error:
Msg 1823, Level 16, State 2, Line 10
A database snapshot cannot be created due to an error during startup.
Msg 5119, Level 16, State 1, Line 10
The file "D:\Temp\TestSnapshot.mdf.snapshot" cannot be converted to a sparse file. Make sure that the file system supports sparse files.

Can ImDisk not handle sparse files?

[v77]

Did you format as NTFS?

[Daniel]

Yes. I think only NTFS supports sparse files.

[v77]

Strange... I just did a few tests on a dynamic ramdisk with the "fsutil sparse" commands, and everything works fine.
I can convert a file to a sparse file and set a region to 0 (with the region properly unallocated).

So I assume an incompatibility from the driver (don't expect a fix). But this has nothing to do with sparse files.

[Daniel]

The fsutils tool, of course, I can use that to test. What commands exactly did you used?
If they work for me too, I can ask Microsoft why a database snapshot cannot be created.

[v77]

I copied a large file (more than 1 GB) onto the ramdisk (no matter the name, let's say "Z:\test"), and then I used
fsutil sparse setFlag Z:\test 1
fsutil sparse setRange Z:\test 0 536870912

The first 512 MB was set to 0, and I retrieved a free space of 512 MB.

[Daniel]

OK, thank you.

[Daniel]

I'm sorry, but I still can't create Microsoft SQL Server database snapshots with ImDisk.
I'm now using the RAMDisk program from Dataram. It does create a database snapshot. Just FYI.