December 6, 2012

Databases and Files Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.backup_devices

backup-device registered by using sp_addumpdevice or created in SQL Server Management Studio.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of the backup device. Is unique in the set.
type tinyint Type of backup device:
2 = Disk
3 = Diskette (obsolete)
5 = Tape
6 = Pipe (obsolete)
7 = Virtual device (for optional use by third-party backup vendors)
Typically, only disk (2) and tape (5) are used.
type_desc nvarchar(60) Description of backup device type:
DISK
DISKETTE (obsolete)
TAPE
PIPE (obsolete)
VIRTUAL_DEVICE (for optional use by third party backup vendors)
Typically, only DISK and TAPE are used.
physical_name nvarchar(260) Physical file name or path of the backup device.

TSQL

Sql 2005
SELECT [name], [type], [type_desc], [physical_name] FROM sys.backup_devices
Sql 2008
SELECT [name], [type], [type_desc], [physical_name] FROM sys.backup_devices
Sql 2008 R2
SELECT [name], [type], [type_desc], [physical_name] FROM sys.backup_devices
Sql 2012
SELECT [name], [type], [type_desc], [physical_name] FROM sys.backup_devices

Back to Top


sys.database_files

Contains a row per file of a database as stored in the database itself. This is a per-database view.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
file_id int ID of the file within database.
file_guid uniqueidentifier GUID for the file.
NULL = Database was upgraded from an earlier version of Microsoft SQL Server.
type tinyint File type:
0 = Rows
1 = Log
2 = Reserved for future use.
3 = Reserved for future use.
4 = Full-text
type_desc nvarchar(60) Description of the file type:
ROWS
LOG
FULLTEXT
data_space_id int ID of the data space to which this file belongs. Data space is a filegroup.
0 = Log file.
name sysname Logical name of the file in the database.
physical_name nvarchar(260) Operating-system file name.
state tinyint File state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = Reserved for future use.
6 = OFFLINE
7 = DEFUNCT
state_desc nvarchar(60) Description of the file state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
OFFLINE
DEFUNCT
For more information, see File States1.
size int Current size of the file, in 8-KB pages.
0 = Not applicable
For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.
max_size int Maximum file size, in 8-KB pages:
0 = No growth is allowed.
-1 = File will grow until the disk is full.
268435456 = Log file will grow to a maximum size of 2 TB.
Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
growth int 0 = File is fixed size and will not grow.
>0 = File will grow automatically.
If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB.
If is_percent_growth = 1, growth increment is expressed as a whole number percentage.
is_media_read_only bit 1 = File is on read-only media.
0 = File is on read-write media.
is_read_only bit 1 = File is marked read-only.
0 = File is marked read/write.
is_sparse bit 1 = File is a sparse file.
0 = File is not a sparse file.
For more information, see Understanding Sparse File Sizes in Database Snapshots2.
is_percent_growth bit 1 = Growth of the file is a percentage.
0 = Absolute growth size in pages.
is_name_reserved bit 1 = Dropped file name (name or physical_name) is reusable only after the next log backup. When files are dropped from a database, the logical names stay in a reserved state until the next log backup. This column is relevant only under the full recovery model and the bulk-logged recovery model.
create_lsn numeric(25,0) Log sequence number (LSN) at which the file was created.
drop_lsn numeric(25,0) LSN at which the file was dropped.
0 = The file name is unavailable for reuse.
read_only_lsn numeric(25,0) LSN at which the filegroup that contains the file changed from read/write to read-only (most recent change).
read_write_lsn numeric(25,0) LSN at which the filegroup that contains the file changed from read-only to read/write (most recent change).
differential_base_lsn numeric(25,0) Base for differential backups. Data extents changed after this LSN will be included in a differential backup.
differential_base_guid uniqueidentifier Unique identifier of the base backup on which a differential backup will be based.
differential_base_time datetime Time corresponding to differential_base_lsn.
redo_start_lsn numeric(25,0) LSN at which the next roll forward must start.
Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_start_fork_guid uniqueidentifier Unique identifier of the recovery fork. The first_fork_guid of the next log backup restored must match this value. This represents the current state of the file.
redo_target_lsn numeric(25,0) LSN at which the online roll forward on this file can stop.
Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_target_fork_guid uniqueidentifier The recovery fork on which the file can be recovered. Paired with redo_target_lsn.
backup_lsn numeric(25,0) The LSN of the most recent data or differential backup of the file.

TSQL

