SQL Server Upsizing Wizard: Step 6 – Set Database Properties

Article
10/23/2006

If you chose to create a new SQL Server database in Step 5, the wizard displays this step to enable you to select the database device and size for your database. You can choose to create your new SQL Server database on an existing device, or create a new device.

Selecting an Existing Device

Database device
Displays all the devices on the SQL Server you logged on to, including default devices. Select a device with sufficient free space for your database.

Default Devices

If one or more devices have been set as default devices on your SQL Server, the SQL Server Upsizing Wizard includes a Default entry in the list of available devices. The Default entry can represent more than one device. Choosing the default device does not ensure you'll have enough room to upsize your database. The SQL Server Upsizing Wizard checks the devices specified as default to make sure there's enough room for the database.

Tip To place your database on multiple devices, make those devices (and no others) default devices. When you run the SQL Server Upsizing Wizard, select Default for your database.

For more information on setting the default status of devices, see your SQL Server documentation for a description of the sp_diskdefault system procedure.

Size
Displays the size in megabytes of the database device selected in the adjoining Database Device list box.
Free Space
Displays the amount of free space remaining on the database device selected in the Database Device list box.
Database size
Enter the amount of space, in megabytes, you want to allocate for the new database. The size of a new database must be at least 2 megabytes because that is the minimum allowed by SQL Server.

Creating a New Device

If existing devices are too full, you might want to create a new device. You must be a system administrator to create new SQL Server devices.

To create a new SQL Server device

Select Create new device from the list of devices.

If you are a system administrator, a dialog box appears. Otherwise, an error message appears.
Type a name for the new device.

The device name must be 30 characters or less and consist of letters, digits, and the symbols #, $, or _. Spaces are not allowed.
Choose OK.

The new device name is added to the devices list box.
Type a device size.

The device size must be at least 2 megabytes. The combined size of new devices cannot exceed available disk space.

If you are a system administrator, the SQL Server Upsizing Wizard displays the amount of space on the server drive storing the Master SQL Server database.

Note The size of a device cannot be changed once it has been set. Make sure you create sufficiently large devices.

The selected new device is created after you choose the Finish button. The SQL Server Upsizing Wizard creates the new device in the same directory as the Master database device. If you add a new device to the list but don't select the new device as the database device, the new device is not created.

The SQL Server Upsizing Wizard can generate a report that includes the logical name, physical name, and other information about new devices you create.

Device Number Limitations

Every SQL Server device is given a number when it is created. However, the available pool of device numbers is limited. The default value is 10, although the number might be different on your server. The SQL Server Upsizing Wizard searches for an available device number. If all device numbers are taken, you won't be able to create a new device.

Note To increase the number of SQL Server device numbers available, see your SQL Server documentation for a description of the sp_configure system procedure.

Share via

SQL Server Upsizing Wizard: Step 6 – Set Database Properties

Selecting an Existing Device

Default Devices

Creating a New Device

Device Number Limitations

See Also

Additional resources