 |
 |
Content:
Ads
SQL 7 fundamentally changes the way that databases are physically stored. Space allocation changes somewhat. Devices are gone entirely now. Instead, databases reside in database files. The data and log are kept separated and these files are never shared by more than 1 database.
When a database is created, two or more files are created to support that database. These files are the Primary Data File and the Transaction Log File. Primary Data Files have an extension of ".MDF" and Transaction Log Files have an extension of ".LDF". A database may also have Secondary Data Files, having an ".NDF" extension. (Note: You can actually use any extension for your files but these are recommended for consistency.)
Every database must have a Primary Data File. This file contains the system tables for the database, pointers to any other files used by the database, and can also contain data. Secondary files contain any data that did not fit in the Primary. Databases do not necessarily have a secondary file, but may have one or more. The Transaction Log File contains all of the log information that may be used to recover a database. Each database has a Transaction Log File, though there may be more than one.
When data is actually stored to these files, it is stored in a contiguous block of data called a page. Pages are 8k in size, with a 96-byte header, leaving up to 8060 bytes for actual data storage. Since rows cannot occupy more than one page, the maximum size of a row is 8060 bytes. When an object, such as a table, has been allocated 8 pages, future allocations will as an extent at a time. An extent is 8 contiguous pages. In other words, 64k of contiguous space. Smaller objects can share extents.
Creating Databases
Creation of a database can be accomplished graphically via the Enterprise
Manager, or via Transact-SQL statements. The following is the basic syntax
for creating a database using Transact-SQL:
CREATE DATABASE database_name
[ON { [PRIMARY] } [,...n] ]
[LOG ON { } [,...n] ]
[FOR RESTORE]
Notice that you can specify multiple operating system files using the
filespec parameter. The optional PRIMARY keyword identifies which of the
files becomes the file containing the system tables and the start of the
database. If omitted, the first filespec listed becomes the primary. The
parameter is actually a parenthetical list of parameter that specify the
geometry of the actual operating system file. It was shortened above to
improve readability, but should be expanded to take on the following syntax:
(NAME = logical_file_name,
FILENAME = 'os_file_name'
[, SIZE = size]
[, MAXSIZE = { max_size | UNLIMITED } ]
[, FILEGROWTH = growth_increment] )
Note the SIZE, MAXSIZE, and FILEGROWTH options. The SIZE option indicates
the initial size of the database file. If not specified, the default is
3 MB (1 MB for log files), or the size of the MODEL database, which serves
as a template for all new databases. SQL 7 introduces the ability for
database to automatically expand. The MAXSIZE option indicates the maximum
size to which a file will grow. If not entered, it will grow until the
disk is full. FILEGROWTH tells SQL Server by how much the file should
expand when it become necessary to do so. The default is 10%, the minimum
is 64k. This value should be an increment of 64k, otherwise SQL Server
will round it to the nearest 64k.
The following is an example:
CREATE DATABASE db_accounting
ON
PRIMARY
(NAME=accounting_dat, FILENAME='C:\MSSQL7\DATA\ACCOUNTING.MDF'
,SIZE=5MB
,MAXSIZE=10MB
,FILEGROWTH=1MB)
LOG ON
(NAME=accounting_log, FILENAME='C:\MSSQL7\DATA\ACCOUNTING.LDF'
,SIZE=3MB
,MAXSIZE=5MB
,FILEGROWTH=64KB)
This example creates a database called "db_accounting". The
database consists of two files, 'ACCOUNTING.MDF' for data and 'ACCOUNTING.LDF'
for the log. The data file can grow from the initial 5MB to 10MB and will
do so in 1MB increments. The log file can grow from the initial 3MB to
5MB in 64K increments.
A database can be easily deleted using the DROP DATABASE command. The syntax is simply:
DROP DATABASE databasename
Altering a Database
Once a database has been created, some items can be changed. The ALTER
DATABASE command provides the ability to add files to the database, drop
files, modify file properties, and manage file groups (more on that later).
The following is the ALTER DATABASE syntax:
ALTER DATABASE database
ADD FILE [TO FILEGROUP filegroup_name][FOR RESTORE]
| ADD LOG FILE
| DROP FILE 'logical_file_name'
| CREATE FILEGROUP filegroup_name
| DROP FILEGROUP filegroup_name
| MODIFY FILE
}
The parameter is the same as above. The following is an example to add
a Secondary Data File to a database:
ALTER DATABASE db_accounting
ADD FILE
(NAME = 'accounting_dat2'
,FILENAME = 'c:\mssql7\data\accounting2.ndf'
,SIZE = 15MB
,MAXSIZE = 30MB
,FILEGROWTH = 1MB
)
Note that to DECREASE the size of database or database file you must use
the DBCC SHRINKDATABASE command. The syntax is as follows:
DBCC SHRINKDATABASE (database_name, target percent free)
For example, DBCC SHRINKDATABASE (db_accounting, 20), would attempt to
shrink all database files such that the database had 20 percent free space
left after the operation was complete. For example. If a database file
is currently 100 MB with 40 MB of data, a setting of 20 would result in
a database file of 50MB with 40 MB of data. If the percentage is not specified
then the database will be made as small as possible. If the percentage
specified is greater than space currently available then the operation
will simply not do anything.
DBCC SHRINKFILE (logical_file_name, size)
In this case, the size is the final desired size in MB.
File Groups
When creating a database of multiple files, some files can be logically
bound in a File Group. Later, when creating an object, such as a table
or an index, that object can be directed to a specific file group. The
Primary Data File can not be made part of a user file group, however.
Therefore, all system tables reside in what is called the Default File
Group ... which is basically the Primary Data File and any Secondary Data
Files that have not been made explicitly part of another user defined
file group.
File Groups are created using the ALTER DATABASE command, mentioned above. For example:
ALTER DATABASE db_accounting
CREATE FILEGROUP fg_payable
After the File Group has been created, Secondary Data Files can be created
and added to the File Group using, again, the ALTER DATABASE command.
For example:
ALTER DATABASE db_accounting
ADD FILE
(NAME = "acct_payable"
,FILENAME = "d:\mssql7\data\fg_payable1.ndf"
,SIZE = 100MB)
TO FILEGROUP fg_payable
You now have a file group "fg_payable" that consists of one
file. Now, when creating objects, such as a table, you can specify a File
Group to place the object ON. The obvious performance benefits would be
realized on extremely large objects when these files reside on dedicated
hardware.
Retrieving Database Information
A variety of stored procedures are available to retrieve information about
databases and database files. Below is a list of commands. You are encouraged
to try each out for yourself.
sp_helpdb
sp_helpdb database_name
sp_spaceused database_name
sp_helpfile database_name
sp_helpfilegroup database_name
| © Copyright 2003-04. E-Learning Center. |