Sql 2005
SELECT [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.database_files
Sql 2008
SELECT [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.database_files
Sql 2008 R2
SELECT [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.database_files
Sql 2012
SELECT [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.database_files

Back to Top


sys.database_mirroring

Contains one row for each database in the instance of SQL Server. If the database is not ONLINE or database mirroring is not enabled, the values of all columns except database_id will be NULL. To see the row for a database other than master or tempdb, you must either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE permission in the master database. To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role. Note If a database does not participate in mirroring, all columns prefixed with "mirroring_" are NULL.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int ID of the database. Is unique within an instance of SQL Server.
mirroring_guid uniqueidentifier ID of the mirroring partnership.
NULL= Database is inaccessible or is not mirrored.
Note: If the database does not participate in mirroring, all columns prefixed with mirroring_ are NULL.
mirroring_state tinyint State of the mirror database and of the database mirroring session.
0 = Suspended
1 = Disconnected from the other partner
2 = Synchronizing
3 = Pending Failover
4 = Synchronized
NULL = Database is inaccessible or is not mirrored.
mirroring_state_desc nvarchar(60) Description of the state of the mirror database and of the database mirroring session, can be one of:
DISCONNECTED
SYNCHRONIZED
SYNCHRONIZING
PENDING_FAILOVER
SUSPENDED
NULL
For more information, see Mirroring States1.
mirroring_role tinyint Current role of the local database plays in the database mirroring session.
1 = Principal
2 = Mirror
NULL = Database is inaccessible or is not mirrored.
mirroring_role_desc nvarchar(60) Description of the role the local database plays in mirroring, can be one of:
PRINCIPAL
MIRROR
mirroring_role_sequence int The number of times that mirroring partners have switched the principal and mirror roles due to a failover or forced service.
NULL = Database is inaccessible or is not mirrored.
mirroring_safety_level tinyint Safety setting for updates on the mirror database:
0 = Unknown state
1 = Off [asynchronous]
2 = Full [synchronous]
NULL = Database is inaccessible or is not mirrored.
mirroring_safety_level_desc nvarchar Transaction safety setting for the updates on the mirror database, can be one of:
UNKNOWN
OFF
FULL
NULL
mirroring_safety_sequence int Update the sequence number for changes to transaction safety level.
NULL = Database is inaccessible or is not mirrored.
mirroring_partner_name nvarchar(128) Server name of the database mirroring partner.
NULL = Database is inaccessible or is not mirrored.
mirroring_partner_instance nvarchar(128) The instance name and computer name for the other partner. Clients require this information to connect to the partner if it becomes the principal server.
NULL = Database is inaccessible or is not mirrored.
mirroring_witness_name nvarchar(128) Server name of the database mirroring witness
NULL = No witness exists.
mirroring_witness_state tinyint State of the witness in the database mirroring session of the database, can be one of:
0 = Unknown
1= Connected
2 = Disconnected
NULL = No witness exists, the database is not online, or the database is not mirrored.
mirroring_witness_state_desc nvarchar(60) Description of state, can be one of:
UNKNOWN
CONNECTED
DISCONNECTED
NULL
mirroring_failover_lsn numeric(25,0) Log sequence number (LSN) of the latest transaction log record that is guaranteed to be hardened to disk on both partners. After a failover, the mirroring_failover_lsn is used by the partners as the point of reconciliation at which the new mirror server begins to synchronize the new mirror database with the new principal database.
mirroring_connection_timeout int Mirroring connection time out in seconds. This is the number of seconds to wait for reply from partner or witness before considering them unavailable. The default time-out value is 10 seconds.
NULL = Database is inaccessible or is not mirrored.
mirroring_redo_queue int Maximum amount of log to be redone on the mirror. If mirroring_redo_queue_type is set to UNLIMITED, which is the default setting, this column is NULL. If the database is not online, this column is also NULL.
Otherwise, this column contains the maximum amount of log in megabytes. When the maximum is reached, the log is temporarily stalled on the principal as the mirror server catches up. This feature limits failover time.
For more information, see Estimating the Interruption of Service During Role Switching2.
mirroring_redo_queue_type nvarchar(60) UNLIMITED indicates that mirroring will not inhibit the redo queue. This is the default setting.
MB for maximum size of the redo queue in mega bytes. Note that if the queue size was specified as kilobytes or gigabytes, the SQL Server 2005 Database Engine converts the value into megabytes.
If the database is not online, this column is NULL.
mirroring_end_of_log_lsn     numeric(25,0) The local end-of-log that has been flushed to disk. This is comparable to the hardened LSN from the mirror server (see the mirroring_failover_lsn column).
mirroring_replication_lsn     numeric(25,0) The maximum LSN that replication can send.

TSQL

Sql 2005
SELECT [database_id], [mirroring_guid], [mirroring_state], [mirroring_state_desc], [mirroring_role], [mirroring_role_desc], [mirroring_role_sequence], [mirroring_safety_level], [mirroring_safety_level_desc], [mirroring_safety_sequence], [mirroring_partner_name], [mirroring_partner_instance], [mirroring_witness_name], [mirroring_witness_state], [mirroring_witness_state_desc], [mirroring_failover_lsn], [mirroring_connection_timeout], [mirroring_redo_queue], [mirroring_redo_queue_type] FROM sys.database_mirroring
Sql 2008
SELECT [database_id], [mirroring_guid], [mirroring_state], [mirroring_state_desc], [mirroring_role], [mirroring_role_desc], [mirroring_role_sequence], [mirroring_safety_level], [mirroring_safety_level_desc], [mirroring_safety_sequence], [mirroring_partner_name], [mirroring_partner_instance], [mirroring_witness_name], [mirroring_witness_state], [mirroring_witness_state_desc], [mirroring_failover_lsn], [mirroring_connection_timeout], [mirroring_redo_queue], [mirroring_redo_queue_type] FROM sys.database_mirroring
Sql 2008 R2
SELECT [database_id], [mirroring_guid], [mirroring_state], [mirroring_state_desc], [mirroring_role], [mirroring_role_desc], [mirroring_role_sequence], [mirroring_safety_level], [mirroring_safety_level_desc], [mirroring_safety_sequence], [mirroring_partner_name], [mirroring_partner_instance], [mirroring_witness_name], [mirroring_witness_state], [mirroring_witness_state_desc], [mirroring_failover_lsn], [mirroring_connection_timeout], [mirroring_redo_queue], [mirroring_redo_queue_type], [mirroring_end_of_log_lsn], [mirroring_replication_lsn] FROM sys.database_mirroring
Sql 2012
SELECT [database_id], [mirroring_guid], [mirroring_state], [mirroring_state_desc], [mirroring_role], [mirroring_role_desc], [mirroring_role_sequence], [mirroring_safety_level], [mirroring_safety_level_desc], [mirroring_safety_sequence], [mirroring_partner_name], [mirroring_partner_instance], [mirroring_witness_name], [mirroring_witness_state], [mirroring_witness_state_desc], [mirroring_failover_lsn], [mirroring_connection_timeout], [mirroring_redo_queue], [mirroring_redo_queue_type], [mirroring_end_of_log_lsn], [mirroring_replication_lsn] FROM sys.database_mirroring

Back to Top


sys.database_recovery_status

database. If the database is not opened, the SQL Server Database Engine tries to start it. To see the row for a database other than master or tempdb, one of the following must apply: Be the owner of the database. Have ALTER ANY DATABASE or VIEW ANY DATABASE server-level permissions. Have CREATE DATABASE permission in the master database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int ID of the database, unique within an instance of SQL Server.
database_guid uniqueindetifier Used to relate all the database files of a database together. All files must have this GUID in their header page for the database to start as expected. Only one database should ever have this GUID, but duplicates can be created by copying and attaching databases. RESTORE always generates a new GUID when you restore a database that does not yet exist.
NULL= Database is offline, or the database will not start.
family_guid uniqueidentifier Identifier of the "backup family" for the database for detecting matching restore states.
NULL= Database is offline or the database will not start.
last_log_backup_lsn numeric(25,0) Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log backup.
NULL= No log backup exists. The database is offline or the database will not start.
recovery_fork_guid uniqueindetifier Identifies the current recovery fork on which the database is currently active.
NULL= Database is offline, or the database will not start.
first_recovery_fork_guid uniqueidentifier Identifier of the starting recovery fork.
NULL= Database is offline, or the database will not start.
fork_point_lsn numeric(25,0) If first_recovery_fork_guid is not equal (!=) to recovery_fork_guid, fork_point_lsn is the log sequence number of the current fork point. Otherwise, the value is NULL.

TSQL

Sql 2005
SELECT [database_id], [database_guid], [family_guid], [last_log_backup_lsn], [recovery_fork_guid], [first_recovery_fork_guid], [fork_point_lsn] FROM sys.database_recovery_status
Sql 2008
SELECT [database_id], [database_guid], [family_guid], [last_log_backup_lsn], [recovery_fork_guid], [first_recovery_fork_guid], [fork_point_lsn] FROM sys.database_recovery_status
Sql 2008 R2
SELECT [database_id], [database_guid], [family_guid], [last_log_backup_lsn], [recovery_fork_guid], [first_recovery_fork_guid], [fork_point_lsn] FROM sys.database_recovery_status
Sql 2012
SELECT [database_id], [database_guid], [family_guid], [last_log_backup_lsn], [recovery_fork_guid], [first_recovery_fork_guid], [fork_point_lsn] FROM sys.database_recovery_status

Back to Top


sys.databases

database in the instance of Microsoft SQL Server or the Windows Azure SQL Database server. Applies to: SQL Server (SQL Server 2012 through current version1), Windows Azure SQL Database (Initial release through current release2). If a database is not ONLINE, or AUTO_CLOSE is set to ON and the database is closed, the values of some columns may be NULL. If a database is OFFLINE, the corresponding row is not visible to low-privileged users. To see the corresponding row if the database is OFFLINE, a user must have at least the ALTER ANY DATABASE server-level permission, or the CREATE DATABASE permission in the master database.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
name sysname Name of database, unique within an instance of SQL Server.
database_id int ID of the database, unique within an instance of SQL Server.
source_database_id int Non-NULL = ID of the source database of this database snapshot.
NULL = Not a database snapshot.
owner_sid varbinary(85) SID (Security-Identifier) of the external owner of the database, as registered to the server.
create_date datetime Date the database was created or renamed. For tempdb, this value changes every time the server restarts.
compatibility_level tinyint Integer corresponding to the version of SQL Server for which behavior is compatible:
70
80
90
NULL = Database is not online, or AUTO_CLOSE is set to ON.
collation_name sysname Collation for the database. Acts as the default collation in the database.
NULL = Database is not online or AUTO_CLOSE is set to ON.
user_access tinyint User-access setting:
0 = MULTI_USER specified
1 = SINGLE_USER specified
2 = RESTRICTED_USER specified
user_access_desc nvarchar(60) Description of user-access setting:
MULTI_USER
SINGLE_USER
RESTRICTED_USER
is_read_only bit 1 = Database is READ_ONLY.
0 = Database is READ_WRITE.
is_auto_close_on bit 1 = AUTO_CLOSE is ON.
0 = AUTO_CLOSE is OFF.
is_auto_shrink_on bit 1 = AUTO_SHRINK is ON.
0 = AUTO_SHRINK is OFF.
state tinyint Database state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
state_desc nvarchar(60) Description of the database state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE
For more information, see Database States1.
is_in_standby bit Database is read-only for restore log.
is_cleanly_shutdown bit 1 = Database shutdown cleanly; no recovery required on startup.
0 = Database did not shutdown cleanly; recovery is required on startup.
is_supplemental_logging_enabled bit 1 = SUPPLEMENTAL_LOGGING is ON.
0 = SUPPLEMENTAL_LOGGING is OFF.
snapshot_isolation_state tinyint State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.
1 = Snapshot isolation state ON. Snapshot isolation is allowed.
2 = Snapshot isolation state is in transition to OFF state. All transactions have their modifications versioned. Cannot start new transactions using snapshot isolation. The database remains in the transition to OFF state until all transactions that were active when ALTER DATABASE was run can be completed.
3 = Snapshot isolation state is in transition to ON state. New transactions have their modifications versioned. Transactions cannot use snapshot isolation until the snapshot isolation state becomes 1 (ON). The database remains in the transition to ON state until all update transactions that were active when ALTER DATABASE was run can be completed.
snapshot_isolation_state_desc nvarchar(60) Description of state of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:
OFF

ON

IN_TRANSITION_TO_ON

IN_TRANSITION_TO_OFF
is_read_committed_snapshot_on bit 1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the read-committed isolation level are based on snapshot scans and do not acquire locks.
0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations under the read-committed isolation level use share locks.
recovery_model tinyint Recovery model selected:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE
recovery_model_desc nvarchar(60) Description of recovery model selected:
FULL
BULK_LOGGED
SIMPLE
page_verify_option tinyint Setting of PAGE_VERIFY option:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM
page_verify_option_desc nvarchar(60) Description of PAGE_VERIFY option setting:
NONE.TORN_PAGE_DETECTION
CHECKSUM
is_auto_create_stats_on bit 1 = AUTO_CREATE_STATISTICS is ON.
0 = AUTO_CREATE_STATISTICS is OFF.
is_auto_update_stats_on bit 1 = AUTO_UPDATE_STATISTICS is ON.
0 = AUTO_UPDATE_STATISTICS is OFF.
is_auto_update_stats_async_on bit 1 = AUTO_UPDATE_STATISTICS_ASYNC is ON.
0 = AUTO_UPDATE_STATISTICS_ASYNC is OFF.
is_ansi_null_default_on bit 1 = ANSI_NULL_DEFAULT is ON.
0 = ANSI_NULL_DEFAULT is OFF.
is_ansi_nulls_on bit 1 = ANSI_NULLS is ON.
0 = ANSI_NULLS is OFF.
is_ansi_padding_on bit 1 = ANSI_PADDING is ON.
0 = ANSI_PADDING is OFF.
is_ansi_warnings_on bit 1 = ANSI_WARNINGS is ON.
0 = ANSI_WARNINGS is OFF.
is_arithabort_on bit 1 = ARITHABORT is ON.
0 = ARITHABORT is OFF.
is_concat_null_yields_null_on bit 1 = CONCAT_NULL_YIELDS_NULL is ON.
0 = CONCAT_NULL_YIELDS_NULL is OFF.
is_numeric_roundabort_on bit 1 = NUMERIC_ROUNDABORT is ON.
0 = NUMERIC_ROUNDABORT is OFF.
is_quoted_identifier_on bit 1 = QUOTED_IDENTIFIER is ON.
0 = QUOTED_IDENTIFIER is OFF.
is_recursive_triggers_on bit 1 = RECURSIVE_TRIGGERS is ON.
0 = RECURSIVE_TRIGGERS is OFF.
is_cursor_close_on_commit_on bit 1 = CURSOR_CLOSE_ON_COMMIT is ON.
0 = CURSOR_CLOSE_ON_COMMIT is OFF.
is_local_cursor_default bit 1 = CURSOR_DEFAULT is local.
0 = CURSOR_DEFAULT is global.
is_fulltext_enabled bit 1 = Full-text is enabled for the database.
0 = Full-text is disabled for the database.
is_trustworthy_on bit 1 = Database has been marked trustworthy.
0 = Database has not been marked trustworthy.
is_db_chaining_on bit 1 = Cross-database ownership chaining is ON.
0 = Cross-database ownership chaining is OFF.
is_parameterization_forced bit 1 = Parameterization is FORCED.
0 = Parameterization is SIMPLE.
is_master_key_encrypted_by_server bit 1 = Database has an encrypted master key.
0 = Database does not have an encrypted master key.
is_published bit 1 = Database is a publication database in a transactional or snapshot replication topology.
0 = Is not a publication database.
is_subscribed bit 1 = Database is a subscription database in a replication topology.
0 = Is not a subscription database.
is_merge_published bit 1 = Database is a publication database in a merge replication topology.
0 = Is not a publication database in a merge replication topology.
is_distributor bit 1 = Database is the distribution database for a replication topology.
0 = Is not the distribution database for a replication topology.
is_sync_with_backup bit 1 = Database is marked for replication synchronization with backup.
0 = Is not marked for replication synchronization with backup.
service_broker_guid uniqueidentifier Identifier of the service broker for this database. Used as the broker_instance of the target in the routing table.
is_broker_enabled bit 1 = The broker in this database is currently sending and receiving messages.
0 = All sent messages will stay on the transmission queue and received messages will not be put on queues in this database.
By default, restored or attached databases have the broker disabled. The exception to this is database mirroring where the broker is enabled after failover.
log_reuse_wait tinyint Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other (transient)
log_reuse_wait_desc nvarchar(60) Description of reuse of transaction log space is currently waiting on one of the following:
NOTHING
CHECKPOINT
LOG_BACKUP
Note: If the reason is LOG_BACKUP, it may take two backups to actually free the space.

ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT
For more information, see Factors That Can Delay Log Truncation2.
is_date_correlation_on bit 1 = DATE_CORRELATION_OPTIMIZATION is ON.
0 = DATE_CORRELATION_OPTIMIZATION is OFF.
is_cdc_enabled   bit 1 = Database is enabled for change data capture. For more information, see sys.sp_cdc_enable_db (Transact-SQL)3.
is_encrypted   bit Indicates whether the database is encrypted (reflects the state last set by using the ALTER DATABASE SET ENCRYPTION clause). Can be one of the following values:
1 = Encrypted
0 = Not Encrypted
For more information about database encryption, see Understanding Transparent Data Encryption (TDE)4.
If the database is in the process of being decrypted, is_encrypted shows a value of 0. You can see the state of the encryption process by using the sys.dm_database_encryption_keys5 dynamic management view.
is_honor_broker_priority_on   bit Indicates whether the database honors conversation priorities (reflects the state last set by using the ALTER DATABASE SET HONOR_BROKER_PRIORITY clause). Can be one of the following values:
1 = HONOR_BROKER_PRIORITY is ON
0 = HONOR_BROKER_PRIORITY is OFF
For more information, see Conversation Priorities6.
replica_id       uniqueidentifier Unique identifier of the local AlwaysOn Availability Groups availability replica of the availability group, if any, in which the database is participating.
NULL = database is not part of an availability replica of in availability group.
group_database_id       uniqueidentifier Unique identifier of the database within an AlwaysOn availability group, if any, in which the database is participating. group_database_id is the same for this database on the primary replica and on every secondary replica on which the database has been joined to the availability group.
NULL = database is not part of an availability replica in any availability group.
default_language_lcid       smallint Indicates the local id (lcid) of the default language of a contained database.
Note Functions as the Configure the default language Server Configuration Option8 of sp_configure. This value is null for a non-contained database.
default_language_name       nvarchar(128) Indicates the default language of a contained database.
This value is null for a non-contained database.
default_fulltext_language_lcid       int Indicates the local id (lcid) of the default fulltext language of the contained database.
Note Functions as the default Configure the default full-text language Server Configuration Option9 of sp_configure. This value is null for a non-contained database.
default_fulltext_language_name       nvarchar(128) Indicates the default fulltext language of the contained database.
This value is null for a non-contained database.
is_nested_triggers_on       bit Indicates whether or not nested triggers are allowed in the contained database.
0 = nested triggers are not allowed
1 = nested triggers are allowed
Note Functions as the Configure the nested triggers Server Configuration Option10 of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL)11 for further information.
is_transform_noise_words_on       bit Indicates whether or noise words should be transformed in the contained database.
0 = noise words should not be transformed.
1 = noise words should be transformed.
Does not apply to Windows Azure SQL Database. Always returns NULL.
Note Functions as the transform noise words Server Configuration Option12 of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL)11 for further information.
two_digit_year_cutoff       smallint Indicates a value of a number between 1753 and 9999 to represent the cutoff year for interpreting two-digit years as four-digit years.
Note Functions as the Configure the two digit year cutoff Server Configuration Option13 of sp_configure. This value is null for a non-contained database. See sys.configurations (Transact-SQL)11 for further information.
containment       tinyint not null Indicates the containment status of the database.
0 = database containment is off
1 = database is in partial containment
Does not apply to Windows Azure SQL Database. Always returns 0.
containment_desc       nvarchar(60) not null Indicates the containment status of the database.
NONE = legacy database (zero containment)
PARTIAL = partially contained database
Does not apply to Windows Azure SQL Database. Always returns NONE.
target_recovery_time_in_seconds       int The estimated time to recover the database, in seconds. Nullable.
is_federation_member       bit Indicates if the database is a member of a federation.
Applies to Windows Azure SQL Database.

TSQL

Sql 2005
SELECT [name], [database_id], [source_database_id], [owner_sid], [create_date], [compatibility_level], [collation_name], [user_access], [user_access_desc], [is_read_only], [is_auto_close_on], [is_auto_shrink_on], [state], [state_desc], [is_in_standby], [is_cleanly_shutdown], [is_supplemental_logging_enabled], [snapshot_isolation_state], [snapshot_isolation_state_desc], [is_read_committed_snapshot_on], [recovery_model], [recovery_model_desc], [page_verify_option], [page_verify_option_desc], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_ansi_null_default_on], [is_ansi_nulls_on], [is_ansi_padding_on], [is_ansi_warnings_on], [is_arithabort_on], [is_concat_null_yields_null_on], [is_numeric_roundabort_on], [is_quoted_identifier_on], [is_recursive_triggers_on], [is_cursor_close_on_commit_on], [is_local_cursor_default], [is_fulltext_enabled], [is_trustworthy_on], [is_db_chaining_on], [is_parameterization_forced], [is_master_key_encrypted_by_server], [is_published], [is_subscribed], [is_merge_published], [is_distributor], [is_sync_with_backup], [service_broker_guid], [is_broker_enabled], [log_reuse_wait], [log_reuse_wait_desc], [is_date_correlation_on] FROM sys.databases
Sql 2008
SELECT [name], [database_id], [source_database_id], [owner_sid], [create_date], [compatibility_level], [collation_name], [user_access], [user_access_desc], [is_read_only], [is_auto_close_on], [is_auto_shrink_on], [state], [state_desc], [is_in_standby], [is_cleanly_shutdown], [is_supplemental_logging_enabled], [snapshot_isolation_state], [snapshot_isolation_state_desc], [is_read_committed_snapshot_on], [recovery_model], [recovery_model_desc], [page_verify_option], [page_verify_option_desc], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_ansi_null_default_on], [is_ansi_nulls_on], [is_ansi_padding_on], [is_ansi_warnings_on], [is_arithabort_on], [is_concat_null_yields_null_on], [is_numeric_roundabort_on], [is_quoted_identifier_on], [is_recursive_triggers_on], [is_cursor_close_on_commit_on], [is_local_cursor_default], [is_fulltext_enabled], [is_trustworthy_on], [is_db_chaining_on], [is_parameterization_forced], [is_master_key_encrypted_by_server], [is_published], [is_subscribed], [is_merge_published], [is_distributor], [is_sync_with_backup], [service_broker_guid], [is_broker_enabled], [log_reuse_wait], [log_reuse_wait_desc], [is_date_correlation_on], [is_cdc_enabled], [is_encrypted], [is_honor_broker_priority_on] FROM sys.databases
Sql 2008 R2
SELECT [name], [database_id], [source_database_id], [owner_sid], [create_date], [compatibility_level], [collation_name], [user_access], [user_access_desc], [is_read_only], [is_auto_close_on], [is_auto_shrink_on], [state], [state_desc], [is_in_standby], [is_cleanly_shutdown], [is_supplemental_logging_enabled], [snapshot_isolation_state], [snapshot_isolation_state_desc], [is_read_committed_snapshot_on], [recovery_model], [recovery_model_desc], [page_verify_option], [page_verify_option_desc], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_ansi_null_default_on], [is_ansi_nulls_on], [is_ansi_padding_on], [is_ansi_warnings_on], [is_arithabort_on], [is_concat_null_yields_null_on], [is_numeric_roundabort_on], [is_quoted_identifier_on], [is_recursive_triggers_on], [is_cursor_close_on_commit_on], [is_local_cursor_default], [is_fulltext_enabled], [is_trustworthy_on], [is_db_chaining_on], [is_parameterization_forced], [is_master_key_encrypted_by_server], [is_published], [is_subscribed], [is_merge_published], [is_distributor], [is_sync_with_backup], [service_broker_guid], [is_broker_enabled], [log_reuse_wait], [log_reuse_wait_desc], [is_date_correlation_on], [is_cdc_enabled], [is_encrypted], [is_honor_broker_priority_on] FROM sys.databases
Sql 2012
SELECT [name], [database_id], [source_database_id], [owner_sid], [create_date], [compatibility_level], [collation_name], [user_access], [user_access_desc], [is_read_only], [is_auto_close_on], [is_auto_shrink_on], [state], [state_desc], [is_in_standby], [is_cleanly_shutdown], [is_supplemental_logging_enabled], [snapshot_isolation_state], [snapshot_isolation_state_desc], [is_read_committed_snapshot_on], [recovery_model], [recovery_model_desc], [page_verify_option], [page_verify_option_desc], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_ansi_null_default_on], [is_ansi_nulls_on], [is_ansi_padding_on], [is_ansi_warnings_on], [is_arithabort_on], [is_concat_null_yields_null_on], [is_numeric_roundabort_on], [is_quoted_identifier_on], [is_recursive_triggers_on], [is_cursor_close_on_commit_on], [is_local_cursor_default], [is_fulltext_enabled], [is_trustworthy_on], [is_db_chaining_on], [is_parameterization_forced], [is_master_key_encrypted_by_server], [is_published], [is_subscribed], [is_merge_published], [is_distributor], [is_sync_with_backup], [service_broker_guid], [is_broker_enabled], [log_reuse_wait], [log_reuse_wait_desc], [is_date_correlation_on], [is_cdc_enabled], [is_encrypted], [is_honor_broker_priority_on], [replica_id], [group_database_id], [default_language_lcid], [default_language_name], [default_fulltext_language_lcid], [default_fulltext_language_name], [is_nested_triggers_on], [is_transform_noise_words_on], [two_digit_year_cutoff], [containment], [containment_desc], [target_recovery_time_in_seconds], [is_federation_member] FROM sys.databases

