DISK INIT Statement

Creates a device on which a database or multiple databases can be placed. A device is an operating-system file that SQL Server pre-allocates for database use.

Syntax

DISK INIT
    NAME = 'logical_name',
    PHYSNAME = 'physical_name',
    VDEVNO = virtual_device_number,
    SIZE = number_of_2K_blocks
[, VSTART = virtual_address]

where

NAME: Is the logical name of the database device. Logical names must correspond to the rules for identifiers and must be enclosed with single quotation marks ('). Identifiers exceeding 30 characters are accepted but truncated to 30 characters. This name is recorded in the name column of the master..sysdevices table, and is used in the CREATE DATABASE, ALTER DATABASE, DISK RESIZE, and many other SQL statements.
PHYSNAME: Is the drive letter, full path, and filename of the database device. Paths for database devices must follow the rules for the operating-system paths and filenames. They must be enclosed with single quotation marks (').
VDEVNO: Is the virtual device number. This number must be unique among the database devices associated with SQL Server. Values can be from 1 through 255; 0 is reserved for the MASTER database device.
SIZE: Specifies the size, in 2K blocks, of the database device.
If you are planning to use the new database device to create a new database, the minimum size is 1 MB.
VSTART: Is the starting virtual address or the starting offset in 2K blocks. The value for VSTART should be 0 (the default). Reset VSTART only if instructed to do so.

Remarks

The DISK INIT statement takes advantage of the fact that Windows NT creates a file either by zeroing out all bytes (FAT) or by presenting the application immediately with a zeroed image of the file (NTFS).

Execute DISK INIT once for each new database device. Each time DISK INIT is executed, a row is added to the master..sysdevices table. A new database device does not automatically become part of the pool of default database storage. Assign default status to a database device with the sp_diskdefault system stored procedure.

Because entries are made in the master..sysdevices table, it is important to back up the master database with the DUMP DATABASE statement after each use of DISK INIT.

User databases are assigned to specific database devices with the optional ON database_device clause of the CREATE DATABASE or ALTER DATABASE statement, or they are placed on a default device(s).

The LOG ON extension to CREATE DATABASE is the preferred method for putting a database's transaction log (that is, the sylogs system table) on a device different from the one on which the data is stored. Alternatively, you can name at least two database devices when you create the database and then execute the sp_logdevice system stored procedure. ALTER DATABASE can also be used at any point to add another database device and migrate the log to it with the sp_logdevice system stored procedure.

Note The LOG ON extension in CREATE DATABASE immediately moves the entire log to a separate device. The sp_logdevice method retains part of the system log on the original database device until a transaction completes the transfer. Using the LOG ON extension is preferable to using the sp_logdevice system stored procedure.

For a report on all database devices and dump devices on your system, execute the sp_helpdevice system stored procedure.

Before removing a database device with the sp_dropdevice system stored procedure, you must first drop all existing databases on that database device.

Note The sp_dropdevice system stored procedure does not remove the operating-system file. The physical file can be deleted by using the standard operating-system del command.

After dropping a database device, you can create a new one with the same information (using DISK INIT) as long as you delete the physical file first.

To successfully complete disk initialization, the SQL Server process must have the appropriate operating-system permissions to create the file as named by the PHYSNAME parameter. To see the account under which SQL Server is running, use the Control Panel Services icon for the MSSQLServer service.

Permission

DISK INIT permission defaults to the system administrator and is not transferable.

Example

Define Database Devices

This example creates a 12 MB (6144 2K pages) device called DEVICE1. This device is created in the same directory as the default SQL Server 6.0 MASTER device.

DISK INIT
    NAME = 'DEVICE1',
    PHYSNAME = 'c:\sql60\data\device1.dat',
    VDEVNO = 1,
    SIZE = 6144

This example creates a 16 MB (8192 2K pages) device called DEVICE2. This device is created on a different logical drive from DEVICE1. The SQLDATA directory must exist prior to the execution of this statement.

DISK INIT
    NAME = 'DEVICE2',
    PHYSNAME = 'd:\sqldata\device2.dat',
    VDEVNO = 2,
    SIZE = 8192

This example creates a 125 MB (64,000 2K pages) device called DEVICE3. This device is created on a third logical drive. The SQLDATA directory must exist prior to the execution of this statement.

DISK INIT
    NAME = 'DEVICE3',
    PHYSNAME = 'e:\sqldata\device3.dat',
    VDEVNO = 3,
    SIZE = 64000