The MS SQL Database Detail attribute group contains attributes that you can use to monitor the performance and usage of MS SQL database.
AccessibleAccess validation of the database. The following values are valid:
Chapter 4. Attributes reference 129
External value Internal value Description
Not Collected -1 The value cannot be collected.
Yes 0 The database is accessible.
No 1 The database is not accessible.
Note: When the database is in offline, restoring, or transition state, the value of this attribute is displayed as No.
Active TransactionsThe number of active transactions for the database. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Aggregate Database Freespace (MB)The total amount of freespace (in MB) that is available in all the data files and on the storage device. The value format is a decimal number with two decimal places allowed. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Aggregate Database Freespace PctThe percentage of the total amount of freespace that is available in all the data files and on the storage device out of the total space allowed. The value format is a decimal number with two decimal places allowed. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Data File NamesThe names of all physical files for data that make up this database.
Data Files Freespace (MB)The amount of freespace (in MB) that is available in all the data files. The value format is a decimal number with two decimal places allowed. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Data FreespaceThe number of megabytes (MB) of free space for the database. The value includes the megabytes of free data only. The value does not include the free space available in the database. The value format is a decimal number with two decimal places allowed; for example, 10.00. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Data Freespace PercentThe percentage of maximum available free space for the database. The value format is a percentage with two decimal places allowed; for example, 20.00. The product calculates this percentage from the maximum available free space for the database and the amount of space already allocated for the database. Consider using the alter database command to expand the database size. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Note: Calculating value for this attribute depends on the database file space configuration options that you specify when creating a database. Depending on these options, the SQL Server agent uses a different algorithm to calculate the data freespace percent.
Option one:Database created with the dynamic growth option This algorithm considers the hard disk drive freespace for calculation.
Formula for calculation:
Data Freespace Percent = (DB freespace/DB max size) X 100 Where:
v DB freespace is the total free space on all hard disk drives that contain the database files v DB max size = total allocated database file size + database freespace
Option two:Database created with a fixed maximum size and the database grows dynamically till the maximum limit is reached.
This algorithm considers the maximum size of the database for calculation.
Formula for calculation:
Data Freespace Percent = (DB freespace/DB max size) X 100 Where:
v DB freespace = (DB max size - DB current size)
v DB max size and DB current size read from the sysfiles table Option three:Database created with the no growth option
This algorithm differs from the first two options. The database maximum size is the initial file size of the database at the time of database creation. The freespace is the freespace in the database file. In the first two options, reports freespace is derived from the physical hard disk drive space.
Formula for calculation:
Data Freespace Percent = (DB freespace/DB max size) X 100 Where:
v DB freespace = unallocated database pages in the database file v DB max size and DB current size read from the sysfiles table
Data SizeThe number of megabytes (MB) allocated for the data only segments of the database. The value format is a decimal number with two decimal places allowed; for example, 50.00. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Chapter 4. Attributes reference 131
Database Growth PercentThe percentage of growth for the database from the last sample to the current sample. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Database NameThe name of the database. The value format is an alphanumeric string with a maximum of 30 characters; for example, KOQ3. Each database name is unique. The SQL server also assigns each database its own identification number. The following value is valid:
External value Internal value Description
No_Data nodata No data is collected.
Database Maximum Growth SizeThe maximum size to which the database can grow in MB. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Database Name (Unicode)The name of the database. Valid values include text strings with a maximum of 384 bytes; for example, KOQ3. Each database name is unique. The SQL server also assigns each database its own identification number.
Database Space Percent UsedThe amount of space (in megabytes) used in the database as a percentage of total space allowed. Use this attribute to warn you when you need to extend the database. If you run out of space, you can no longer use the database. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Database StateReports the database state. The possible values for this attribute are:
Value Description
Online Database is available for access.
Offline Database is unavailable.
Restoring One or more files of the primary filegroup are being restored,
or one or more secondary files are being restored offline.
Recovering Database is being recovered.
Recovery Pending SQL Server has encountered a resource-related error during recovery.
Suspect At least the primary filegroup is suspect and might be
damaged.
Emergency User has changed the database and set the status to
Emergency.
When the agent fails to collect data, the following value is displayed:
External value Internal value Description
No_Data nodata No data is collected.
Database StatusReports when the database is unavailable. If a database is offline, you cannot access it.
Use this attribute to warn that the database has become unavailable. When the database is in transition state, the database status is displayed as Available. The following values are valid:
External value Internal value Description
Available 0 The database is available.
Not Available 1 The database is not available.
DB IDThe ID for the database. The value format is an alphanumeric string with a maximum of 12 characters; for example, 2156. This value is stored in the sysdatabases table. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
DB OwnerThe SQL server-assigned user ID for the owner of the database. The value format is an alphanumeric string with a maximum of 8 characters; for example, DBO. Use the create database command to establish this identifier.
DB Owner (Unicode)The SQL server-assigned user ID for the owner of the database. Valid values include text strings with a maximum of 72 bytes. Use the create database command to establish this identifier.
DBO Only AccessIndicates whether the database has a status of DBO only. The following value is valid:
External value Internal value Description
No No Database can be accessed by
authorized users.
Yes Yes Database has a status of DBO only.
Dump Tran DateThe timestamp that indicates the date on which the dump transaction command was last executed for the database. The dump transaction command copies the uncommitted transactions in the transaction log. Refer to this date to determine when the latest backup of the transaction log was made. This attribute value may not be available for the SQL Server to capture. The format is
YYYYMMDD, where:
Format Description
YYYY Year
MM Month
DD Day
For example, 20020501 indicates May 1, 2002. The following value is valid:
External value Internal value Description
Not Collected nodata The value cannot be collected.
Error StatusIndicates whether the database has an error status. A database with an error status has a status of suspect, crashed, or recovery. Check the status bits in the sysdatabases table to determine the cause of the error. Use the database consistency checker (dbcc) to verify the database integrity. The following values are valid:
Chapter 4. Attributes reference 133
External value Internal value Description
No No Database does not have an error
status.
Yes Yes Database has an error status.
Free Space Accounting SuppressedIndicates whether the free space accounting option is disabled for the database. The no-free-space-actg option turns off free space accounting on non-log segments only.
Information about free space is inaccurate when free space accounting is turned off. Use the
no-free-space-actg option and the checkpoint command to speed recovery. No time is needed to count free space for non-log segments. The following values are valid:
External value Internal value Description
No No Free space accounting is enabled.
Yes Yes Free space accounting is disabled.
Host NameThe name of the computer on which the SQL Server is running. The value format is an alphanumeric string with a maximum of 64 characters, for example, Voyager. This attribute is not available for use in situations or for historical data collection.
Hub TimestampThe time when this data was inserted at the hub. This attribute is not available for use in situations. Otherwise, this attribute is available to use like any other attribute, for example it is available for reports, queries, and workspaces.
Last Database SizeThe database size in MB from the previous sample. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Log Bytes Flushed per SecThe number of bytes of the log file that are flushed for the current interval.
The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Log File NamesThe names of all physical files for logs that make up this database.
Log FreespaceThe number of megabytes (MB) of free space in the database transaction log. The value includes the number of megabytes of free space on the log-only partitions. The value format is a decimal number with two decimal places allowed; for example, 8.00. Various types of transactions, such as mass updates and bulk copying involve extensive logging. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Note:
The value of this attribute depends on the log file configuration settings that you specify when creating a database. Depending on the configuration settings, the SQL Server agent uses different algorithms to calculate the log free space.
Option one:When the value of the auto-growth property in the log file is disabled.
This algorithm considers the free space inside the log file for calculation of the attribute value.
Free space inside log file = Log Size in KB - (hard disk drive space used by the log file * 8) Log Freespace (MB) = (Free space inside log file) / 1024
Option two:When the value of the auto-growth property in the log file is enabled.
This algorithm considers the Maximum Log Freespace for calculation of the attribute value. The value of the Maximum Log free space depends on the mode in which the auto-growth property is enabled.
v When auto-growth property is enabled in the restricted mode:
If (Maximum File Size – Log Size) > Free space on the hard disk drive Maximum Log Freespace = Free space on the hard disk drive
Else
Maximum Log Freespace = (Maximum File Size – Log Size) v When auto-growth property is enabled in the unrestricted mode:
Maximum Log Freespace = Free space on the hard disk drive
Log Freespace (MB) = (Maximum Log Freespace + Free space inside the log file) / 1024
Log Freespace PercentThe percentage of free space in the database transaction log. The value format is a percentage with two decimal places; for example, 20.00. Various types of transactions, such as mass updates and bulk copying involve extensive logging. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Note:
The value of this attribute depends on the log file configuration settings that you specify when creating a database. Depending on the configuration settings, the SQL Server agent uses different algorithms to calculate the log free space percent.
Option one:When the value of the auto-growth property in the log file is disabled.
This algorithm considers the free space inside the log file for calculation of the attribute value.
Free space inside log file = Log Size in KB - (hard disk drive space used by the log file (KB)) Log Freespace Percent = ((Free space inside log file) / Log Size in KB ) * 100
Option two:When the value of the auto-growth property in the log file is specified as enabled
This algorithm considers the Maximum Log Freespace for calculation of the attribute value. The value of the Maximum Log free space depends on the mode in which the auto-growth property is enabled.
v When auto-growth property is enabled in the restricted mode:
If (Maximum File Size – Log Size) > Free space on the hard disk drive Maximum Log Freespace = Free space on the hard disk drive
Else
Maximum Log Freespace = (Maximum File Size – Log Size)
Chapter 4. Attributes reference 135
v When auto-growth property is enabled in the unrestricted mode:
Maximum Log Freespace = Free space on the hard disk drive
Log Freespace Percent = ((Maximum Log Freespace + Free space inside log file) / (Log Size in KB + Maximum Log Freespace )) * 100
Log GrowthsThe total number of times the database transaction log is expanded since the SQL Server was started. The format is an integer. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Log Maximum Growth SizeThe maximum size to which the log can grow in MB. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Note:
The value of this attribute depends on the log file configuration settings that you specify when creating a database. Depending on the configuration settings, the SQL Server agent uses different algorithms to calculate the log maximum growth size.
Option one:When the value of the auto-growth property in the log file is disabled.
This algorithm considers the free space inside the log file for calculation of the attribute value.
Free space inside log file = Log Size (KB) - (hard disk drive space used by the log file (KB)) Log Maximum Growth Size (MB) = Log Size (KB) / 1024
Option two:When the value of the auto-growth property in the log file is enabled.
This algorithm considers the Maximum Log Freespace for calculation of the attribute value. The value of Maximum Log free space depends on the mode in which the auto-growth property is enabled.
v When auto-growth property is enabled in the restricted mode:
If (Maximum File Size – Log Size) > Free space on the hard disk drive Maximum Log Freespace (KB) = Free space on the hard disk drive (KB) Else
Maximum Log Freespace (KB) = (Maximum File Size (KB) – Log Size (KB)) v When auto-growth property is enabled in the unrestricted mode:
Maximum Log Freespace (KB) = Free space on the hard disk drive
Log Maximum Growth Size (MB) = (Log size (KB) + Maximum Log Freespace (KB)) / 1024
Log SizeThe number of megabytes (MB) allocated for the transaction log for the database. The value includes the number of megabytes allocated for the transaction log on the log only partitions. The value format is a decimal number with two decimal places allowed; for example, 500. The appropriate size for a transaction log depends on how the database is used. Several factors, such as the number of
transactions, type of transactions, and number of users, effect sizing. Evaluate the need to truncate the transaction log to prevent it from filling up. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Log Space Percent UsedThe percentage of the transaction log that is full. When the log has filled up all of its allocated space, transactions that require logging are rejected. Use this attribute to set an alert based on a percent full threshold, then spawn a task to dump or truncate the transaction log or to do both. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Note:
Log Space Percent Used = 100 – Log Freespace Percent
Long Running Transaction NameThe long running transaction name. The following value is valid:
External value Internal value Description
No Data nodata No data is collected.
Long Running Transaction Process IDThe longest running transaction. The Long running transactions are transactions that are open for longer than the LongRunningTransColl standard collector parameter.
The format is an integer. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Long Running Transaction Time Per SecThe time for which the longest running transaction is active (in seconds). The format is an integer. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
No CKPT After RecoveryIndicates whether a record for the checkpoint is added to the transaction log when the database is recovered. The following values are valid:
External value Internal value Description
No No No CKPT After Recovery option is
disabled.
Yes Yes No CKPT After Recovery option is
enabled.
Oldest Open TransactionThe age (in minutes) of the oldest open transaction in the database transaction log. Use this attribute to determine up to which point in time the transaction log can be truncated and backed up. The following value is valid:
External value Internal value Description
Not Collected -1 The value cannot be collected.
Chapter 4. Attributes reference 137
OriginnodeThe key for the table view in the format serverid:hostname:MSS. This attribute is not available for use in situations. Otherwise, this attribute is available to use like any other attribute, for example it is available for reports, queries, and workspaces.
Page VerifyThe option that identifies and notifies incomplete I/O transactions that have occurred because of disk I/O errors. The following values are valid:
External value Internal value
NONE 0
TORN PAGE DETECTION 1
CHECKSUM 2
Not collected -1
Read Only AccessIndicates whether the database has a status of read only. The following value is valid:
External value Internal value Description
No No Database is read and write database.
Yes Yes Database has a status of read-only.
Recovery ModelThe method used to maintain the transaction log. The three types of recovery models include Simple, Full, and Bulk-logged. The following values are valid:
External value Internal value
FULL 1
BULK LOGGED 2
SIMPLE 3
Not collected -1
Replicated Transaction RateThe rate per second at which transactions have been read out of the publication database transaction log and delivered to the distribution database. Use this attribute to
Replicated Transaction RateThe rate per second at which transactions have been read out of the publication database transaction log and delivered to the distribution database. Use this attribute to