Microsoft KB Archive/67411

INF: QS0433: Devices, Databases, and Transaction Logs ID Number: Q67411

1.00 1.10 OS/2

=
========================================================= Microsoft Product Support Services Application Note (Text File) QS0433: DEVICES, DATABASES, AND TRANSACTION LOGS ====================================================================== Revision Date: 8/91 No Disk Included

The following information applies to Microsoft SQL Server versions 1.0, 1.1, and 1.10a.

Summary
The following is a description of the relationships among SQL Server disk devices, dump devices, and databases.

Disk Devices
A disk device is an allocation of disk space. SQL Server allows for 10 separate disk devices. The first one is assigned to MASTER.DAT. The disk device can be as large as an entire physical device (hard disk drive) but cannot span more than one physical device. Please note that the physical device does not have to reside at the server but can be shared over the network. Also, the NET USE command must be executed prior to using the device.

Dump Devices
Dump devices are completely different from disk devices. The role of the dump device is to act as the storage area for the database and transaction log dumps. Three dump devices come predefined in SQL Server version 1.1:

Dump Device Description ———– ———–

diskettedumpa Disk dump to drive A diskettedumpb Disk dump to drive B diskdump Disk dump to a NULL device

In addition to the three predefined dump devices above, you can define many more of your own. Like the disk devices, the dump devices can span over the network.

Databases
Databases can sit on the disk devices that you create. There can be one database on several disk devices, several databases on one disk device, or any combination thereof. When you create a database and indicate what memory allocations you want to use on the disk devices, SQL Server initializes every bit, setting the bits to 0 (zero).

One often confusing part of this relationship is the role of the transaction log. Every database created has a transaction log of its own. The transaction log competes with data for space on the device(s) and slows down the system slightly due to the double writing of the same device.

Therefore, we recommend that you create each database to access at least two separate disk devices (preferably on different physical drives). After the database is created, you should execute the sp_logdevice command and give the transaction log one of the disk devices for its own use. Please note that the transaction log should be about 15 to 25 percent of the size of the database.