Back to Top


sys.master_files

Contains a row per file of a database as stored in the master database. This is a single, system-wide view.

Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
database_id int ID of the database to which this file applies.
file_id int ID of the file within database.
file_guid uniqueidentifier Unique identifier of the file.
NULL = Database was upgraded from an earlier version of Microsoft SQL Server.
type tinyint File type:
0 = Rows
1 = Log
2 = Reserved for future use.
3 = Reserved for future use.
4 = Full-text
type_desc nvarchar(60) Description of the file type:
ROWS
LOG
FULLTEXT
data_space_id int ID of the data space to which this file belongs. Data space is a filegroup.
0 = Log files
name sysname Logical name of the file in the database.
physical_name nvarchar(260) Operating-system file name.
state tinyint File state:
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = Reserved for future use.
6 = OFFLINE
7 = DEFUNCT
state_desc nvarchar(60) Description of the file state:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
OFFLINE
DEFUNCT
For more information, see File States1.
size int Current file size, in 8-KB pages. For a database snapshot, size reflects the maximum space that the snapshot can ever use for the file.
max_size int Maximum file size, in 8-KB pages:
0 = No growth is allowed.
-1 = File will grow until the disk is full.
268435456 = Log file will grow to a maximum size of 2 TB.
Note: Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.
growth int 0 = File is fixed size and will not grow.
>0 = File will grow automatically.
If is_percent_growth = 0, growth increment is in units of 8-KB pages, rounded to the nearest 64 KB
If is_percent_growth = 1, growth increment is expressed as a whole number percentage.
is_media_read_only bit 1 = File is on read-only media.
0 = File is on read/write media.
is_read_only bit 1 = File is marked read-only.
0 = file is marked read/write.
is_sparse bit 1 = File is a sparse file.
0 = File is not a sparse file.
For more information, see Understanding Sparse File Sizes in Database Snapshots2.
is_percent_growth bit 1 = Growth of the file is a percentage.
0 = Absolute growth size in pages.
is_name_reserved bit 1 = Dropped file name is reusable. A log backup must be taken before the name (name or physical_name) can be reused for a new file name.
0 = File name is unavailable for reuse.
create_lsn numeric(25,0) Log sequence number (LSN) at which the file was created.
drop_lsn numeric(25,0) LSN at which the file was dropped.
read_only_lsn numeric(25,0) LSN at which the filegroup that contains the file changed from read/write to read-only (most recent change).
read_write_lsn numeric(25,0) LSN at which the filegroup that contains the file changed from read-only to read/write (most recent change).
differential_base_lsn numeric(25,0) Base for differential backups. Data extents changed after this LSN will be included in a differential backup.
differential_base_guid uniqueidentifier Unique identifier of the base backup on which a differential backup will be based.
differential_base_time datetime Time corresponding to differential_base_lsn.
redo_start_lsn numeric(25,0) LSN at which the next roll forward must start.
Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_start_fork_guid uniqueidentifier Unique identifier of the recovery fork. The first_fork_guid of the next log backup restored must match this value. This represents the current state of the container.
redo_target_lsn numeric(25,0) LSN at which the online roll forward on this file can stop.
Is NULL unless state = RESTORING or state = RECOVERY_PENDING.
redo_target_fork_guid uniqueidentifier The recovery fork on which the container can be recovered. Paired with redo_target_lsn.
backup_lsn numeric(25,0) The LSN of the most recent data or differential backup of the file.

TSQL

Sql 2005
SELECT [database_id], [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.master_files
Sql 2008
SELECT [database_id], [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.master_files
Sql 2008 R2
SELECT [database_id], [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.master_files
Sql 2012
SELECT [database_id], [file_id], [file_guid], [type], [type_desc], [data_space_id], [name], [physical_name], [state], [state_desc], [size], [max_size], [growth], [is_media_read_only], [is_read_only], [is_sparse], [is_percent_growth], [is_name_reserved], [create_lsn], [drop_lsn], [read_only_lsn], [read_write_lsn], [differential_base_lsn], [differential_base_guid], [differential_base_time], [redo_start_lsn], [redo_start_fork_guid], [redo_target_lsn], [redo_target_fork_guid], [backup_lsn] FROM sys.master_files

Back to Top

No comments:

Post a Comment

Total Pageviews