четверг, 16 августа 2012 г.

MSSQL : Исследования - sys.databases

Решил немного поковырять содержимое и структуры системных таблиц, и посмотреть что из этого получится.
Начну с представления sys.databases
Что же это за таблица?
Цитата из MSDN:
Содержит одну строку для каждой базы данных в экземпляре Microsoft SQL Server.
Если участник, вызывающий sys.databases, не является владельцем базы данных и база данных не master или tempdb, минимально требуемыми разрешениями для просмотра соответствующей строки являются разрешения уровня сервера ALTER ANY DATABASE или VIEW ANY DATABASE, или разрешение CREATE DATABASE в базе данных master. Определить базу данных, к которой подключен участник, можно в таблице sys.databases.
Если база данных не находится в режиме ONLINE или параметр AUTO_CLOSE установлен в ON, значения некоторых столбцов могут быть равны NULL. Если база данных находится в режиме OFFLINE, соответствующая строка не будет видна пользователям с недостаточными правами доступа. Чтобы увидеть соответствующую строку в случае, когда база данных находится в режиме OFFLINE, пользователю необходимо иметь, по крайней мере, разрешение уровня сервера ALTER ANY DATABASE или разрешение CREATE DATABASE в базе данных master.
По сути это список доступных вам, как пользователю баз данных
Описание полей:
Имя столбца
Тип данных
Описание
name
Имя базы данных, уникальное внутри экземпляра SQL Server.
database_id
Идентификатор базы данных, уникальный внутри экземпляра SQL Server.
source_database_id
Не NULL = идентификатор базы данных-источника данного моментального снимка базы данных.
NULL = моментальный снимок не базы данных.
owner_sid
SID (идентификатор безопасности) внешнего владельца базы данных, зарегистрированного на сервере.
create_date
Дата создания или переименования базы данных. Для базы данныхtempdb это значение изменяется каждый раз при перезапуске сервера.
compatibility_level
Целое число, которое соответствует версии SQL Server, для которой поведение совместимо:
90
100
110
NULL — база данных не находится в режиме «в сети», либо параметр AUTO_CLOSE установлен в ON, и база данных закрыта.
collation_name
Параметры сортировки для базы данных. Действует как параметры сортировки по умолчанию для базы данных.
NULL — база данных не находится в режиме «в сети», либо параметр AUTO_CLOSE установлен в ON, и база данных закрыта.
user_access
Установка доступа пользователя:
0 = указано MULTI_USER.
1 = указано SINGLE_USER;
2 = указан RESTRICTED_USER.
user_access_desc
Описание установки доступа пользователя:
MULTI_USER
SINGLE_USER
RESTRICTED_USER
is_read_only
1 = база данных находится в режиме READ_ONLY
0 = база данных находится в режиме READ_WRITE
is_auto_close_on
1 = параметр AUTO_CLOSE находится в состоянии ON
0 = параметр AUTO_CLOSE находится в состоянии OFF
is_auto_shrink_on
1 = параметр AUTO_SHRINK находится в состоянии ON
0 = параметр AUTO_SHRINK находится в состоянии OFF
состояние
Состояние базы данных
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
Примечание
База данных, которая только что перешла в режим «в сети», не обязательно готова к приему соединений. Чтобы определить, может ли база данных принимать соединения, выполните запрос к столбцу collation_name представления sys.databases или к свойству Collation функции DATABASEPROPERTYEX. База данных может принимать соединения, если параметры сортировки базы данных возвращают значение, отличное от NULL. Применительно к базам данных AlwaysOn выполните запрос к столбцу database_state или database_state_desc представления sys.dm_hadr_database_replica_states.
state_desc
Описание состояния базы данных:
ONLINE
RESTORING
RECOVERING
RECOVERY_PENDING
SUSPECT
EMERGENCY
OFFLINE
Примечание
База данных, которая только что перешла в режим «в сети», не обязательно готова к приему соединений. Чтобы определить, может ли база данных принимать соединения, выполните запрос к столбцу collation_name представления sys.databases или к свойству Collation функции DATABASEPROPERTYEX. База данных может принимать соединения, если параметры сортировки базы данных возвращают значение, отличное от NULL. Применительно к базам данных AlwaysOn выполните запрос к столбцу database_state или database_state_desc представления sys.dm_hadr_database_replica_states.
Дополнительные сведения см. в разделе Состояния базы данных.
is_in_standby
База данных доступна только для чтения для журнала восстановления.
is_cleanly_shutdown
1 = база данных закрыта верно; восстановление при запуске не требуется
0 = база данных закрыта неверно; требуется восстановление при запуске
is_supplemental_logging_enabled
1 = SUPPLEMENTAL_LOGGING в состоянии ON
0 = SUPPLEMENTAL_LOGGING в состоянии OFF
snapshot_isolation_state
Состояние транзакций изоляции моментальных снимков, задаваемое при помощи параметра ALLOW_SNAPSHOT_ISOLATION.
0 = изоляция моментальных снимков в состоянии OFF (по умолчанию). Изоляция моментальных снимков запрещена.
1 = изоляция моментальных снимков в состоянии ON. Изоляция моментальных снимков разрешена.
2 = изоляция моментальных снимков в состоянии перехода в состояние OFF. Для всех транзакций записываются изменения. Нельзя запустить новые транзакции, использующие изоляцию моментальных снимков. База данных находится в состоянии перехода в состояние OFF до тех пор, пока все транзакции, активные при выполнении инструкции ALTER DATABASE, не будут завершены.
3 = изоляция моментальных снимков в состоянии перехода в состояние ON. Для новых транзакций записываются изменения. Транзакции не могут использовать изоляцию моментальных снимков до тех пор, пока состояние изоляции моментальных снимков не перейдет в 1 (ON). База данных находится в состоянии перехода в состояние ON до тех пор, пока все транзакции, активные при выполнении инструкции ALTER DATABASE, не будут завершены.
snapshot_isolation_state_desc
Описание состояния транзакций изоляции моментальных снимков, задаваемое при помощи параметра ALLOW_SNAPSHOT_ISOLATION:
OFF
ON
IN_TRANSITION_TO_ON
IN_TRANSITION_TO_OFF
is_read_committed_snapshot_on
1 = параметр READ_COMMITTED_SNAPSHOT установлен в значение ON. Операции чтения с уровнем изоляции read-committed основаны на просмотре моментальных снимков и не запрашивают блокировок.
0 = параметр READ_COMMITTED_SNAPSHOT установлен в значение OFF (по умолчанию). Операции чтения с уровнем изоляции read-committed используют разделяемые блокировки.
recovery_model
Выбранная модель восстановления:
1 = FULL
2 = BULK_LOGGED
3 = SIMPLE
recovery_model_desc
Описание выбранной модели восстановления:
FULL
BULK_LOGGED
SIMPLE
page_verify_option
Значение параметра PAGE_VERIFY:
0 = NONE
1 = TORN_PAGE_DETECTION
2 = CHECKSUM
page_verify_option_desc
Описание значений параметра PAGE_VERIFY:
NONE
TORN_PAGE_DETECTION
CHECKSUM
is_auto_create_stats_on
1 = AUTO_CREATE_STATISTICS в состоянии ON
0 = AUTO_CREATE_STATISTICS в состоянии OFF
is_auto_update_stats_on
1 = AUTO_UPDATE_STATISTICS в состоянии ON
0 = AUTO_UPDATE_STATISTICS в состоянии OFF
is_auto_update_stats_async_on
1 = AUTO_UPDATE_STATISTICS_ASYNC в состоянии ON
0 = AUTO_CREATE_STATISTICS_ASYNC в состоянии OFF
is_ansi_null_default_on
1 = ANSI_NULL_DEFAULT в состоянии ON
0 = ANSI_NULL_DEFAULT в состоянии OFF
is_ansi_nulls_on
1 = ANSI_NULLS в состоянии ON
0 = ANSI_NULLS в состоянии OFF
is_ansi_padding_on
1 = ANSI_PADDING в состоянии ON
0 = ANSI_PADDING в состоянии OFF
is_ansi_warnings_on
1 = ANSI_WARNINGS в состоянии ON
0 = ANSI_WARNINGS в состоянии OFF
is_arithabort_on
1 = ARITHABORT в состоянии ON
0 = ARITHABORT в состоянии OFF
is_concat_null_yields_null_on
1 = CONCAT_NULL_YIELDS_NULL в состоянии ON
0 = CONCAT_NULL_YIELDS_NULL в состоянии OFF
is_numeric_roundabort_on
1 = NUMERIC_ROUNDABORT в состоянии ON
0 = NUMERIC_ROUNDABORT в состоянии OFF
is_quoted_identifier_on
1 = QUOTED_IDENTIFIER в состоянии ON
0 = QUOTED_IDENTIFIER в состоянии OFF
is_recursive_triggers_on
1 = RECURSIVE_TRIGGERS в состоянии ON
0 = RECURSIVE_TRIGGERS в состоянии OFF
is_cursor_close_on_commit_on
1 = CURSOR_CLOSE_ON_COMMIT в состоянии ON
0 = CURSOR_CLOSE_ON_COMMIT в состоянии OFF
is_local_cursor_default
1 = CURSOR_DEFAULT соответствует локальному курсору
0 = CURSOR_DEFAULT соответствует глобальному курсору
is_fulltext_enabled
1 = полнотекстовый режим включен для данной базы данных
0 = полнотекстовый режим отключен для данной базы данных
is_trustworthy_on
1 = база данных помечена как надежная
0 = база данных не помечена как надежная
is_db_chaining_on
1 = межбазовые цепочки владения в состоянии ON
0 = межбазовые цепочки владения в состоянии OFF
is_parameterization_forced
1 = параметризация в состоянии FORCED
0 = параметризация в состоянии SIMPLE
is_master_key_encrypted_by_server
1 = база данных имеет главный ключ шифрования
0 = база данных не имеет главного ключа шифрования
is_published
1 = база данных является базой данных публикации в топологии репликации транзакций или моментальных снимков
0 = не является базой данных публикации
is_subscribed
Данный столбец не используется. Он всегда возвращает 0, независимо от состояния подписчика базы данных.
is_merge_published
1 = база данных является базой данных публикации в топологии репликации слиянием
0 = база данных не является базой данных публикации в топологии репликации слиянием
is_distributor
1 = база данных является базой данных распространителя в топологии репликации
0 = база данных не является базой данных распространителя в топологии репликации
is_sync_with_backup
1 = база данных помечена для синхронизации с резервной копией при помощи репликации
0 = база данных не помечена для синхронизации с резервной копией при помощи репликации
service_broker_guid
Идентификатор компонента Service Broker для данной базы данных. Используется как целевой экземпляр broker_instance в таблице маршрутизации.
is_broker_enabled
1 = брокер в этой базе данных в данный момент отправляет и принимает сообщения.
0 = все отправленные сообщения останутся в очереди передачи, а полученные сообщения не будут помещены в очередь в этой базе данных.
По умолчанию в восстановленных или прикрепленных базах данных брокер отключен.Исключением является зеркальное отображение базы данных, при котором брокер включается после отработки отказа.
log_reuse_wait
Действие или объект, ожидаемые в настоящий момент журналом транзакций:
0 = ничего;
1 = контрольная точка;
2 = резервная копия журнала;
3 = активное резервное копирование или восстановление;
4 = активная транзакция;
5 = зеркальное отображение базы данных;
6 = репликация;
7 = создание моментального снимка базы данных;
8 = просмотр журнала;
9 = вторичная реплика Группы доступности AlwaysOn применяет записи журнала транзакций этой базы данных к соответствующей базе данных-получателю;
10 = только для внутреннего использования;
11 = только для внутреннего использования;
12 = только для внутреннего использования;
13 = самая старая страница;
14 = другое (переходное).
log_reuse_wait_desc
nvarchar (60)
Описание действия или объекта, ожидаемого в настоящий момент журналом транзакций:
NOTHING
CHECKPOINT
LOG_BACKUP
Примечание
Если причиной является LOG_BACKUP, могут потребоваться две резервные копии, чтобы действительно освободить пространство.
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
AVAILABILITY_REPLICA
Примечание
Чтобы определить, какая из баз данных-получателей задерживает усечение журнала, см. столбец truncation_lsn динамического административного представления sys.dm_hadr_database_replica_states .
Только для внутреннего использования
Только для внутреннего использования
Только для внутреннего использования
OLDEST_PAGE
OTHER_TRANSIENT
is_date_correlation_on
1 = DATE_CORRELATION_OPTIMIZATION в состоянии ON
0 = DATE_CORRELATION_OPTIMIZATION в состоянии OFF
is_cdc_enabled
1 = в базе данных включена система отслеживания измененных данных. Дополнительные сведения см. в разделе sys.sp_cdc_enable_db (Transact-SQL) .
is_encrypted
Указывает, зашифрована ли база данных (отражает последнее состояние, установленное с помощью предложения ALTER DATABASE SET ENCRYPTION). Может использоваться одно из следующих значений:
1 = зашифрована;
0 = не зашифрована.
Дополнительные сведения о шифровании баз данных см. в разделе Прозрачное шифрование данных (TDE) .
Если база данных находится в процессе расшифровки, столбец is_encrypted отображает значение 0.Состояние процесса шифрования базы данных можно просмотреть с помощью динамического административного представления sys.dm_database_encryption_keys .
is_honor_broker_priority_on
Указывает, учитываются ли в базе данных приоритеты диалогов (отражает последнее состояние, установленное предложением ALTER DATABASE SET HONOR_BROKER_PRIORITY). Может использоваться одно из следующих значений:
1 = HONOR_BROKER_PRIORITY имеет значение ON;
0 = HONOR_BROKER_PRIORITY имеет значение OFF.
replica_id
Уникальный идентификатор локальной реплики доступности Группы доступности AlwaysOn группы доступности, если таковая имеется, частью которой является база данных.
NULL = база данных не является частью реплики доступности в группе доступности.
group_database_id
Уникальный идентификатор базы данных в группе доступности, в которой участвует база данных, если такая группа имеется. Значение group_database_id для этой базы данных совпадает в первичной реплике и в каждой вторичной реплике, для которой база данных присоединена к группе доступности.
NULL = база данных не является частью реплики доступности в любой группе доступности.
default_language_lcid
Указывает идентификатор локали (lcid) языка по умолчанию автономной базы данных.
Примечание. Такие функции, как Настройка параметра конфигурации сервера «язык по умолчанию» хранимой процедуры sp_configure . Это значение равно null для неавтономной базы данных.
default_language_name
nvarchar (128)
Указывает язык по умолчанию автономной базы данных.
Это значение равно null для неавтономной базы данных.
default_fulltext_language_lcid
Указывает идентификатор локали (lcid) языка полнотекстового поиска по умолчанию автономной базы данных.
Примечание. Такие функции, как Настройка параметра конфигурации сервера «язык полнотекстового поиска по умолчанию» по умолчанию хранимой процедуры sp_configure . Это значение равно null для неавтономной базы данных.
default_fulltext_language_name
nvarchar (128)
Указывает язык полнотекстового поиска по умолчанию автономной базы данных.
Это значение равно null для неавтономной базы данных.
is_nested_triggers_on
Указывает, разрешены ли вложенные триггеры в автономной базе данных.
0 = вложенные триггеры не разрешены
1 = вложенные триггеры разрешены
Примечание. Такие функции, как Настройка конфигурации сервера nested triggers хранимой процедуры sp_configure . Это значение равно null для неавтономной базы данных. Дополнительные сведения см. в разделе sys.configurations (Transact-SQL) .
is_transform_noise_words_on
Указывает должны ли преобразовываться пропускаемые слова в автономной базе данных.
0 = пропускаемые слова не должны преобразовываться.
1 = пропускаемые слова должны преобразовываться.
Примечание. Такие функции, как Параметр конфигурации сервера «transform noise words» хранимой процедуры sp_configure . Это значение равно null для неавтономной базы данных.Дополнительные сведения см. в разделе sys.configurations (Transact-SQL) .
two_digit_year_cutoff
Указывает числовое значение в диапазоне от 1753 до 9999, представляющее пороговый год для интерпретации года, представленного двумя цифрами, в виде года, представленного четырьмя цифрами.
Примечание. Такие функции, как Настройка параметра конфигурации сервера two digit year cutoff хранимой процедуры sp_configure . Это значение равно null для неавтономной базы данных.Дополнительные сведения см. в разделе sys.configurations (Transact-SQL) .
containment
tinyint not null
Указывает состояние включения базы данных.
0 = включение базы данных отключено
1 = база данных находится в состоянии частичного включения
2 = база данных находится в состоянии полного включения
containment_desc
nvarchar (60) not null
Указывает состояние включения базы данных.
NONE = прежняя версия базы данных (нулевое включение)
PARTIAL = частично автономная база данных
FULL = полностью автономная база данных
target_recovery_time_in_seconds
Предполагаемое время восстановления базы данных в секундах. Допускает значение NULL.

