TechTutorials - Free Computer Tutorials  







SQL 7.0 Database Files 
 


Added: 06/01/2000, Hits: 2,430, Rating: 0, Comments: 0, Votes: 0
Add To Favorites | Comment on this article
By Brian Talbert

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:

Code :

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:

Code :

  (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:

Code :

CREATE DATABASE db_accounting
  ON
    PRIMARY
  (NAME=accounting_dat, FILENAME='C:MSSQL7DATAACCOUNTING.MDF'
  ,SIZE=5MB
  ,MAXSIZE=10MB
  ,FILEGROWTH=1MB)
  LOG ON
  (NAME=accounting_log, FILENAME='C:MSSQL7DATAACCOUNTING.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:

Code :

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:

Code :

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:

Code :

ALTER DATABASE db_accounting
  ADD FILE
  (NAME = 'accounting_dat2'
  ,FILENAME = 'c:mssql7dataaccounting2.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:

Code :

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.

Code :

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:

Code :

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:

Code :

ALTER DATABASE db_accounting
  ADD FILE
         (NAME = "acct_payable"
         ,FILENAME = "d:mssql7datafg_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





Comments (0)

Be the first to comment on this article


Related Items








7 Seconds Resources, Inc.




IT Showcase