SQL Server – CREATE DATABASE on RAW Partitions Issue

sql serverstorage

I'm attempting to create a database using two raw, i.e. unformatted, partitions.

Microsoft Docs states you can do this, you simply need to specify only the drive letter of the raw partition, as in:

CREATE DATABASE DirectDevice 
ON (NAME = DirectDevice_system, FILENAME = 'S:')
LOG ON (NAME = DirectDevice_log, FILENAME = 'T:')

However, SQL Server 2017 returns this error:

Msg 5170, Level 16, State 4, Line 1
Cannot create file 'S:' because it already exists. Change the file path or the file name, and retry the operation.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

The pertinent bit of the documentation states:

If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one data file can be created on each raw partition.

And yes, drive S: and T: are both unformatted raw partitions that do exist in my system:

DISKPART> detail partition

Partition 4
Type    : ebd0a0a2-b9e5-4433-87c0-68b6b72699c7
Hidden  : No
Required: No
Attrib  : 0000000000000000
Offset in Bytes: 999934656512

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
* Volume 6     T                RAW    Partition    127 MB  Healthy

DISKPART> select partition 3

Partition 3 is now the selected partition.

DISKPART> detail partition

Partition 3
Type    : ebd0a0a2-b9e5-4433-87c0-68b6b72699c7
Hidden  : No
Required: No
Attrib  : 0000000000000000
Offset in Bytes: 1000067825664

  Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
* Volume 7     S                RAW    Partition    129 MB  Healthy

Removing the colon from the drive letters, as in FILENAME = 'S' and FILENAME = 'T', results in:

Msg 5105, Level 16, State 2, Line 1
A file activation error occurred. The physical file name 'S' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

SQL Server 2000 Documentation shows the following example under the CREATE DATABASE section:

H. Use raw partitions
This example creates a database called Employees using raw partitions. The raw partitions must exist when the statement is executed, and only one file can go on each raw partition.

    USE master
    GO
    CREATE DATABASE Employees
    ON
    ( NAME = Empl_dat,
        FILENAME = 'f:',
        SIZE = 10,
        MAXSIZE = 50,
        FILEGROWTH = 5 )
    LOG ON
    ( NAME = 'Sales_log',
        FILENAME = 'g:',
        SIZE = 5MB,
        MAXSIZE = 25MB,
        FILEGROWTH = 5MB )
    GO

However, the above example shows SIZE, MAXSIZE, and FILEGROWTH parameters which are clearly not required for SQL Server Data Files stored on RAW partitions.

Further details from the SQL Server 2000 documentation, specifically concerning raw drives:

Using Raw Partitions
Microsoft® SQL Server™ 2000 supports the use of raw partitions for creating database files. Raw partitions are disk partitions that have not been formatted with a Microsoft Windows NT® file system, such as FAT and NTFS. In some cases, using databases created on raw partitions can yield a slight performance gain over NTFS or FAT. However, for most installations the preferred method is to use files created on NTFS or FAT partitions.
When creating a database file on a raw partition, you do not specify the physical names of the files comprising the database; you specify only the drive letters of the disks on which the database files should be created.
If you are using Microsoft Windows® 2000 Server, you can create mounted drives to point to raw partitions. When you mount a local drive at an empty folder, Windows 2000 assigns a drive path to the drive rather than a drive letter. Mounted drives are not subject to the 26-drive limit imposed by drive letters; therefore, you can use an unlimited number of raw partitions. When you create a database file on a mounted drive, you must end the drive path to the file name with a trailing backslash (), for example, E:\Sample name. For information about creating a mounted drive, see the Windows 2000 Server documentation.

There are several limitations to consider when using raw partitions:
Only one database file can be created on each raw partition. The logical partition must be configured as a single database file, because there is no file system on the raw partition.

Standard file-system operations such as copy, move, and delete cannot be used with raw partitions.

Database files located on raw partitions cannot be backed up using the Windows NT Backup utility. However, SQL Server database or transaction log backups can still be created.

Database files on raw partitions cannot be automatically expanded. Either initially create the database at its full size, or manually expand the database files. For more information, see Expanding a Database.

Only lettered partitions, such as E:, or mounted drives, such as E:\Sample name\ can be used. Numbered devices cannot be used.

File-system services such as bad block replacement are not available with raw partitions.


This was inspired by Brent Ozar's post about SQL Server 6.5, which did support raw partitions

Best Answer

I can confirm raw partitions work, as expected, with SQL Server 2000 SP4 on Windows XP x64.

I just ran the following through Query Analyzer (shudders) against SQL Server 2000 SP4:

CREATE DATABASE t
ON PRIMARY 
(
    NAME = t_primary
    , FILENAME = 'E:'
)
LOG ON 
(
    NAME = t_log
    , FILENAME = 'F:'
);

The results:

The CREATE DATABASE process is allocating 0.64 MB on disk 't_primary'.
The CREATE DATABASE process is allocating 1.00 MB on disk 't_log'.

The above CREATE DATABASE t code works with SQL Server 2005 on Windows XP x64; the only output is Command(s) completed successfully.

SSMS shows the following when you look at the database files:

enter image description here

Isn't that about as cool as a bucket of bolts?

SQL Server 2012 SP1 on Windows Server 2012 Standard, with SQL Server Management Studio service account set to "Local System":

enter image description here

SQL Server 2014 (12.0.5000.0) running as "Local System" on Windows Server 2012 exhibits the same behavior as SQL Server 2017; i.e., it returns this error message:

Msg 5170, Level 16, State 4, Line 1
Cannot create file 'E:' because it already exists. Change the file path or the file name, and retry the operation.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.