| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
System Administration Guide (Online Only) |
![[-]](/dwicons/i_colpse.gif)
| Chapter 22 Setting Database Options |
Chapter 22
This chapter describes how to use database options.
Database options control:
The behavior of transactions
Defaults for table columns
Restrictions to user access
Performance of recovery and bcp operations
Log behavior
The System Administrator and the Database Owner can use database options to configure the settings for an entire database. Database options differ from sp_configure parameters, which affect the entire server, and set options, which affect only the current session or stored procedure.
Use sp_dboption to change settings for an entire database. The options remain in effect until they are changed. sp_dboption:
Displays a complete list of the database options when it is used without a parameter
Changes a database option when used with parameters
You can change options for user databases only. You cannot change options for the master database. To change a database option in a user database (or to display a list of the database options), execute sp_dboption while using the master database.
The syntax is:
sp_dboption [dbname, optname, {true | false}] To make an option or options take effect for every new database, change the option in the model database.
All users with access to the master database can execute sp_dboption with no parameters to display a list of the database options. The report from sp_dboption looks like this:
sp_dboption
Settable database options. -------------------- abort tran on log full allow nulls by default auto identity dbo use only ddl in tran identity in nonunique index no chkpt on recovery no free space acctg read only select into/bulkcopy/pllsort single user trunc log on chkpt trunc. log on chkpt. unique auto_identity index
For a report on which options have been set in a particular database, execute sp_helpdb in that database.
The following sections describe each database option in detail.
abort tran on log full determines the fate of a transaction that is running when the last-chance threshold is crossed. The default value is false, meaning that the transaction is suspended and is awakened only when space has been freed. If you change the setting to true, all user queries that need to write to the transaction log are killed until space in the log has been freed.
Setting allow nulls by default to true changes the default null type of a column from not null to null, in compliance with the SQL standard. The Transact-SQL default value for a column is not null, meaning that null values are not allowed in a column unless null is specified in the create table or alter table column definition.
You cannot use allow nulls by default to change the nullibility of a column during select into statements. Instead, use convert() to specify the nullibility of the resulting columns.
While the auto identity option is true, a 10-digit IDENTITY column is defined in each new table that is created without specifying either a primary key, a unique constraint, or an IDENTITY column. This IDENTITY column is only created when you issue a create table command, not when you issue a select into. The column is not visible when you select all columns with the select * statement. To retrieve it, you must explicitly mention the column name, SYB_IDENTITY_COL, in the select list.
To set the precision of the automatic IDENTITY column, use the size of auto identity configuration parameter.
Though you can set auto identity to true in tempdb, it is not recognized or used, and temporary tables created there do not automatically include an IDENTITY column.
While dbo use only is set to true (on), only the Database Owner can use the database.
Setting ddl in tran to true allows these commands to be used inside a user-defined transaction:
alter table (clauses other than partition and unpartition are allowed)
create default
create index
create procedure
create rule
create schema
create table
create trigger
create view
drop default
drop index
drop procedure
drop rule
drop table
drop trigger
drop view
grant
revoke
Data definition statements lock system tables for the duration of a transaction, which can result in performance problems. Use them only in short transactions.
These commands cannot be used in a user-defined transaction under any circumstances:
alter database
alter table...partition
alter table...unpartition
create database
disk init
dump database
dump transaction
drop database
load transaction
load database
select into
truncate table
update statistics
identity in nonunique index automatically includes an IDENTITY column in a table's index keys so that all indexes created on the table are unique. This database option makes logically nonunique indexes internally unique and allows those indexes to be used to process updatable cursors and isolation level 0 reads.
The table must already have an IDENTITY column for the identity in nonunique index option to work either from a create table statement or from setting the auto identity database option to true before creating the table.
Use identity in nonunique index if you plan to use cursors and isolation level 0 reads on tables that have nonunique indexes. A unique index ensures that the cursor is positioned at the correct row the next time a fetch is performed on that cursor.
Do not confuse the identity in nonunique index option with unique auto_identity index, which is used to add an IDENTITY column with a unique, nonclustered index to new tables.
no chkpt on recovery is set to true (on) when an up-to-date copy of a database is kept. In these situations, there is a "primary" database and a "secondary" database. Initially, the primary database is dumped and loaded into the secondary database. Then, at intervals, the transaction log of the primary database is dumped and loaded into the secondary database.
If this option is set to false (off)--the default--a checkpoint record is added to the database after it is recovered by restarting Adaptive Server. This checkpoint, which ensures that the recovery mechanism is not re-run unnecessarily, changes the sequence number of the database. If the sequence number of the secondary database has been changed, a subsequent dump of the transaction log from the primary database cannot be loaded into it.
Turning this option on for the secondary database causes it to not get a checkpoint from the recovery process so that subsequent transaction log dumps from the primary database can be loaded into it.
no free space acctg suppresses free-space accounting and execution of threshold actions for the non-log segments. This speeds recovery time because the free-space counts will not be recomputed for those segments. It disables updating the rows-per-page value stored for each table, so system procedures that estimate space usage may report inaccurate values.
read only means that users can retrieve data from the database, but cannot modify anything.
select into/bulkcopy/pllsort must be set to on to perform operations that do not keep a complete record of the transaction in the log, which include:
Using the writetext utility.
Doing a select into a permanent table.
Doing a "fast" bulk copy with bcp. By default, fast bcp is used on tables that do not have indexes.
Performing a parallel sort.
Adaptive Server performs minimal logging for these commands, recording only page allocations and deallocations, but not the actual changes made to the data pages.
You do not have to set select into /bulkcopy/pllsort on to select into a user database when you issue the select into command to a temporary table. This is because temporary tables are created on tempdb and tempdb is never recovered. Additionally, you do not need to set the option to run bcp on a table that has indexes, because inserts are logged.
After you have run select into or performed a bulk copy in a database, you will not be able to perform a regular transaction log dump. After you have made minimally logged changes to your database, you must perform a dump database, since changes are not recoverable from transaction logs.
Setting select into/bulkcopy/pllsort does not block log dumping, but making minimally logged changes to data does block the use of a regular dump transaction. However, you can still use dump transaction...with no_log and dump transaction...with truncate_only.
By default, select into/bulkcopy/pllsort is turned off in newly created databases. To change the default, turn this option on in the model database.
When single user is set to true, only one user at a time can access the database. You cannot set single user to true in tempdb.
When trunc log on chkpt is true (on), the transaction log is truncated (committed transactions are removed) when the checkpoint checking process occurs (usually more than once per minute), if 50 or more rows have been written to the log. The log is not truncated if less than 50 rows were written to the log, or if the Database Owner runs the checkpoint command manually.
You may want to turn this option on while doing development work during which backups of the transaction log are not needed. If this option is off (the default), and the transaction log is never dumped, the transaction log continues to grow, and you may run out of space in your database.
When trunc log on chkpt is on, you cannot dump the transaction log because changes to your data are not recoverable from transaction log dumps. Use dump database instead.
By default, the trunc log on chkpt option is off in newly created databases. To change the default, turn this option on in the model database.
Warning!
If you set trunc log on chkpt on in model, and you need to load a set of database and transaction logs into a newly created database, be sure to turn the option off in the new database.
When the unique auto_identity index option is set to true, it adds an IDENTITY column with a unique, nonclustered index to new tables. By default, the IDENTITY column is a 10-digit numeric datatype, but you can change this default with the size of auto identity column configuration parameter.
Though you can set unique auto_identity index to true in tempdb, it is not recognized or used, and temporary tables created there do not automatically include an IDENTITY column with a unique index.
The unique auto_identity index option provides a mechanism for creating tables that have an automatic IDENTITY column with a unique index that can be used with updatable cursors. The unique index on the table ensures that the cursor is positioned at the correct row after a fetch. (If you are using isolation level 0 reads and need to make logically nonunique indexes internally unique so that they can process updatable cursors, use the identity in nonunique index option.)
In some cases, the unique auto_identity index option can avoid the Halloween Problem for the following reasons:
Users cannot update an IDENTITY column; hence, it cannot be used in the cursor update.
The IDENTITY column is automatically created with a unique, nonclustered index so that it can be used for the updatable cursor scan.
For more information about the Halloween Problem, IDENTITY columns, and cursors, see the Transact-SQL User's Guide.
Do not confuse the unique auto_identity index option with the identity in nonunique index option, which is used to make all indexes in a table unique by including an IDENTITY column in the table's index keys.
Only a System Administrator or the Database Owner can change a user's database options by executing sp_dboption. Users aliased to the Database Owner cannot change database options with sp_dboption.
You must be using the master database to execute sp_dboption. Then, for the change to take effect, you must issue the checkpoint command while using the database for which the option was changed.
Remember that you cannot change any master database options.
To change pubs2 to read only:
use master sp_dboption pubs2, "read only", true
Then, run the checkpoint command in the database that was changed:
use pubs2 checkpoint
For the optname parameter of sp_dboption, Adaptive Server understands any unique string that is part of the option name. To set the trunc log on chkpt option:
use master sp_dboption pubs2, trunc, true
If you enter an ambiguous value for optname, an error message is displayed. For example, two of the database options are dbo use only and read only. Using "only" for the optname parameter generates a message because it matches both names. The complete names that match the string supplied are printed out so that you can see how to make the optname more specific.
You can turn on more than one database option at a time. You cannot change database options inside a user-defined transaction.
Use sp_helpdb to determine the options that are set for a particular database. sp_helpdb lists each active option in the "status" column of its output.
The following example shows that the read only option is turned on in mydb:
sp_helpdb mydb
name db_size owner dbid created status ----- ------- ----- ---- ------------ ---------------------- mydb 2.0 MB sa 5 Mar 05, 1999 read only device_fragments size usage free kbytes ----------------- ------ ------------ ------------- master 2.0 MB data and log 576 device segment ------------------------------ ------------------------------ master default master logsegment master system name attribute_class attribute int_value char_value comments ----- --------------- ---------- --------- -------------------- --------- pubs2 buffer manager cache name NULL cache for database mydb NULL
To display a summary of the options for all databases, use sp_helpdb without specifying a database:
sp_helpdb
name db_size owner dbid created status ----------- ------- ----- ---- ----------- ---------------------------
mydb 2.0 MB sa 5 May 10, 1997 read only
master 3.0 MB sa 1 Jan 01, 1997 no options set
model 2.0 MB sa 3 Jan 01, 1997 no options set
sybsystemprocs 2.0 MB sa 4 Mar 31, 1995 trunc log on chkpt tempdb 2.0 MB sa 2 May 04, 1998 select into/bulkcopy/pllsort
|
|