Yesterday in "SQL Server Tools and Utilities," you learned about the various programs that ship with SQL Server. You learned about Enterprise Manager and how to register and manage SQL Servers. You will use Enterprise Manager a lot in today's lesson.
Today's subject deals with devices and databases. You first will learn about the two types of devices. The discussion then focuses on the default devices and databases created when SQL Server is installed. Creating and setting options for databases is the next topic. Finally, you will learn about how database objects, such as tables and indexes, are stored in the database.
There are two basic types of devices: backup devices (also called dump devices) and database devices.
Both types of devices are directly associated with a file. Backup devices are simply files that hold backups of a database; database devices are simply files that have a name, size, and device number assigned to them. Details on the two type of devices follow.
Backup devices are used to store database and transaction log backups. Earlier versions of SQL Server referred to these type of devices as dump devices, but SQL Server 6.5 refers to these type of devices primarily as backup devices. Backup devices can point to a file on a hard disk, to a backup tape, to floppy disks, and can even represent a network path to a file on remote servers.
Backup devices and how to best use them are covered in depth in Day 8, "Backing up and Restoring," and are not covered further in today's lesson.
A database device is a set of configurations for a file that will contain one or more databases. Database devices are used to store databases and database transaction logs. A database device has the following characteristics:
You need to keep a couple of issues in mind as you work with database devices: There is a limitation of 255 database devices per SQL Server. A database device can be made larger, but it cannot be made smaller.
Device names must be unique within SQL Server, must be a maximum length of 30 characters, must contain no spaces or punctuation, and must start with letters or numbers.
Check with your database administrator for device naming conventions. It is recommended that you identify your devices in some manner--for example, placing the word log in all devices that will hold transaction logs.
Valid device names follow: payroll_data
payrolldata
payroll_log payroll_indianapolis_data Here are some invalid device names: payroll data
payroll"data payroll!
Each database device must have its own VDEVNO as SQL Server will display it. The VDEVNO is simply an identifier used internally by SQL Server. If you use SQL Enterprise Manager to create the device, it selects an available number for you. If you use the DISK INIT statement, you need to use sp_helpdevice to find an available number. Valid numbers are 1 through 255. 0 is reserved for the master device, and numbers 127 and 126 are reserved for the msdb and msdb log devices, respectively.
The SQL Server stored procedure sp_helpdevice displays information on all the devices. You can get more detailed information on a single device by executing
sp_helpdevice logicaldevicename
To execute sp_helpdevice and find a valid device number, follow these steps:
2. Execute the sp_helpdevice command.
3. Scroll over to the right until the device _ no column is visible.
4. Note which numbers are in use and select one that is not.
sp_helpdevice device_name physical_name ----------- ------------- diskdump null master D:\MSSQL\DATA\MASTER.DAT MSDBData D:\MSSQL\DATA\MSDB.DAT MSDBLog D:\MSSQL\DATA\MSDBLOG.DAT RECEIVERDEV D:\MSSQL\DATA\RECEIVERDEV.DAT
The amount of storage reserved in the initialization of the device is measured in 2KB blocks or pages for the DISK INIT statement and in megabytes for the SQL Enterprise Manager Manage Devices dialog. How large you should make the device depends on several factors. You must know the size of the database(s) that will be placed on the device, as well as how much you think the database(s) will grow. You also need to know how much available space you have and whether this device will store more than one database. You'll learn how to estimate the size of a database later today.
WARNING: You must understand the different measurements required by different SQL Server statements. Sometimes you'll use megabytes, and other times you'll measure according to pages. Using an incorrect type of measurement could have serious complications. So, unless you work with SQL Server measurements every day, (or you like to restore your database frequently), refer to the SQL Server Books Online if you have questions about which measurement to use.
When SQL Server is installed, three default database devices are created:
These three system devices are located in the \mssql\data folder. When you install SQL Server, you can specify the path and size for the master device, although using default folders is always a good idea.
Note that a device has a logical name, as well as a complete path to the physical file. The logical name is used in most commands; the physical path is used only when creating devices. Devices also have a number, which SQL Server uses to track the individual devices.
The default devices are installed initially to hold the system databases; usually, you shouldn't tamper with these devices. SQL Server enables you to easily create new database devices, which you then can use to hold your user databases.
Next, you'll learn about creating database devices.
You use the DISK INIT command to create database devices using Transact-SQL.
The mandatory parameters of the DISK INIT command follow:
DISK INIT NAME = `logical_name', PHYSNAME = `physical_name', VDEVNO = virtual_device_number, SIZE = number of 2k blocks
Here is a sample that uses DISK INIT:
DISK INIT NAME = `PAYROLL', PHYSNAME = `c:\MSSQL\data\payroll.dat', VDEVNO = 6, SIZE = 5120
This example creates a 10MB physical file (5120 * 2KB) called payroll.dat on the C: drive in the folder MSSQL\data. It has the device number 6 and appears in SQL Enterprise Manager as PAYROLL in the device hierarchy.
Here is an additional, non-mandatory parameter of the DISK INIT statement:
[, VSTART = virtual_address]
This parameter should be left out, because it defaults to 0. You should not specify this parameter unless you are doing advanced troubleshooting. See SQL Server Unleashed, by Sams Publishing, for more details.
Each time a DISK INIT statement is issued, a row is added to the sysdevices table in the master database. Therefore, it is important that you back up the master database after each use of DISK INIT.
Only the System Administrator (SA) has permission to execute the DISK INIT statement. This permission cannot be transferred.
A few problems can occur with SQL Server when it comes to disk initialization. The most common problems are insufficient storage space, invalid file or directory names, or duplicate filenames.
Usually, the error message states that the DISK INIT command encountered an error. You can view the SQL Server Error Log, or you can inspect the hard drive using operating system tools (Windows Explorer on NT) to see whether the cause is due to any of the preceding reasons.
TIP: One of the most common errors occurs when you attempt to re-create a device you have used previously. If you used Enterprise Manager to drop the device, the physical file was not deleted when SQL Server dropped all internal references to the device. To make a device that uses the same filename again, you must use the operating system utilities (such as Explorer) to delete the file.
To create a device in SQL Enterprise Manager, you first take one of these actions:
In the Manage Database Devices dialog, click the first button on the toolbar to display the New Database Device dialog. Then follow these steps:
You can remove a device by using the sp_dropdevice command or by using SQL Enterprise Manager.
The following code shows the syntax for dropping or removing a device by using the sp_dropdevice command:
sp_dropdevice logical_name [,DELFILE]
Dropping the device does not delete the file; it merely removes the record of the device from the sysdevices table of the master database. To delete the physical operating system file, issue the sp_dropdevice command with the DELFILE command, or use the operating system utility to delete the file. Here are two examples:
sp_dropdevice payroll sp_dropdevice accounting1, DELFILE
TIP: If databases still exist on the device, the device cannot be removed except through the operating system services; this is dangerous! You first must remove the databases.
Storing SQL Server itself and all the database devices on NTFS partitions enables you to secure the files using the Windows NT security model.
To remove database devices by using Enterprise Manager, follow these steps:
Or, you can use this procedure:
Figure 4.1. Deleting a device.
A device cannot be reduced in size. However, it may be expanded. To expand a device, use the DISK RESIZE command or Enterprise Manager. The following shows the syntax for the DISK RESIZE command:
DISK RESIZE NAME = logical device name SIZE = final device size
The final device size is the final size in 2KB pages--not the increase in size. In other words, if your device PAYROLLDATA is 10MB or 57202KB blocks and you want to add 5MB to its size, you can use this command:
DISK RESIZE NAME = PAYROLLDATA SIZE = 7680f
To expand a device using Enterprise Manager, follow these steps:
Figure 4.2. Editing Devices.
Devices are created to store databases on them. Devices can be specified as default devices, meaning that databases are created on them if a particular device is not specified when a database is created.
NOTE: Only the SA can change the default status of a database device.
The status of a device can be set by using Transact-SQL or by using Enterprise Manager.
TIP: Each database should be assigned one or more specific devices in a logical manner to improve performance and ease maintenance. In other words, don't use the default device when you create databases.
One of the best ways to deal with default devices is to un-tag the master device as a default device and make sure that no new devices are tagged as default devices. This forces anyone creating databases to list specific devices on which the databases should be created instead of taking the lazy way out (by putting databases on default devices).
To set a device's default status, use this syntax:
sp_diskdefault logical_devicename, default on | default off
To set the master database default status to off, use this syntax:
sp_diskdefault master, default off
To set a default device using SQL Enterprise Manager, you can follow one of two procedures:
Or,
More than one device may have its default status turned on. If a database is created without a device specified, (thus using the default device), SQL Server selects the first default device in alphabetical order, uses all the space on it, uses the space on the second device (if necessary), and so on until the database is created or you run out of default devices.
You can use several options to help ensure the reliability of the data for SQL Server and database devices. Because SQL Server is fully compatible with Windows NT, SQL Server can use Windows NT's capability to protect hard drives from failure. Windows NT's use of hard drive mirroring, duplexing, and RAID 5 (stripe with parity) are all covered in Day 8, "Backing up and Restoring."
SQL Server enables you to mirror a single file by giving you the capability to mirror a database device to another hard drive. Each time data is written to or deleted from the original device, it also is written to the mirrored copy.
If the original (or mirrored copy) becomes corrupt, SQL Server continues to use the remaining database device.
WARNING: Although device mirroring sounds good, it is highly recommended that you use more complete and robust hard drive protection, such as that supplied by Windows NT or dedicated RAID 5 hardware. Support for device mirroring may be dropped from future versions of SQL Server.
SQL Server stores data and related objects in a database. A database is stored on one or more devices. In the next section, you'll look at the default databases that SQL Server comes installed with, as well as creating new databases and resizing existing ones.
When SQL Server is installed, four system databases (and one sample database) are created and placed on the system devices (master, msdbdata, and msdblog). The default databases follow:
The master database contains all the internal settings used by SQL Server to keep track of devices, databases, user logins, and configurations. Backing up and restoring the master database is covered in Appendix A, "Restoring the Master Database."
The msdb database is used to store tasks, alerts, and operators used in automating certain SQL Server operations. The msdb database and its role in automation is covered in Day 18, "SQL Server Automation."
The tempdb database is used to hold temporary objects such as temporary tables, and is also used by SQL Server as a sorting area for queries. Configuring the tempdb database is covered in Day 20, "Performance Tuning and Optimization."
The Model database is the template database used whenever any new databases are created. It consists of 18 system tables that hold information about the database and all objects, users, and security it contains. Because this database serves as a template for all new databases, the 18 system tables occur in each user database. Table 4.1 lists the system tables and the information they contain.
Table | Information |
sysalternates | Each SQL Server user mapped to a database user |
syscolumns | Each column in a table or view, and each parameter in stored procedure |
syscomments | Each view, rule, default, trigger, and stored procedure that has a definition statement |
sysdepends | Each procedure, view, or table referenced by a procedure, view, or trigger |
sysindexes | Each index, each table without a clustered index, and each table with text or image data |
syskeys | Each foreign, primary, or common key |
syslogs | The Transaction Log |
sysobjects | Each table, view, stored procedure, rule, trigger, default, and log (in tempdb, you will find rows for both permanent and temporary objects) |
sysprocedures | Each view, rule, trigger, default, and stored procedure |
sysprotects | User permission information |
syssegments | Each segment |
systypes | Each default system-supplied and user-defined datatype |
sysusers | Each user allowed in the database |
sysreferences | Each reference constraint created |
sysconstraints | Each constraint created |
sysarticles | Each article created for replication |
syspublications | Each publication created |
syssubscriptions | Each subscription subscribed to |
NOTE: Any changes made to the model database affect only new databases--not any databases that already exist.
The pubs database is a sample database installed by default. Because it is a good example of a relational database, many of the examples and exercises in this book are written to use the pubs database.
TIP: If the pubs database becomes corrupted or is deleted, you can reinstall it by running the Instpubs.sql script found in the \mssql\install folder.
After you create your database devices, you are ready to create your databases. Creating a database in SQL Server consists of specifying where the database will reside, its size, and (optionally) its database options.
It is important to understand the database components that are placed in the space allocated for the database. Database components can be grouped into the following two major categories:
A database and its Transaction Log may reside on the same database device or on separate database devices. In addition, a device may contain multiple databases.
A Transaction Log records the transactions against the database. Each transaction is recorded first in the Transaction Log for that database, cached in RAM, and then eventually recorded in the actual database. This gives SQL Server the advantages of using a data cache (speed), without the corresponding disadvantage of having data reside only in volatile RAM. If SQL Server stops unexpectedly, data can be recovered from the Transaction Log and used to bring the database up to date. You can not (and should not) prevent the creation or use of the Transaction Log.
See Day 8, "Backing up and Restoring," for more information on backing up and restoring the database and the Transaction Log.
When the database is created, the syslogs table, which is the Transaction Log, is placed in the same database device as the tables and data unless a separate database device is specifically assigned to the Transaction Log.
WARNING: It is highly recommended that you put the Transaction Log on its own database device. Doing so not only improves performance, but it makes data recovery--in case of a catastrophic failure--more flexible and robust. In certain kinds of failures, recovery is not possible unless the Transaction Log is on a separate device.
Although it is not necessary to place the Transaction Log on a separate device, doing so offers several advantages:
How big should the log be? This is a difficult factor to predict. If you make your log too small, you run the risk of filling the log prematurely. All processing comes to a halt until the log is cleared. If you make your log too large, you waste resources.
Ideally, the Transaction Log should be large enough to hold all the transactions that transpire between the times the log is cleaned out without wasting too much space. A good number to start with is 25 percent of the size of the database. If you will be updating large amounts of data in a single transaction, the log must be large enough to hold the entire transaction (which includes data as well as instructions) and might need to be several times the size of the data to be updated. Of course, you could break up the transactions into several, smaller sized transactions to keep the Transaction Logs smaller.
You must manage the space used by the Transaction Log by occasionally removing old transactions from the log. You easily can schedule this process (see Day 18, "SQL Server Automation"). Day 8, "Backing up and Restoring," covers the Transaction Log in more detail and how to maintain adequate space for it.
The syntax for creating databases using the SQL commands follows:
CREATE DATABASE database_name [ON {DEFAULT | database_device} [ = size] [, database_device [= size]]...] [LOG ON database_device [=size] [, database_device [=size]]...] [FOR LOAD]
database_name needs to be 30 characters or less, it should contain no spaces or punctuation, and it must be unique on the SQL Server. The database name may be typed frequently, so it's probably to your advantage to create short, meaningful names.
database_device specifies on which device (or devices) to put the database. If you do not specify which device to create the database on, the database is placed on the default device (see Day 5, "Creating Tables," for a discussion of default database devices). You may place a database on one or many devices.
size is the amount of space on each device that should be allocated for the database; it is indicated in megabytes.
LOG ON specifies a separate device on which to place the Transaction Log.
The FOR LOAD parameter is used only if you are creating the structure of the database so that you can restore it from a backup (for example, the database may have been lost or corrupted). To restore the database from a backup, it must be restored to an existing database. Specifying the FOR LOAD option creates a database that can be used only to load from a backup--normal operations will not work on the database until it is loaded from a backup.
CREATE DATABASE payroll ON payroll = 14 CREATE DATABASE accounting ON accounting1 = 10, accounting2 = 10, accounting3 = 12
The first example creates a 14MB database on device payroll. The second example creates a 32MB database on devices accounting1, accounting2, and accounting3. Both examples do not specify a separate device for the Transaction Log, so the syslogs table is placed on the databases device.
Do you get the idea that database design is a combination of administrative tasks, data design, and use? This is a factor that many system administrators and database designers overlook. All three factors are important elements to consider.
Here are some examples of the CREATE DATABASE command:
CREATE DATABASE payable ON payable_data = 50 LOG ON payable_log = 10
This example creates a 50MB database on the payable_data device and places its Transaction Log (the syslogs table) on the payable_log device.
To create a database using Enterprise Manager, follow these steps:
Figure 4.3. Creating a new database.
When creating a new database with SQL Enterprise Manager, you can select only one device for the data and one device for the log. If you want the database to be spread over multiple devices, you must use the CREATE DATABASE statement and specify the multiple devices. Or, you can use Enterprise Manager to create the database on a single device and then expand the database across multiple devices, as described in the following steps:
User-defined databases can be assigned a database device. If no device is defined, they are placed on the default device.
Follow these steps to spread the database over several devices:
You may set optional information on the database. These options are set to false in the default model database and in each user database created from model. If you set database options in model, they impact each new database created. Optionally, you can set the options for each database after it is created.
This section discusses the database options and their consequences.
By default, columns created in SQL Server tables do not permit Null values. If this option is set to true, any column created in a SQL Server table in that database allows Null values.
When this option is set to true, only the database owner (DBO) may work with this database. This option often is set when loading data by bulk copy methods, during development, or when you want to keep other users out of the database. A common error is to forget to turn off this option after the procedure is complete.
By default, a checkpoint record (see "Truncate Log on Checkpoint" later in this section) is written to the Database Log after the completion of a recovery process. This is necessary in order to maintain data integrity, but in some circumstances, it interferes with database operations. If the database is replicated on another SQL Server, for example, the replicated database should have this option set to true, because a recovery of a replicated database would not match its subscribed database on the other server.
Removable databases (databases that can be copied to a CD-ROM and then sent to another SQL Server) can be created on a SQL Server, but they must be taken offline before being placed on removable media. An offline database is not accessible to SQL Server or its users and thus cannot be modified.
The database may be a source database for replication.
Database users may select or look at data in the tables, but they cannot make modifications. This option often is used in decision support databases to improve performance. When a database is set to Read Only, no locking mechanisms can be applied, so time is not spent locking and unlocking rows of data.
Normally, when data is added to a table, each add transaction is logged. When data is added to a table by using the SELECT INTO statement or by using the Bulk Copy Program (BCP), a significant performance improvement can be gained by first setting this option to true. Using BCP with this option is covered in Day 9, "Importing and Exporting Data."
Database access is limited to one user at a time.
The database becomes a destination point for replication.
A system-generated checkpoint writes to disk all cached pages that have been modified. If this switch is set to true, the Transaction Log is truncated after each system-generated checkpoint.
Setting this option to true can have consequences for data recoverability, because the Transaction Log no longer can be backed up or restored. See Day 8, "Backing up and Restoring," for more details on backing up and restoring the Transaction Log.
NOTE: System databases have the following options set. The master, model, and pubs databases have the Truncate Log on Checkpoint option set to true. The tempdb database has the Select Into/Bulk Copy option set to true.
Use the sp_dboption stored procedure to set database options. Only the SA and the database owner can set database options. The sp_dboption must be issued from the master database.
The syntax for the sp_dboption command follows:
sp_dboption database_name, option, {true | false}
This sets the payroll database to single-user mode:
use master go sp_dboption payroll, "single user", true go
This sets the accounting database so that only the DBO can use it:
use master go sp_dboption accounting, "dbo use only", true go
You can use Enterprise Manager to change major database options. If the option you want to change is not listed when using Enterprise Manager, you must use Transact-SQL (see "Creating Databases Using Transact-SQL," earlier in this chapter).
To change options using SQL Enterprise Manager, follow these steps:
2. Double-click the database for which you want to change options.
Or,
Figure 4.4. Setting database options.
You can get information about current databases by directly examining the system databases. Alternatively, several system-stored procedures exist for obtaining information about databases.
The sp_helpdb stored procedure is very handy to check the status of databases.
sp_helpdb database
If you issue this command without a parameter, the sp_helpdb command provides information about all the databases on the server. If you include a database after the command, sp_helpdb provides more detailed information about only the database named.
sp_helpdb payroll name db_size owner dbid created status ---- ------- ----- ---- ------- ------ payroll 12 sa 9 Jan 9 no options set device fragments size usage free kbytes ---------------- ---- ----- ----------- payroll_data 2 MB data 480
The device fragments information is a list of database devices that tells you about specific allocations of space on each device and whether that space is used for data, the log, or both.
The sp_helplog stored procedure shows information on the Transaction Log.
sp_helplog `in database `master' the log starts on device master'
The format of sp_helplog is different than sp_helpdb. It takes no parameters. You must switch to the right database before issuing the sp_helplog command to get log information about that particular database.
SQL Server uses three system tables (all contained in the master database) to track devices and databases.
Information in sysdevices is used to build information about the database in sysusages. cntrltype indicates whether the device is a database device or a backup (dump) device. A value of 0 indicates a database device. low represents the first virtual page number and high represents the last virtual page number. You determine the number of pages on a device by calculating high - low + 1. Multiply this solution by 512 to determine the number of megabytes.
select cntrltype, low, high, name from sysdevices where cntrltype = 0 order by low
sysusages contains information on the first database page number or logical page start within the database, the number of contiguous pages, and the vstart or virtual starting page number. The vstart enables the mapping of database fragments to a specific virtual device. If the vstart value falls between the low and high page numbers for the device in sysdevices, the fragment is located on the device.
In sysdatabases information includes the database name, the ID of the database owner, status flags, and the date of the last dump. SQL Server stores the various statuses of a database as the additive sum of all the status flags that apply to the database. If a database is marked as both No Checkpoint on Recovery (16) and Database Not Recovered Yet (64), for example, the value of the status is 80 (16 + 64). Table 4.2 lists the database status flags and their values.
Value | Status |
2 | Database in Transition |
4 | Select Into/Bulk Copy |
8 | Truncate Log on Checkpoint |
16 | No Checkpoint on Recovery |
32 | Crashed During Load |
64 | Database Not Recovered Yet |
128 | Database Is in Recovery |
256 | Database Suspect |
1024 | Read Only |
2048 | DBO Use Only |
4096 | Single User |
8192 | Database Not Being Checkpointed |
16384 | ANSI Null Default |
32768 | Emergency Mode |
WARNING: Do not attempt to change the status bits manually! Although it is possible to do so, you have to be very careful that the value you change it to exactly specifies all the options you want (and don't want). It is not recommended that you do this.
The DROP DATABASE command is used to delete (or drop) a database. This removes the entries from the system tables as well as all the objects (tables and so on) and the log. The space can be used for other databases. You must be the DBO or SA to drop a database.
The syntax for the DROP DATABASE command follows:
DROP DATABASE database_name [, database_name...]
This command drops the payroll database:
DROP DATABASE payroll
If the DROP DATABASE command does not remove the database, you may need to use the sp_dbremove command or the DBCC DBREPAIR (database_name, dropdb) command.
This command removes a corrupt database:
DBCC DBREPAIR (database, dropdb)
Alternatively, you may use this command to remove a database:
sp_dbremove database [,dropdev]
The dropdev parameter removes from sysdevices all references to the devices of which the database being dropped had exclusive use. It does not delete the physical file. The preferred command to use is sp_dbremove.
This command removes the payroll database, even if it normally cannot be dropped:
sp_dbremove payroll
To delete a database using Enterprise Manager, follow these steps:
Or,
You can enlarge a database by using the ALTER DATABASE command. This may be done while the database is in use.
The syntax for ALTER DATABASE follows:
ALTER DATABASE database_name [ON device_name [=size], [..]] [FOR LOAD]
Here, size indicates the additional amount of space to allocate--not the finished size of the database. The default increment is 1MB.
This command expands the accounting database on the accounting device by an additional 50MB:
ALTER DATABASE accounting ON accounting3 = 50
The amount of new space allocated for data or the log depends on the original allocation. If the original allocation during the CREATE DATABASE statement was for data, the new allocation is for data. If the original allocation was for both data and a log, the new allocation is for data and the log. If the original allocation was for the log, the new allocation is for the log as well. If the new allocation is for space on a device that has no fragment of the database, the allocation is assumed to be for data.
If you attempt to enlarge a database by an amount of space that is not available, SQL Server allocates as much as it can, rounding to the nearest .5 MB. It then reports the results.
You can enlarge the size of a database up to 32 times.
To enlarge a database using Enterprise Manager, follow these steps:
You can use the DBCC SHRINKDB command to change the size of the database. It is a good idea to use SQL Enterprise Manager to shrink the database, however, because it automatically shows you the minimum size to which the database can be shrunk. You cannot decrease the size of the database below the space needed to store the current objects. You cannot decrease the size of the log. The database should be in single-user mode.
Figure 4.5. Expanding a database.
The syntax for the DBCC SHRINKDB command follows:
DBCC SHRINKDB (database_name [, new_size [ [ , `MASTEROVERRIDE' ] ] )
Here, the MASTEROVERRIDE part of the statement is used if you need to shrink the master database.
new_size is in 2KB pages and represents the final size of the database.
DBCC SHRINKDB (accounting , 4096)
If you do not specify a size, DBCC SHRINKDB returns the minimum size to which the data can be shrunk. DBCC SHRINKDB shrinks both the data and log portions. To change only the size of one or the other, use the DBCC SHRINKDB statement and then use ALTER DATABASE to increase the size of the data or log.
WARNING: DBCC SHRINKDB is fully logged and recoverable except when used on the master database. Because SQL Server is unrecoverable if the system crashes while shrinking the master database, you should back up the master database before attempting to shrink it.
To shrink a database using Enterprise Manager, follow these steps:
Figure 4.6. Shrinking the database.
If you created the database and placed it and the log on the same device as the database, you later may move the log to its own separate device. You may do so by using the ALTER DATABASE statement to create log space on a separate device. Using sp_logdevice ensures that all new log allocations of space are placed on the new device. The old log, however, still remains on the old device. To remove all logs from the original device, you must follow these instructions:
TIP: Summary of Sizes for Creating Devices and Databases
To create a device using DISK INIT, specify the size in 2KB pages.
To expand a device using DISK RESIZE, specify the size in 2KB pages.
To create a database using CREATE DATABASE, specify the size in MB.
To shrink a database using DBCC SHRINKDB, specify the size in 2KB pages.
To expand a database using ALTER DATABASE, specify the size in MB.
Document the sequence in which you CREATE ALTER, RESIZE, or MOVE a database. You will need this information if you need to rebuild a database, load from backup, or create multiple servers with the same configuration.
Keep the following information:
If Transact-SQL was used to create the devices and databases, keep the scripts that were used to create them (DISK INIT, CREATE DATABASE, ALTER DATABASE). Keep these scripts on disk, and keep paper copies. This serves as excellent documentation, and the scripts can be run again to re-create devices and databases.
As you have seen, you can use Enterprise Manager to create and tinker with your databases. Then before going into production, you can use Enterprise Manager to generate Transact-SQL scripts for all objects. Keep those scripts as emergency backups for re-creating your servers. Even during the development process, development and test teams may want to occasionally drop and re-create fresh versions of their databases using these scripts.
To estimate the storage requirements of your database, you need to understand the storage structures of SQL Server. The first place to start is in examining the basic elements of storage and then estimating the storage needed for your particular database. For more detailed information about how space is used in a database, refer to SQL Server 6.5 Unleashed.
The three main parts of a database that use space follow:
The sp_spaceused stored procedure displays space used by object_name. This procedure is an important tool that you can use to determine how much space is left on your database or on the Database Log if the log is on a separate device. If the database runs out of space, no more data can be added. A good rule of thumb is 25 percent free space in your database at all times. When sp_spaceused is issued from within a user database, it requires no parameters.
The syntax for the sp_spaceused command follows:
sp_spaceused [object_name] [[,] @updateusage = {true | false} ]
Here, @updateusage = true issues a DBCC UPDATEUSAGE command before displaying usage. The DBCC UPDATEUSAGE command reports and then corrects inconsistencies in sysindexes that may result in incorrect information being displayed by sp_spaceused. You should be in single-user mode to run this command. If there are no inconsistencies DBCC UPDATEUSAGE displays no data; if there are inconsistencies, it displays information on what it has found and corrected.
sp_spaceused database_name database_size ------------- ------------- master 8.00 MB (o rows affected) reserved data index_size unused -------- ---- ---------- ------ 13860 KB 12260 KB 436 KB 1156 KB
database_size is the total size, including data and log allocations. reserved is based on data use. Log information is not included.
NOTE: sp_spaceused can be off by a couple of extents (an extent is 16KB).
SQL Server operates on a building block of a 2KB page. That is why most units in SQL are given in 2KB units. When SQL Server allocates pages for objects, it allocates eight pages at a time in order to lower the overhead associated with allocating and tracking individual pages. A set of eight pages is called an extent. An extent is 16KB (8 * 2KB).
SQL Server tracks extents in groups of 32; each group is called an allocation unit. An allocation unit is 512KB (32 * 16KB). Databases are based on allocation units, whereas objects inside a database are based on extents. Objects cannot share extents; any space left in an extent is wasted unless the object becomes larger.
Therefore, even a one-line stored procedure takes an extent in a database, which equates to 16KB. Thirty-two such objects take at least 512KB in a database, even if the objects are very small.
A table is an object that is based on extents (16KB) but has individual 2KB pages. A 2KB page can hold 2,048 bytes of data (1024 * 2). Of the 2,048 bytes available, 32 bytes are used for overhead in linking pages and another approximately 54 bytes are used for Transaction Log recording; this leaves approximately 1,962 bytes (2,048-32-54 = 1962) to store information. Rows of data can never cross pages, so the largest row of data SQL Server 6.5 can hold is approximately 1,962 bytes or characters.
If a single row contains 196 bytes of data, then a total of 10 rows can be held per data page (1962/196) = 10.x. If a single row contains 1,000 bytes of data, then only one row of data can be held per data page (1962/1000) = 1.x. Remember that because rows cannot cross data pages, any extra room on a data page (after fitting all the complete rows) is wasted. In the previous example, if the data could be shortened to 981 bytes, two rows would fit on each data page, cutting the space required for this particular table in half.
A data row size is calculated by adding the size of its columns plus any row overhead. More details on calculating row sizes is provided on Day 5, "Creating Tables." The basics to get you started follow:
NOTE: Text and image datatypes are exceptions to the rule that a row can only be 1,962 bytes or less. These two datatypes allow data sizes up to 2GB!
You started today's lesson with a look at the two types of devices: backup devices and database devices. Backup devices are used to back up databases and are covered in Day 8, "Backing up and Restoring." Database devices are used to contain databases and consist mainly of a name, a path to a file, and an internal number that SQL Server uses to track the device. Database devices can be expanded but not shrunk.
You then learned how to create databases, with an emphasis on putting the Transaction Log on a separate device for both performance and recoverability reasons. You then learned how to expand and shrink databases.
Database options then were discussed, and you learned how to set options using both Transact-SQL and Enterprise Manager.
You ended the day by learning how to estimate the space needed by tables, data, and objects; you also got an introduction to indexes. Indexes are covered in depth on Day 13, "Indexing."
The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. The answers are provided in Appendix B, "Answers."
1A. sysdevices, sysdatabases, sysusages
2A. sysdevices holds information about devices, sizes, and file locations. sysdatabases holds information about the user databases, and sysusages is the table that holds information on which databases are held on which devices.
3A.
DISK INIT NAME = `Accounting', PHYSNAME = `c:\MSSQL\data\accounting.dat', VDEVNO = 6, SIZE = 15000
DISK INIT NAME = `Employees', PHYSNAME = `c:\MSSQL\data\employee.dat', VDEVNO = 6, SIZE = 5000
4A.
CREATE DATABASE accounting ON accounting_data = 30 log on accounting_log=10
5A. Yes, but you initially can create only the database on a single device; then you can use Enterprise Manager to expand it to multiple devices.
Use the statements you created to implement the accounting and employee databases.
© Copyright, Macmillan Computer Publishing. All rights reserved.