- sys.backup_devices
- sys.database_files
- sys.database_mirroring
- sys.database_recovery_status
- sys.databases
- sys.master_files
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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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