Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Database-level properties

The following table lists properties available for each database on the server.

To retrieve the value of a database property

• Use the DB_PROPERTY system function. For example, the following statement returns the page size of the current database:

  SELECT DB_PROPERTY ( 'PageSize' );

To retrieve the values of all database properties

• Use the sa_db_properties system procedure:

  CALL sa_db_properties;

See also

• DB_EXTENDED_PROPERTY function [System]
• Server-level properties
• Connection-level properties

Descriptions

Property	Description
AccentSensitive	Returns the status of the accent sensitivity feature. Returns Yes if the database is accent sensitive, No if it is not, or FRENCH if it is using French sensitivity rules.
Alias	Returns the database name.
AlternateServerName	Returns the alternate server named associated with the database if one was specified. See -sn database option.
ArbiterState	Returns one of the following values: NULL  You are connected to a database that is not mirrored. connected  The arbiter server is connected to the primary server. disconnected  The arbiter server is not connected to the primary server. See Introduction to database mirroring.
AuditingTypes	Returns the types of auditing currently enabled. See auditing option [database]
BlankPadding	Returns On if the database has blank padding enabled. Otherwise, it returns Off.
CacheHits	Returns the number of database page lookups satisfied by finding the page in the cache.
CacheRead	The number of database pages that have been looked up in the cache.
CacheReadIndInt	Returns the number of index internal-node pages that have been read from the cache.
CacheReadIndLeaf	Returns the number of index leaf pages that have been read from the cache.
CacheReadTable	Returns the number of table pages that have been read from the cache.
Capabilities	Returns the capability bits enabled for the database. This property is primarily for use by technical support.
CaseSensitive	Returns the status of the case sensitivity feature. Returns On if the database is case sensitive. Otherwise, it returns Off. In case sensitive databases, data comparisons are case sensitive. This setting does not affect the case sensitivity of identifiers. Passwords are always case sensitive. See Case sensitivity.
CatalogCollation	Returns the identifier for the collation used for the catalog. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
CharSet	Returns the CHAR character set of the database. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
CheckpointLogBitmapPagesWritten	Returns the number of writes to the checkpoint log bitmap.
CheckpointLogBitmapSize	Returns the checkpoint log bitmap size.
CheckpointLogCommitToDisk	Returns the number of checkpoint log commits to disk.
CheckpointLogPageInUse	Returns the number of checkpoint log pages in use.
CheckpointLogPagesRelocated	Returns the number of relocated checkpoint log pages.
CheckpointLogPagesWritten	Returns the number of checkpoint log pages that have been written.
CheckpointLogSavePreimage	Returns the rate at which the pre-images of database pages are being added to the checkpoint log.
CheckpointLogSize	Returns the size of the checkpoint log, in pages.
CheckpointLogWrites	Returns the number of writes to the checkpoint log.
CheckpointUrgency	Returns the time that has elapsed since the last checkpoint, as a percentage of the checkpoint time setting of the database.
Checksum	Returns On if database page checksums are enabled for the database. Otherwise, returns Off. Checksums are always present for critical pages.
Chkpt	Returns the number of checkpoints that have been performed.
ChkptFlush	Returns the number of ranges of adjacent pages written out during a checkpoint.
ChkptPage	Returns the number of transaction log checkpoints.
CleanablePagesAdded	Returns the number of pages marked to be cleaned since database server startup.
CleanablePagesCleaned	Returns the number of database pages cleaned since database server startup.
CleanableRowsAdded	Returns the number of rows marked to be deleted since database server startup.
CleanableRowsCleaned	Returns the number of shadow table rows deleted since database server startup.
Collation	Returns the collation used by the database. For a list of available collations, see Supported and alternate collations. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
CommitFile	Returns the number of times the server has forced a flush of the disk cache. On Windows and NetWare platforms, the disk cache doesn't need to be flushed if unbuffered (direct) I/O is used.
ConnCount	Returns the number of connections to the database.
ConnsDisabled	Returns On if connections to the current database are disabled, otherwise, returns Off.
CurrentRedoPos	Returns the current offset in the transaction log file where the next database operation is to be logged.
CurrIO	Returns the current number of file I/Os that were issued by the server but haven't yet completed.
CurrRead	Returns the current number of file reads that were issued by the server, but haven't yet completed.
CurrWrite	Returns the current number of file writes that were issued by the server, but haven't yet completed.
DatabaseCleaner	Returns On or Off to indicate whether the database cleaner is enabled.
DBFileFragments	Returns the number of database file fragments. This property is supported on Windows.
DiskRead	Returns the number of pages that have been read from disk.
DiskReadIndInt	Returns the number of index internal-node pages that have been read from disk.
DiskReadIndLeaf	Returns the number of index leaf pages that have been read from disk.
DiskReadTable	Returns the number of table pages that have been read from disk.
DiskWrite	Returns the number of modified pages that have been written to disk.
DriveType	Returns the type of drive on which the database file is located. The value is one of the following: CD, FIXED, RAMDISK, REMOTE, REMOVABLE, or UNKNOWN. On Unix, depending on the version of Unix and the type of drive, it may not be possible to determine the drive type. In these cases UNKNOWN is returned. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
Encryption	Returns the type of encryption used for database or table encryption, one of None, Simple, AES, or AES_FIPS.
EncryptionScope	Returns the part of the database, if any, that can be encrypted. The value is one of the following: TABLE, DATABASE, or NONE. TABLE indicates that table encryption is enabled. DATABASE indicates that the whole database is encrypted. NONE indicates that table encryption is not enabled, and the database is not encrypted.
ExprCacheAbandons	Returns the number of time that the expression cache was completely abandoned because the hit rate was too low.
ExprCacheDropsToReadOnly	Returns the number of times that the expression cache dropped to read-only status because the hit rate was low.
ExprCacheEvicts	Returns the number of evictions from the expression cache.
ExprCacheHits	Returns the number of hits in the expression cache.
ExprCacheInserts	Returns the number of values inserted into the expression cache.
ExprCacheLookups	Returns the number of lookups performed in the expression cache.
ExprCacheResumesOfReadWrite	Returns the number of times that the expression cache resumed read-write status because the hit rate increased.
ExprCacheStarts	Returns the number of times the expression cache was started.
ExtendDB	Returns the number of pages by which the database file has been extended.
ExtendTempWrite	Returns the number of pages by which temporary files have been extended.
File	Returns the file name of the database root file, including path. This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System]
FileSize	Returns the file size of the system dbspace, in pages. This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System]
FreePages	Returns the number of free pages in the system dbspace. The FreePages property is only supported on databases created with version 8.0.0 or later. This property has extensions that you can specify when querying for property value. See DB_EXTENDED_PROPERTY function [System]
FullCompare	Returns the number of comparisons that have been performed beyond the hash value in an index.
GetData	Returns the number of GETDATA requests.
GlobalDBID	Returns the value of the global_database_id option used to generate unique primary key values in a replication environment.
HashForcedPartitions	Returns the number of times that a hash operator was forced to partition because of competition for memory.
HasCollationTailoring	Returns a response indicating whether collation tailoring was specified when the database was created. Possible values are On or Off.
HashRowsFiltered	Returns the rate at which probe rows are rejected by bit-vector filters.
HashRowsPartitioned	Returns the rate at which rows are written to hash work tables.
HashWorkTables	Returns the number of work tables created for hash-based operations.
IdentitySignature	Reserved.
IdleCheck	Returns the number of times that the server's idle thread has become active to do idle writes, idle checkpoints, and so on.
IdleChkpt	Returns the number of checkpoints completed by the server's idle thread. An idle checkpoint occurs whenever the idle thread writes out the last dirty page in the cache.
IdleChkTime	Returns the number of hundredths of a second spent checkpointing during idle I/O.
IdleWrite	Returns the number of disk writes that have been issued by the server's idle thread.
IndAdd	Returns the number of entries that have been added to indexes.
IndLookup	Returns the number of entries that have been looked up in indexes.
IOParallelism	Returns the estimated number of simultaneous I/O operations supported by the dbspace. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
IOToRecover	Returns the estimated number of I/O operations required to recover the database.
IQStore	IQ Store is on/off. The value returned is always Off for SQL Anywhere.
JavaVM	Returns the Java VM the database server uses to execute Java in the database.
Language	Returns a comma-separated list of languages known to be supported by the database collation. The languages are in two-letter ISO format. If the language isn't known, the return value is NULL. For a list of the two-letter ISO format language names and the language they correspond to, see Understanding the locale language.
LockCount	Returns the number of locks held by the connection.
LockTablePages	Returns the number of pages used to store lock information.
LogFileFragments	Returns the number of log file fragments. This property is supported on Windows.
LogFreeCommit	Returns the number of Redo Free Commits. A Redo Free Commit occurs when a commit of the transaction log is requested but the log has already been written (so the commit was done for free).
LogMirrorName	Returns the file name of the transaction log mirror, including path.
LogName	Returns the file name of the transaction log, including path.
LogWrite	Returns the number of pages that have been written to the transaction log.
LTMGeneration	Returns the generation number of the LTM or Replication Agent. This property is primarily for use by technical support.
LTMTrunc	Returns the minimal confirmed log offset for the Replication Agent.
MapPages	Returns the number of map pages used for accessing the lock table, frequency table, and table layout.
MaxIO	Returns the maximum value that CurrIO has reached.
MaxRead	Returns the maximum value that CurrRead has reached.
MaxWrite	Returns the maximum value that CurrWrite has reached.
MirrorState	Returns one of the following values: null  You are connected to a database that is not mirrored. synchronizing  The mirror server is not connected or has not yet read all of the primary's log pages. This value is also returned if the synchronization mode is asynchronous. synchronized  The mirror server is connected and has all changes that have been committed on the primary server. See Introduction to database mirroring.
MultiByteCharSet	Returns On if the database uses a multibyte character set. Otherwise, returns Off.
Name	Returns the database name (identical to Alias).
NcharCharSet	Returns the NCHAR character set of the database.
NcharCollation	Returns the name of the collation used for NCHAR data. This property has extensions that you can specify when querying the property value. See DB_EXTENDED_PROPERTY function [System].
NextScheduleTime	Returns the next scheduled execution time for a specified event; query this property using the DB_EXTENDED_PROPERTY function. See DB_EXTENDED_PROPERTY function [System].
PageRelocations	Returns the number of relocatable heap pages that have been read from the temporary file.
PageSize	Returns the page size of the database, in bytes.
PartnerState	Returns one of the following values: NULL  You are connected to a database that is not mirrored. connected  The mirror server is connected to the primary server. disconnected  The mirror server is not connected to the primary server. See Introduction to database mirroring.
PreserveSource	Returns On. This property is deprecated.
ProcedurePages	Returns the number of relocatable heap pages that have been used for procedures.
ProcedureProfiling	Returns On if procedure profiling is turned on for the database. Otherwise, returns Off.
QueryBypassed	Returns the number of requests reused from the plan cache.
QueryCachedPlans	Returns the number of cached execution plans across all connections.
QueryCachePages	Returns the number of pages used to cache execution plans.
QueryJHToJNLOptUsed	Returns the number of times a hash join was converted to a nested loops join.
QueryLowMemoryStrategy	Returns the number of times the server changed its execution plan during execution as a result of low memory conditions. The strategy can change because less memory is available than the optimizer estimated, or because the execution plan required more memory than the optimizer estimated.
QueryOptimized	Returns the number of requests fully optimized.
QueryRowsBufferFetch	Returns the number of rows fetched using buffering.
QueryRowsMaterialized	Returns the rate at which rows are written to work tables during query processing.
ReadOnly	Returns On if the database is being run in read-only mode. Otherwise, returns Off.
ReceivingTracingFrom	Returns the name of the database from which the tracing data is coming. Returns a blank string if tracing is not attached.
RecoveryUrgency	Returns an estimate of the amount of time required to recover the database as a percentage of the recovery time setting of the database. See -gr server option.
RecursiveIterations	Returns the number of iterations for recursive unions.
RecursiveIterationsHash	Returns the number of times recursive hash join used a hash strategy.
RecursiveIterationsNested	Returns the number of times recursive hash join used a nested loops strategy.
RecursiveJNLMisses	Returns the number of index probe cache misses for recursive hash join.
RecursiveJNLProbes	Returns the number of times recursive hash join attempted an index probe.
RelocatableHeapPages	Returns the number of pages used for relocatable heaps (cursors, statements, procedures, triggers, views, and so on.).
RemoteTrunc	Returns the minimal confirmed log offset for the SQL Remote Message Agent.
RollbackLogPages	Returns the number of pages in the rollback log.
SendingTracingTo	Returns the connection string where the tracing data is being sent. Returns a blank string if tracing is not attached.
SnapshotCount	Returns the number of snapshots associated with the connection.
SnapshotIsolationState	Returns one of the following values: On  snapshot isolation is enabled for the database. Off  snapshot isolation is disabled for the database. in_transition_to_on  snapshot isolation will be enabled once the current transactions complete. in_transition_to_off  snapshot isolation will be disabled once the current transactions complete. See allow_snapshot_isolation option [database].
SortMergePasses	Returns the number of merge passes used during sorting.
SortRowsMaterialized	Returns the rate at which rows are written to sort work tables.
SortRunsWritten	Returns the number of sorted runs written during sorting.
SortSortedRuns	Returns the number of sorted runs created during run formation.
SortWorkTables	Returns the number of work tables created for sorting.
SyncTrunc	Returns the minimal confirmed log offset for the MobiLink client dbmlsync executable.
TempFileName	Returns the file name of the database temporary file, including path.
TempTablePages	Returns the number of pages in the temporary file used for temporary tables.
TriggerPages	Returns the number of relocatable heap pages used for triggers.
UniqueIdentifier	Returns On. This property is deprecated.
VersionStorePages	Returns the number of pages in the temporary file that are being used for the row version store when snapshot isolation is enabled.
ViewPages	Returns the number of relocatable heap pages used for views.
XPathCompiles	Returns the number of times any XPath query (using the openxml procedure) was compiled by the database server since database server startup.