Что внутри:

ALTER VIEW [sys].[databases] AS
SELECT d.name, d.id AS database_id, r.indepid AS source_database_id,d.sid AS owner_sid, d.modified AS create_date,
convert(tinyint, (d.category/256) & 0xff) AS compatibility_level,
convert(sysname, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
p.user_access, ua.name AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
p.state, st.name AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown, -- DBR_CLEANLY_SHUTDOWN
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
p.recovery_model, ro.name AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.restat & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.restat & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
sysconv(bit, d.restat & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
sysconv(bit, d.restat & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
sysconv(bit, d.restat & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
sysconv(bit, d.restat & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
sysconv(bit, d.restat & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
sysconv(bit, d.restat & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
sysconv(bit, d.restat & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
sysconv(bit, d.restat & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
sysconv(bit, d.restat & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
sysconv(bit, d.restat & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
sysconv(bit, d.restat & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
sysconv(bit, d.restat & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
sysconv(bit, d.restat & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
sysconv(bit, d.restat & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
sysconv(bit, d.restat & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
sysconv(bit, d.restat & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
sysconv(bit, d.restat & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
sysconv(bit, d.category & 1) AS is_published,
sysconv(bit, d.category & 2) AS is_subscribed,
sysconv(bit, d.category & 4) AS is_merge_published,
sysconv(bit, d.category & 16) AS is_distributor,
sysconv(bit, d.category & 32) AS is_sync_with_backup,
d.svcbrkrguid AS service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
sysconv(bit, d.restat & 0x00000004) AS is_date_correlation_on -- DBR_DATECORRELATIONOPT
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id ) p
LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
WHERE d.id < 0x7fff
AND has_access('DB', d.id ) = 1
GO

Собственно меня заинтересовали таблицы:
Системные базовые таблицы — это основные таблицы, в которых хранятся метаданные определенной базы данных. База данных master рассматривается особо, так как она содержит некоторые дополнительные таблицы, которых нет в других базах данных. Эти таблицы содержат устойчивые метаданные, областью которых является весь сервер.
В принципе по описанию из MSDN к ним можно получить доступ, но для этого необходимо установить " выделенное административное соединение " (dedicated administrative connection, DAC).

Microsoft SQL Server предусматривает применение выделенных административных соединенией (DAC). Выделенные административные соединения позволяют администратору обращаться к запущенному экземпляру SQL Server Database Engine для устранения неполадок на сервере, даже если сервер не отвечает на другие клиентские соединения. Выделенные административные соединения доступны в программе sqlcmd и в среде Среда SQL Server Management Studio. Такое соединение разрешено только с клиента, запущенного на сервере. Сетевые соединения запрещены.

Чтобы использовать среду Среда SQL Server Management Studio с выделенными административными соединениями, подключитесь к экземпляру SQL Server Database Engine с помощью редактора запросов, введя ADMIN: перед именем сервера. Обозреватель объектов не может подключаться с помощью выделенных административных соединений.

Подключение к серверу с помощью выделенных административных соединений
  1. В среде Среда SQL Server Management Studio (в отсутствие других открытых выделенных административных соединений) на панели инструментов нажмите кнопку Запрос к ядру СУБД.
  2. В диалоговом окне Подключиться к компоненту Database Engine выберите Имя сервера и введите ADMIN: , за которым следует имя экземпляра сервера. Например, чтобы подключиться к экземпляру сервера с именем ACCT\PAYABLE, введите ADMIN:ACCT\PAYABLE.
  3. Заполните раздел Проверка подлинности. Для этого введите учетные данные члена группы sysadmin и нажмите кнопку Соединить.
Подключение выполнено.
Если выделенное административное соединение уже используется, то подключение не будет установлено и появится ошибка, означающая, что подключение завершилось неудачно.



з.ы. Если кому-то интересны мои "исследования" то прошу отписаться, может удастся и само ядрышко расковырять :)
Отправить комментарий