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.
--------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the |
| accuracy and the use of this Application Note. This Application |
| Note may be copied and distributed subject to the following |
| conditions: 1) All text must be copied without modification and |
| all pages must be included; 2) If software is included, all files |
| on the disk(s) must be copied without modification [the MS-DOS̉ |
| utility DISKCOPY is appropriate for this purpose]; 3) All |
| components of this Application Note must be distributed together; |
| and 4) This Application Note may not be distributed for profit. |
| |
| Copyright 1991 Microsoft Corporation. All Rights Reserved. |
| Microsoft, MS-DOS, and the Microsoft logo are registered |
| trademarks and Windows is a trademark of Microsoft Corporation. |
--------------------------------------------------------------------
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.