Devices (Level 3)
The database architecture of Microsoft® SQL Server™ 2000 differs from the database architecture of SQL Server 6.x. In SQL Server 2000:
- Operating system files replace database devices.
- Data files and transaction logs cannot co-exist on the same operating system file.
- A single operating system file cannot be shared by multiple databases.
For more information about database architecture, see Overview of SQL Server Architecture.
| SQL Server 6.x | SQL Server 2000 |
|---|---|
| DISK INIT created database or transaction log devices. When DISK INIT followed either a CREATE DATABASE or ALTER DATABASE statement, SQL Server used the specified devices for storing the specified database or transaction log. | The CREATE DATABASE statement syntax and ALTER DATABASE statement syntax both allow the creation of separate data and log files. Both CREATE DATABASE and ALTER DATABASE create operating system files and databases in a single step (generating a log file automatically, if none is specified with the LOG ON clause).
Consider removing all references to DISK INIT and replacing with references to either CREATE DATABASE or ALTER DATABASE. DISK INIT has limited support in SQL Server 2000. Existing scripts will run as long as they do not have data and log sharing of the same data files. |
| DISK REINIT restored device entries to appropriate system tables when the device entry was missing from sysdevices. | Removed; no replacement.
Consider removing all references to DISK REINIT. |
| sp_logdevice put syslogs (contains the transaction log) on a separate database device. To add another log segment to a database with an existing log segment, it was necessary to execute DISK INIT followed by sp_logdevice. | Removed. The CREATE DATABASE statement creates a log file on a new operating system file.
Consider removing all references to sp_logdevice and replacing with references to CREATE DATABASE. SQL Server 6.x scripts using the LOG ON clause of CREATE DATABASE will work as expected. Scripts without the LOG ON clause of CREATE DATABASE will have a log file generated automatically. |
| Devices created using DISK INIT and CREATE DATABASE could be dropped only by using sp_dropdevice. | Databases created without DISK INIT before CREATE DATABASE can be dropped with DROP DATABASE; otherwise, use sp_dropdevice.
Use sp_dropdevice when using DISK INIT, followed by CREATE DATABASE. |
Examples
A. Use both DISK INIT and CREATE DATABASE syntax
This example uses DISK INIT and CREATE DATABASE and works in SQL Server version 6.5 and SQL Server 2000:
DISK INIT name = 'testdb_data',
physname = 'c:\testdb_data.dat',
vdevno = 9,
size = 10240
DISK INIT name = 'testdb_log',
physname = 'c:\testdb_log.dat',
vdevno = 8,
size = 10240
CREATE DATABASE testdb
ON testdb_data = 10
LOG ON testdb_log = 10
GO
B. Use of sp_logdevice and CREATE DATABASE in SQL Server 2000 fails
In earlier versions of SQL Server, this script created a 20 MB database consisting of the two files named testdb_data and testdb_log. This script also moved the transaction log to the testdb_log device by using sp_logdevice.
Note Scripts like this one were usually generated by the SQL Server 6.5 sp_help_revdatabase system stored procedure, which used sp_logdevice to ensure the proper device layout for database restores. Because SQL Server 2000 creates the database when it is restored, scripts such as these are no longer necessary.
-- SQL Server 6.x example.
DISK INIT name = 'testdb_data',
physname = 'c:\testdb_data.dat',
vdevno = 9,
size = 10240
DISK INIT name = 'testdb_log',
physname = 'c:\testdb_log.dat',
vdevno = 8,
size = 10240
CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10
-- Use sp_logdevice to move the log to the testdb_log device.
EXEC sp_logdevice testdb, testdb_log
In SQL Server 2000, the above script does not work the same as in SQL Server 6.x because sp_logdevice no longer exists.
In SQL Server 2000, this script creates a 20 MB database consisting of the two files named testdb_data and testdb_log. In addition, SQL Server generates a log file automatically, which is 25 percent of the database size. In the following script (using the devices created earlier), a 10 MB log file is generated automatically:
CREATE DATABASE testdb on testdb_data = 10, testdb_log = 10
C. Use CREATE DATABASE syntax only
Using the SQL Server 2000 CREATE DATABASE syntax, the database from the earlier example could be created as follows:
CREATE DATABASE testdb ON (name = 'testdb_data',
filename = 'd:\testdb_data.dat', size = 10)
LOG ON (name = 'testdb_log', filename = 'd:\testdb_log.dat',
size = 10)