ALTER DATABASE (Transact-SQL)

Изменения: 12 декабря 2006 г.

Изменяет базу данных или файлы и файловые группы, связанные с базой данных. Добавляет или удаляет файлы и файловые группы из базы данных, изменяет атрибуты базы данных или ее файлов и файловых групп, изменяет параметры сортировки базы данных и устанавливает параметры базы данных. Моментальные снимки базы данных изменить нельзя. Чтобы изменить параметры базы данных, связанные с репликацией, используйте процедуру sp_replicationdboption.

Синтаксические обозначения в Transact-SQL

Синтаксис

ALTER DATABASE database_name 
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
  | <set_database_options>
  | MODIFY NAME = new_database_name 
  | COLLATE collation_name
}
[;]

<add_or_modify_files>::=
{
    ADD FILE <filespec> [ ,...n ] 
        [ TO FILEGROUP { filegroup_name } ]
  | ADD LOG FILE <filespec> [ ,...n ] 
  | REMOVE FILE logical_file_name 
  | MODIFY FILE <filespec>
}

<filespec>::= 
(
    NAME = logical_file_name  
    [ , NEWNAME = new_logical_name ] 
    [ , FILENAME = 'os_file_name' ] 
    [ , SIZE = size [ KB | MB | GB | TB ] ] 
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] 
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ] 
    [ , OFFLINE ]
) 

<add_or_modify_filegroups>::=
{
    | ADD FILEGROUP filegroup_name 
    | REMOVE FILEGROUP filegroup_name 
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option> 
        | DEFAULT
        | NAME = new_filegroup_name 
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE } 
    | { READ_ONLY | READ_WRITE }
}

<set_database_options>::=
SET 
{
    { <optionspec> [ ,...n ] [ WITH <termination> ] }
}

<optionspec>::= 
{
    <db_state_option>
  | <db_user_access_option> 
  | <db_update_option> 
  | <external_access_option>
  | <cursor_option> 
  | <auto_option> 
  | <sql_option> 
  | <recovery_option> 
  | <database_mirroring_option>
  | <service_broker_option>
  | <date_correlation_optimization_option>
  | <parameterization_option>
}

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }
  | TRUSTWORTHY { ON | OFF }
}

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<auto_option> ::= 
{
    AUTO_CLOSE { ON | OFF } 
  | AUTO_CREATE_STATISTICS { ON | OFF } 
  | AUTO_SHRINK { ON | OFF } 
  | AUTO_UPDATE_STATISTICS { ON | OFF } 
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<database_mirroring_option> ::= 
{ <partner_option> | <witness_option> }
    <partner_option> ::=
    PARTNER { = 'partner_server' 
            | FAILOVER 
            | FORCE_SERVICE_ALLOW_DATA_LOSS
            | OFF
            | RESUME 
            | SAFETY { FULL | OFF }
            | SUSPEND 
            | TIMEOUT integer
            }
    <witness_option> ::=
    WITNESS { = 'witness_server' 
            | OFF 
            }

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
}

<date_correlation_optimization_option> ::=
{
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }
}

<parameterization_option> ::=
{
    PARAMETERIZATION { SIMPLE | FORCED }
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<termination> ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Аргументы

• database_name
  Имя изменяемой базы данных.

• MODIFY NAME **=**new_database_name
  Присваивает базе данных имя, указанное в аргументе new_database_name.

• COLLATE collation_name
  Определяет параметры сортировки для базы данных. Аргумент collation_name может быть либо именем параметров сортировки Windows, либо именем параметров сортировки SQL. Если аргумент не указан, базе данных будут назначены параметры сортировки экземпляра SQL Server.

  Дополнительные сведения об именах параметров сортировки Windows и SQL см. в разделе COLLATE (Transact-SQL).

<add_or_modify_files>::=

Указывает файл, который будет добавлен, удален или изменен.

• ADD FILE
  Добавляет файл к базе данных.

  • TO FILEGROUP { filegroup_name }
    Указывает файловую группу, к которой необходимо добавить указанный файл. Чтобы отобразить текущую файловую группу и узнать, какая файловая группа в данный момент установлена по умолчанию, используйте представление каталога sys.filegroups.

• ADD LOG FILE
  Добавляет файл журнала в указанную базу данных.

• REMOVE FILE logical_file_name
  Удаляет логическое описание файла из экземпляра SQL Server и физический файл. Файл не может быть удален, если он не пуст.

  • logical_file_name
    Логическое имя, используемое в SQL Server при обращении к файлу.

• MODIFY FILE
  Указывает файл, который должен быть изменен. Одновременно может быть изменено только одно свойство <filespec>. Предложение NAME всегда должно быть указано в <filespec>, чтобы определить, какой файл будет изменен. Если указано предложение SIZE, новый размер файла должен быть больше, чем текущий.

  Чтобы изменить логическое имя файла данных или файла журнала, укажите логическое имя файла, который будет переименован, в предложении NAME, а новое логическое имя для файла — в предложении NEWNAME. Например:

  MODIFY FILE ( NAME = logical_file_name, NEWNAME = new_logical_name ) 
  

  Чтобы переместить файл данных или файл журнала в новое расположение, укажите текущее логическое имя файла в предложении NAME и укажите новый путь и имя файла в операционной системе в предложении FILENAME. Например:

  MODIFY FILE ( NAME = logical_file_name, FILENAME = ' new_path/os_file_name ' )
  

  При перемещении полнотекстового каталога укажите только новый путь в предложении FILENAME. Не указывайте имя файла в операционной системе.

  Дополнительные сведения см. в разделе Перемещение файлов баз данных.

<filespec>::=

Управляет свойствами файла.

• NAME logical_file_name
  Указывает логическое имя файла.

  • logical_file_name
    Логическое имя, используемое экземпляром SQL Server при обращении к файлу.

• NEWNAME new_logical_file_name
  Указывает новое логическое имя для файла.

  • new_logical_file_name
    Имя, которым будет заменено текущее логическое имя файла. Имя должно быть уникальным в пределах базы данных и соответствовать правилам для идентификаторов. Имя может быть символьной константой или константой Юникода, обычным идентификатором или идентификатором с разделителем. Дополнительные сведения см. в разделе Использование идентификаторов в качестве имен объектов.

• FILENAME 'os_file_name'
  Указывает имя файла в операционной системе (физическое имя файла).

  • ' os_file_name '
    Путь и имя файла, которые используются операционной системой при создании файла. Файл должен постоянно храниться на сервере, на котором установлен SQL Server. Указанный путь должен существовать до выполнения инструкции ALTER DATABASE.

    Если путь к файлу указан в формате UNC, нельзя установить параметры SIZE, MAXSIZE и FILEGROWTH.

    Файлы данных не должны располагаться в сжатой файловой системе, кроме случаев, когда файлы являются вторичными файлами только для чтения или база данных находится в режиме только для чтения. Файлы журналов ни в коем случае не должны размещаться в сжатых файловых системах. Дополнительные сведения см. в разделе Сжатие и файловые группы только для чтения.

    Если файл находится в необработанном разделе, аргумент os_file_name должен указывать только букву диска существующего необработанного раздела. В каждый необработанный раздел может быть помещен только один файл.

• SIZE size
  Указывает размер файла.

  • size
    Размер файла.

    При использовании в инструкции ADD FILE аргумент size является начальным размером файла. При использовании в инструкции MODIFY FILE аргумент size является новым размером файла и должен превышать текущий размер файла.

    Если аргумент size не предоставлен для первичного файла, SQL Server 2005 Database Engine использует размер первичного файла в базе данных model. Когда указан вторичный файл данных или журнала, но параметр size для файла не указан, компонент Database Engine задает размер файла равным 1 МБ.

    Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. Значение по умолчанию — MB. Укажите целое число без десятичного разделителя. Для указания долей мегабайта преобразуйте значение в килобайты, умножив число на 1024. Например, укажите «1536 KB» вместо «1,5 MB» (1,5 x 1024 = 1536).

• MAXSIZE { max_size| UNLIMITED }
  Указывает максимальный размер, до которого может расти файл.

  • max_size
    Максимальный размер файла. Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. Значение по умолчанию — MB. Укажите целое число без десятичного разделителя. Если аргумент max_size не указан, размер файла может увеличиваться, пока диск не будет заполнен.
  • UNLIMITED
    Указывает, что файл может расти вплоть до заполнения диска. В SQL Server 2005 файл журнала, определенный с возможностью неограниченного увеличения размера, имеет максимальный размер 2 ТБ, а файл данных — 16 ТБ.

• FILEGROWTH growth_increment
  Задает автоматическое приращение размера файла. Параметр FILEGROWTH для файла не может превосходить параметр MAXSIZE.

  • growth_increment
    Количество дискового пространства, добавляемого к файлу каждый раз, когда оно требуется.

    Значение может быть указано в килобайтах, мегабайтах, гигабайтах, терабайтах или процентах (%). Если указано число без суффикса «MB», «KB» или «%», то по умолчанию используется «MB». Если размер указан в процентах (%), он увеличивается на заданное количество процентов от размера файла на момент увеличения. Указанный размер округляется до ближайших 64 КБ.

    Значение 0 указывает, что автоматическое приращение выключено и дополнительное пространство для файла не разрешено.

    Если параметр FILEGROWTH не задан, значением по умолчанию является 1 МБ для файлов данных и 10% для файлов журналов, минимальное значение — 64 КБ.

    Примечание.
    В SQL Server 2005 приращение размера по умолчанию для файлов данных изменилось с 10% на 1 МБ. Значение по умолчанию для файлов журналов (10%) осталось неизменным.

• OFFLINE
  Переводит файл в автономный режим и делает все его объекты в файловой группе недоступными.

  Внимание!
  Используйте этот параметр только в том случае, когда файл поврежден и может быть восстановлен. Файл, переведенный в режим OFFLINE, может быть заново включен в оперативный режим только при восстановлении из резервной копии. Дополнительные сведения о восстановлении одиночного файла см. в разделе RESTORE (Transact-SQL).

<add_or_modify_filegroups>::=

Добавить, изменить или удалить файловую группу из базы данных.

• ADD FILEGROUP filegroup_name
  Добавляет файловую группу в базу данных.

• REMOVE FILEGROUP filegroup_name
  Удаляет файловую группу из базы данных. Файловая группа не может быть удалена, пока она не пустая. Вначале удалите из файловой группы все файлы. Дополнительные сведения см. выше в разделе «REMOVE FILE logical_file_name».

• MODIFY FILEGROUP filegroup_name { <filegroup_updatability_option> | DEFAULT | NAME **=**new_filegroup_name }
  Изменяет файловую группу, меняя ее состояние на READ_ONLY или READ_WRITE, делая ее файловой группой по умолчанию для базы данных или изменяя имя файловой группы.

  • <filegroup_updatability_option>
    Устанавливает свойство «только для чтения» или «чтение и запись» для файловой группы.
  • DEFAULT
    Изменяет файловую группу по умолчанию базы данных на аргумент filegroup_name. Только одна файловая группа в базе данных может быть файловой группой по умолчанию. Дополнительные сведения см. в разделе Основные сведения о файлах и файловых группах.
  • NAME = new_filegroup_name
    Изменяет имя файловой группы на аргумент new_filegroup_name.

<filegroup_updatability_option>::=

Устанавливает свойство «только для чтения» или «чтение и запись» для файловой группы.

• READ_ONLY | READONLY
  Определяет, что файловая группа находится в состоянии только для чтения. Изменение ее объектов запрещено. Первичную файловую группу перевести в состояние только для чтения нельзя. Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных. Дополнительные сведения см. в описании предложения SINGLE_USER.

  Поскольку база данных находится в состоянии только для чтения, невозможно производить изменения данных:

  • при запуске системы будет пропущено автоматическое восстановление;
  • сжатие базы данных невозможно;
  • в базах данных, находящихся в состоянии только для чтения, невозможны блокировки. Это может привести к более быстрому выполнению запросов.

  Примечание.
  Ключевое слово READONLY будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READONLY в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_ONLY.

• READ_WRITE | READWRITE
  Определяет, что файловая группа находится в состоянии READ_WRITE. Разрешено изменять объекты в файловой группе. Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных. Дополнительные сведения см. в описании предложения SINGLE_USER.

  Примечание.
  Ключевое слово READWRITE будет удалено в будущей версии Microsoft SQL Server. Избегайте использования ключевого слова READWRITE в новых разработках и запланируйте изменение приложений, которые сейчас его используют. Вместо него используйте READ_WRITE.

Состояние этих параметров может быть определено с помощью проверки значения столбца is_read_only в представлении каталога sys.databases или свойства Updateability функции DATABASEPROPERTYEX.

<db_state_option>::=

Управляет состоянием базы данных.

• OFFLINE
  База данных закрыта, аккуратно закрыта и помечена как автономная. В автономном режиме базу данных изменять нельзя.

• ONLINE
  База данных открыта и доступна для использования.

• EMERGENCY
  База данных помечена как READ_ONLY, ведение журнала отключено, доступ возможен только членам постоянной роли сервера sysadmin. Состояние EMERGENCY используется в основном для диагностики. Например, база данных, помеченная как подозрительная из-за поврежденного файла журнала, может быть переведена в состояние EMERGENCY. Это может предоставить системному администратору доступ к базе данных только для чтения. Перевести базу данных в состояние EMERGENCY могут только члены постоянной серверной роли sysadmin.

Состояние этого параметра можно определить с помощью проверки значений столбцов state и state_desc в представлении каталога sys.databases или свойства Status функции DATABASEPROPERTYEX. Дополнительные сведения см. в разделе Состояния базы данных.

База данных, находящаяся в состоянии RESTORING, не может быть переведена в состояние OFFLINE, ONLINE или EMERGENCY. База данных может находиться в состоянии RESTORING во время выполнения операции восстановления или тогда, когда при операции восстановления базы данных или файла журнала происходит сбой из-за поврежденного файла резервной копии. Дополнительные сведения см. в разделе Действия при ошибках восстановления SQL Server, вызванных повреждением резервных копий.

<db_user_access_option> ::=

Управляет пользовательским доступом к базе данных.

• SINGLE_USER
  Указывает, что только один пользователь одновременно может обращаться к базе данных. Если параметр SINGLE_USER указан и есть другие пользователи, подключенные к базе данных, инструкция ALTER DATABASE будет блокирована, пока все пользователи не отключатся от указанной базы данных. Чтобы отменить это поведение, см. описание предложения WITH <termination>.

  База данных остается в режиме SINGLE_USER, даже если пользователь, который установил этот параметр, отключился. В этот момент к базе данных может подключиться другой пользователь, но только один.

  Перед заданием параметра SINGLE_USER проверьте, чтобы параметру AUTO_UPDATE_STATISTICS_ASYNC было присвоено значение OFF. Если его значение равно ON, то фоновый поток, используемый для обновления статистики, создает соединение с базой данных, и получение доступа к этой базе данных в однопользовательском режиме будет невозможно. Для просмотра состояния этого параметра выполните запрос к столбцу is_auto_update_stats_async_on в представлении каталога sys.databases. Если этот параметр установлен в значение ON, выполните следующие действия.

  1. Установите параметр AUTO_UPDATE_STATISTICS_ASYNC в значение OFF.
  2. Проверьте наличие активных заданий синхронизации статистики, выполнив запрос к динамическому административному представлению sys.dm_exec_background_job_queue.
  3. Если были обнаружены активные задания, то либо дождитесь их завершения, либо прервите их работу с помощью команды KILL STATS JOB.

• RESTRICTED_USER
  Предложение RESTRICTED_USER позволяет подключаться к базе данных только членам постоянных ролей db_owner и dbcreator базы данных и постоянной серверной роли sysadmin, не ограничивая количества подключений. Все подключения к базе данных будут отключены на период времени, определяемый завершающим предложением инструкции ALTER DATABASE. После того как база данных перешла в состояние RESTRICTED_USER, попытки подключения пользователями, не соответствующими описанным выше условиям, будут отклонены.

• MULTI_USER
  Все пользователи, имеющие соответствующие разрешения на подключение к базе данных, будут допущены к базе данных.

Состояние этого параметра можно определить с помощью проверки значения столбца user_access в представлении каталога sys.databases или свойства UserAccess функции DATABASEPROPERTYEX.

<db_update_option>::=

Управляет разрешениями на обновления базы данных.

• READ_ONLY
  Пользователи могут считывать данные из базы данных, но не могут изменять их.

• READ_WRITE
  База данных доступна для операций чтения и записи.

Чтобы изменить это состояние, необходимо обладать монопольным доступом к базе данных. Дополнительные сведения см. в описании предложения SINGLE_USER.

<external_access_option>::=

Управляет возможностью обращения к базе данных из внешних ресурсов, таких как объекты другой базы данных.

• DB_CHAINING { ON | OFF }

  • ON
    База данных может быть источником или целевой базой данных межбазовой цепочки владения.
  • OFF
    База данных не может быть членом межбазовой цепочки владения.

  Важно!
  Экземпляр SQL Server распознает эту настройку, если параметр сервера cross db ownership chaining имеет значение 0 (OFF). Если параметр cross db ownership chaining имеет значение 1 (ON), все пользовательские базы данных могут участвовать в межбазовых цепочках владения вне зависимости от значения параметра DB_CHAINING. Этот параметр задается с помощью использования процедуры sp_configure.

  Для установки этого параметра необходимо быть членом фиксированной серверной роли sysadmin. Параметр DB_CHAINING не может быть установлен для следующих системных баз данных: master, model и tempdb.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_db_chaining_on в представлении каталога sys.databases.

  Дополнительные сведения см. в разделе Цепочки владения.

• TRUSTWORTHY { ON | OFF }

  • ON
    Модули базы данных (например пользовательские функции или хранимые процедуры), которые используют контекст олицетворения, могут обращаться к ресурсам, находящимся вне базы данных.
  • OFF
    Модули базы данных в контексте олицетворения не могут обращаться к ресурсам, находящимся вне базы данных.

  Параметр TRUSTWORTHY устанавливается в значение OFF при каждом присоединении базы данных.

  По умолчанию для всех баз данных, кроме msdb, параметр TRUSTWORTHY установлен в значение OFF. Оно не изменяется для баз данных model и tempdb. Рекомендуется никогда не устанавливать параметр TRUSTWORTHY в состояние ON для базы данных master.

  Для установки этого параметра необходимо быть членом фиксированной серверной роли sysadmin.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_trustworthy_on в представлении каталога sys.databases.

<cursor_option>::=

Управляет параметрами курсора.

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    Любые курсоры, открытые при завершении или откате транзакции, будут закрыты.
  • OFF
    Курсоры остаются открытыми при завершении транзакции; откат транзакции закрывает любые курсоры, кроме тех, которые имеют свойства INSENSITIVE или STATIC.

  Настройки уровня подключения, которые установлены с помощью инструкции SET, перекрывают настройки базы данных по умолчанию для CURSOR_CLOSE_ON_COMMIT. По умолчанию клиенты ODBC и OLE DB при соединении с экземпляром SQL Server устанавливают параметр CURSOR_CLOSE_ON_COMMIT инструкции SET уровня подключения в состояние OFF для сеанса. Дополнительные сведения см. в разделе SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  Состояние этого параметра можно определить с помощью проверки значения столбца is_cursor_close_on_commit_on в представлении каталога sys.databases или свойства IsCloseCursorsOnCommitEnabled функции DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Управляет тем, какую область (LOCAL или GLOBAL) использует курсор.

  • LOCAL
    Если задан параметр LOCAL и курсор не определен как GLOBAL при создании, то область курсора локальна по отношению к пакету, хранимой процедуре или триггеру, в котором курсор был создан. Имя курсора действительно только внутри этой области. На курсор могут ссылаться локальные переменные курсоров пакета, хранимые процедуры, триггеры или параметр OUTPUT хранимой процедуры. Курсор будет неявно освобожден при завершении пакета, хранимой процедуры или триггера, если только он не был передан в параметре OUTPUT. Если курсор передан в параметре OUTPUT, курсор будет освобожден, когда последняя переменная, которая ссылается на него, будет освобождена или выйдет из области.
  • GLOBAL
    Если задан параметр GLOBAL и курсор не определен как LOCAL при создании, то область курсора глобальна для подключения. Имя курсора может использоваться в любой хранимой процедуре или пакете, выполняемом при подключении.

  Курсор неявно освобождается только при отключении. Дополнительные сведения см. в разделе DECLARE CURSOR (Transact-SQL).

  Состояние этого параметра можно определить с помощью проверки значения столбца is_local_cursor_default в представлении каталога sys.databases или свойства IsLocalCursorsDefault функции DATABASEPROPERTYEX.

<auto_option>::=

Управляет автоматическими параметрами.

• AUTO_CLOSE { ON | OFF }

  • ON
    База данных закрыта аккуратно, а ее ресурсы освобождены после выхода последнего пользователя.

    База данных автоматически открывается, когда пользователь пытается ее повторно использовать. Например, с помощью инструкции USE database_name. Если база данных аккуратно закрыта во время установки параметра AUTO_CLOSE в состояние ON, она не будет повторно открываться при попытках использовать ее, пока компонент Database Engine не будет перезапущен.

  • OFF
    База данных остается открытой после того, как последний пользователь вышел.

  Параметр AUTO_CLOSE полезен для настольных баз данных, поскольку он позволяет управлять файлами базы данных так же, как обычными файлами. Они могут быть перемещены, скопированы для создания резервной копии или даже отосланы по электронной почте другим пользователям.

  Примечание.

  В более ранних версиях SQL Server AUTO_CLOSE является синхронным процессом, который может отрицательно повлиять на производительность, если к базе данных обращается приложение, которое многократно устанавливает и разрывает соединение с компонентом Database Engine. В SQL Server 2005 процесс AUTO_CLOSE является асинхронным; многократное открытие и закрытие базы данных больше не снижает производительность.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_auto_close_on в представлении каталога sys.databases или свойства IsAutoClose функции DATABASEPROPERTYEX.

  Примечание.
  Если параметр AUTO_CLOSE установлен в состояние ON, некоторые столбцы в представлении каталога sys.databases и функции DATABASEPROPERTYEX возвратят значение NULL, потому что база данных недоступна для извлечения данных. Для решения этой проблемы выполните инструкцию USE, чтобы открыть базу данных.

  Примечание.
  Зеркальное отображение базы данных требует, чтобы параметр AUTO_CLOSE был установлен в состояние OFF.

  Если параметр базы данных AUTOCLOSE установлен в значение ON, то действия, инициирующие автоматическое закрытие базы данных, очищают кэш планов для экземпляра SQL Server. Очистка кэша планов становится причиной перекомпиляции всех последующих планов выполнения и приводит к непредвиденному временному снижению производительности обработки запросов. В SQL Server 2005 с пакетом обновления 2 (SP2) для каждого очищенного хранилища кэша в кэше планов журнал ошибок SQL Server содержит следующее информационное сообщение: «SQL Server обнаружил %d экземпляров, сброшенных на диск хранилищ кэша для хранилища кэша "%s" (части кэша планов) в результате операций по обслуживанию или изменению настройки базы данных». Это сообщение протоколируется каждые пять минут при сбросе кэша в течение этого временного интервала.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    Любые недостающие статистические данные, необходимые запросу для оптимизации, будут автоматически созданы при оптимизации запроса.

    Добавление статистики улучшает работу запроса, потому что оптимизатор запроса SQL Server может лучше определить то, как вычислить запрос. Если статистические данные не используются, компонент Database Engine автоматически удалит их. Если этот параметр установлен в OFF, статистика автоматически не создается; вместо этого она может быть создана вручную. Дополнительные сведения см. в разделе Статистика индексов.

  • OFF
    Статистика должна быть создана вручную.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_auto_update_stats_on в представлении каталога sys.databases или свойства IsAutoUpdateStatistics функции DATABASEPROPERTYEX.

  Примечание.
  Оптимизатор запроса обрабатывает все внутренние системные таблицы так, как будто параметр AUTO_CREATE_STATISTICS установлен в состояние ON, независимо от фактической настройки. Эти таблицы включают основные системные таблицы, XML-индексы, полнотекстовые индексы, таблицы очередей компонента Service Broker и таблицы уведомлений запросов.

• AUTO_SHRINK { ON | OFF }

  • ON
    Файлы базы данных являются кандидатами на периодическое сжатие.

    И файлы данных, и файлы журналов могут быть автоматически сжаты. Параметр AUTO_SHRINK уменьшает размер журнала транзакций только в том случае, если выбрана простая модель восстановления базы данных или была создана резервная копия журнала. Если этот параметр установлен в состояние OFF, файлы базы данных не будут автоматически сжиматься при периодической проверке на неиспользуемое пространство.

    При включенном параметре AUTO_SHRINK файлы будут сжаты, если более 25 процентов файла содержит неиспользуемое пространство. Файл будет сжат до размера, в котором 25 процентов файла — неиспользуемое пространство, или до того размера, который был у файла при создании, каким бы большим он ни был.

    Нельзя сжать базу данных, находящуюся в состоянии только для чтения.

  • OFF
    Автоматическое сжатие файлов при периодической проверке на неиспользуемое пространство не производится.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_auto_shrink_on в представлении каталога sys.databases или свойства IsAutoShrink функции DATABASEPROPERTYEX. .

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Любые устаревшие статистические данные, необходимые запросу для оптимизации, будут автоматически обновлены при оптимизации запроса.
  • OFF
    Статистика должна быть обновлена вручную.

  Примечание.
  Инструкция UPDATE STATISTICS заново включает автоматическое обновление статистических данных в целевой таблице или представлении, если не указано предложение NORECOMPUTE.

  Примечание.
  Оптимизатор запроса обрабатывает все внутренние системные таблицы так, как будто параметр AUTO_UPDATE_STATISTICS установлен в состояние ON, независимо от фактической настройки. Эти таблицы включают основные системные таблицы, XML-индексы, полнотекстовые индексы, таблицы очередей компонента Service Broker и таблицы уведомлений запросов.

  Дополнительные сведения см. в разделе Статистика индексов.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Запросы, которые инициализируют автоматическое обновление устаревшей статистики, не будут ожидать обновления статистики перед компиляцией. Последующие запросы будут использовать обновленную статистику, когда она будет доступна.
  • OFF
    Запросы, которые инициализируют автоматическое обновление устаревшей статистики, будут ожидать, пока обновленная статистика не сможет использоваться в плане оптимизации запроса.

  Установка этого параметра в состояние ON не будет иметь эффекта, если параметр AUTO_UPDATE_STATISTICS не установлен в состояние ON.

  Дополнительные сведения см. в разделе Статистика индексов.

<sql_option>::=

Управляет параметрами соответствия ANSI на уровне базы данных.

• ANSI_NULL_DEFAULT { ON | OFF }
  Определяет значение по умолчанию NULL или NOT NULL, столбца с типом тип данных псевдонима или пользовательский тип CLR, для которого возможность принимать значение NULL явно не определена в инструкциях CREATE TABLE или ALTER TABLE. Столбцы, определенные с ограничениями, следуют правилам ограничения независимо от этой настройки.

  • ON
    Значением по умолчанию является NULL.
  • OFF
    Значением по умолчанию является NOT NULL.

  Настройки уровня подключения, установленные с помощью инструкции SET, перекрывают настройки уровня базы данных по умолчанию для ANSI_NULL_DEFAULT. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр ANSI_NULL_DEFAULT инструкции SET уровня подключения в состояние ON для сеанса. Дополнительные сведения см. в разделе SET ANSI_NULL_DFLT_ON (Transact-SQL).

  Для совместимости ANSI, при установке параметра базы данных ANSI_NULL_DEFAULT в состояние ON изменяется значение по умолчанию базы данных на значение NULL.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_ansi_null_default_on в представлении каталога sys.databases или свойства IsAnsiNullDefault функции DATABASEPROPERTYEX.

• ANSI_NULLS { ON | OFF }

  • ON
    Результатом любого сравнения со значением NULL будет UNKNOWN.
  • OFF
    Результатом сравнения значений не в Юникоде будет TRUE, если оба значения — NULL.

  Настройки уровня подключения, установленные с помощью инструкции SET, перекрывают настройки базы данных по умолчанию для ANSI_NULLS. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр ANSI_NULLS инструкции SET уровня подключения в состояние ON для сеанса. Дополнительные сведения см. в разделе SET ANSI_NULLS (Transact-SQL).

  При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр SET ANSI_NULLS также должен быть установлен в ON.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_ansi_nulls_on в представлении каталога sys.databases или свойства IsAnsiNullsEnabled функции DATABASEPROPERTYEX.

• ANSI_PADDING { ON | OFF }

  • ON
    Перед преобразованием или вставкой в тип данных varchar или nvarchar строки дополняются до нужной длины.

    Конечные пробелы в символьных значениях, вставляемых в столбцы varchar или nvarchar, и конечные нули в двоичных значениях, вставляемых в столбцы varbinary, не отбрасываются. Значения не дополняются до ширины столбца.

  • OFF
    Конечные пробелы для varchar или nvarchar и нули для varbinary будут отброшены.

  Состояние OFF касается только определения новых столбцов.

  Столбцы с типами char(n) и binary(n), которые могут принимать значение NULL, подгоняются по длине столбца, если параметр ANSI_PADDING установлен в состояние ON; однако, если параметр ANSI_PADDING установлен в состояние OFF, конечные пробелы и нули отбрасываются. Столбцы с типами char(n) и binary(n), которые не могут принимать значение NULL, всегда подгоняются по длине столбца.

  Настройки уровня подключения, установленные с помощью инструкции SET, перекрывают настройки уровня базы данных по умолчанию для ANSI_PADDING. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр ANSI_PADDING инструкции SET уровня подключения в состояние ON для сеанса. Дополнительные сведения см. в разделе SET ANSI_PADDING (Transact-SQL).

  Важно!
  Параметр ANSI_PADDING рекомендуется всегда устанавливать в значение ON. При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр ANSI_PADDING должен быть установлен в ON.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_ansi_padding_on в представлении каталога sys.databases или свойства IsAnsiPaddingEnabled функции DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    В таких ситуациях, как деление на ноль или использование значения NULL в статистических функциях, выводятся ошибки или предупреждения.
  • OFF
    Предупреждения не выводятся, а в таких ситуациях, как деление на ноль, возвращается NULL.

  При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр SET ANSI_WARNINGS должен быть установлен в ON.

  Настройки уровня подключения, установленные с помощью инструкции SET, перекрывают настройки базы данных по умолчанию для ANSI_WARNINGS. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр ANSI_WARNINGS инструкции SET уровня подключения в состояние ON для сеанса. Дополнительные сведения см. в разделе SET ANSI_WARNINGS (Transact-SQL).

  Состояние этого параметра можно определить с помощью проверки значения столбца is_ansi_warnings_on в представлении каталога sys.databases или свойства IsAnsiWarningsEnabled функции DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Запрос будет завершен, если во время его выполнения возникла ошибка переполнения или деления на ноль.
  • OFF
    При возникновении одной из этих ошибок будет выдано предупреждающее сообщение, однако запрос, пакет или транзакция продолжит работу, как будто никакой ошибки не произошло.

  При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр SET ARITHABORT должен быть установлен в ON.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_arithabort_on в представлении каталога sys.databases или свойства IsArithmeticAbortEnabled функции DATABASEPROPERTYEX.

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    Результатом операции сцепления будет NULL, если любой из операндов — NULL. Например, сцепление строки символов «Это» со значением NULL приведет к результату NULL вместо «Это».
  • OFF
    Значение NULL будет обработано, как пустая строка символов.

  При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр CONCAT_NULL_YIELDS_NULL должен быть установлен в ON.

  Настройки уровня подключения, которые установлены с помощью инструкции SET, перекрывают настройки базы данных по умолчанию для CONCAT_NULL_YIELDS_NULL. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр CONCAT_NULL_YIELDS_NULL инструкции SET уровня подключения в состояние ON для сеанса. Дополнительные сведения см. в разделе SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  Состояние этого параметра можно определить с помощью проверки значения столбца is_concat_null_yields_null_on в представлении каталога sys.databases или свойства IsNullConcat функции DATABASEPROPERTYEX.

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    Двойные кавычки могут использоваться для идентификаторов с разделителями.

    Все строки, находящиеся в двойных кавычках, интерпретируются как идентификаторы объектов. Идентификаторы в кавычках не должны удовлетворять правилам языка Transact-SQL для идентификаторов. Они могут быть ключевыми словами и могут включать символы, не разрешенные в идентификаторах Transact-SQL. Если в состав строки-литерала входит одиночная кавычка ('), строка может быть заключена в двойные кавычки (").

  • OFF
    Идентификаторы не могут быть заключены в кавычки и должны следовать всем правилам для идентификаторов языка Transact-SQL. Литералы могут разделяться как одинарными, так и двойными кавычками.

  SQL Server также допускает разделение идентификаторов квадратными скобками ([]). Идентификаторы в скобках могут использоваться всегда, независимо от настройки параметра QUOTED_IDENTIFIER. Дополнительные сведения см. в разделе Идентификаторы с разделителями (компонент Database Engine).

  После создания таблицы параметр QUOTED IDENTIFIER всегда сохраняется в метаданных таблицы со значением ON, даже если при создании таблицы для него было задано OFF.

  Настройки уровня подключения, установленные с помощью инструкции SET, перекрывают настройки базы данных по умолчанию для QUOTED_IDENTIFIER. По умолчанию клиенты ODBC и OLE DB при подключении к экземпляру SQL Server устанавливают параметр QUOTED_IDENTIFIER инструкции SET уровня подключения в состояние ON. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).

  Состояние этого параметра можно определить с помощью проверки значения столбца is_quoted_identifier_on в представлении каталога sys.databases или свойства IsQuotedIdentifiersEnabled функции DATABASEPROPERTYEX.

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    Если в выражении происходит потеря точности, будет сформирована ошибка.
  • OFF
    Потери точности не приводят к формированию сообщений об ошибках, а результат округляется с точностью столбца или переменной, в которых сохраняется результат.

  При создании или управлении индексами, основанными на вычисляемых столбцах или индексированных представлениях, параметр NUMERIC_ROUNDABORT должен быть установлен в OFF.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_numeric_roundabort_on в представлении каталога sys.databases или свойства IsNumericRoundAbortEnabled функции DATABASEPROPERTYEX.

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    Рекурсивное срабатывание триггеров AFTER разрешено.
  • OFF
    Не разрешено только прямое рекурсивное срабатывание триггеров AFTER. Чтобы отключить косвенную рекурсию триггеров AFTER, присвойте с помощью процедуры sp_configure параметру сервера «nested triggers» значение 0.

  Примечание.
  Если параметр RECURSIVE_TRIGGERS установлен в состояние OFF, будет запрещена только прямая рекурсия. Чтобы отключить косвенную рекурсию, нужно дополнительно установить параметр сервера nested triggers в состояние 0.

  Состояние этого параметра можно определить с помощью проверки значения столбца is_recursive_triggers_on в представлении каталога sys.databases или свойства IsRecursiveTriggersEnabled функции DATABASEPROPERTYEX.

<recovery_option> ::=

Управляет параметрами восстановления базы данных и проверкой ошибок дискового ввода-вывода.

• FULL
  Обеспечивает полное восстановление после отказа носителя с помощью резервных копий журнала транзакций. Если файл данных поврежден, то восстановление носителя может восстановить все зафиксированные транзакции. Дополнительные сведения см. в разделе Резервное копирование в полной модели восстановления.

• BULK_LOGGED
  Обеспечивает восстановление после отказа носителя, объединяя оптимальную производительность и минимальный объем пространства, занимаемого журналами, используется для больших систем или массовых операций. Сведения о том, к каким операциям применяется неполное протоколирование, см. в разделе Операции с минимальным протоколированием. В модели восстановления BULK_LOGGED ведение журнала для этих операций минимально. Дополнительные сведения см. в разделе Резервное копирование с использованием модели восстановления с неполным протоколированием.

• SIMPLE
  Предусматривается стратегия простого резервирования, которая использует минимальное пространство под журналы. Пространство, отведенное под журналы, может быть автоматически многократно использовано, если оно больше не требуется для восстановления сбоев сервера. Дополнительные сведения см. в разделе Резервное копирование при простой модели восстановления.

  Важно!
  Модель простого восстановления проще в управлении, чем другие две модели, но больше подвержена потере данных, если файл данных поврежден. Все изменения, начиная с наиболее свежей резервной копии базы данных или разностной резервной копии базы данных, будут потеряны и должны быть повторно введены вручную.

Модель восстановления по умолчанию определяется моделью восстановления базы данных model. Дополнительные сведения о выборе подходящей модели восстановления см. в разделе Выбор модели восстановления для базы данных.

Состояние этого параметра можно определить с помощью проверки значений столбцов recovery_model и recovery_model_desc в представлении каталога sys.databases или свойства Recovery функции DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Неполные страницы могут быть обнаружены компонентом Database Engine.
  • OFF
    Неполные страницы не могут быть обнаружены компонентом Database Engine.

  Важно!
  Синтаксическая структура TORN_PAGE_DETECTION ON | OFF будет удалена в будущей версии Microsoft SQL Server. Избегайте использования этой структуры в новых разработках и запланируйте изменение приложений, которые сейчас ее используют. Вместо этого используйте параметр PAGE_VERIFY.

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  Обнаруживает поврежденные страницы базы данных, вызванные ошибками пути дискового ввода-вывода. Ошибки пути дискового ввода-вывода могут быть причиной повреждения базы данных и обычно происходят при сбоях питания или сбоях оборудования диска, которые происходят во время записи страницы на диск.

  • CHECKSUM
    Вычисляет контрольную сумму по содержанию целой страницы и сохраняет полученное значение в ее заголовке при записи страницы на диск. При считывании страницы с диска происходит пересчет контрольной суммы и сравнение со значением контрольной суммы, которое хранится в заголовке страницы. Если значения не соответствуют, будет выведено сообщение об ошибке 824 (ошибка контрольной суммы) как в журнал ошибок SQL Server, так и в журнал событий Windows. Ошибка контрольной суммы указывает на проблему пути ввода-вывода. Чтобы определить первопричину, необходимо исследовать оборудование, драйверы, BIOS, фильтрующее программное обеспечение (такое как антивирусное программное обеспечение) и другие компоненты пути ввода-вывода.
  • TORN_PAGE_DETECTION
    Сохраняет определенный бит для каждого 512-байтового сектора в 8 килобайтной (КБ) странице базы данных и сохраняет в базе данных заголовок страницы при записи страницы на диск. При считывании страницы с диска биты, хранящиеся в заголовке страницы, сравниваются со сведениями из сектора текущей страницы. Несовпадающие значения указывают, что только часть страницы была записана на диск. В этой ситуации сообщение об ошибке 824 (ошибка разрыва страницы) будет выведено как в журнал ошибок SQL Server, так и в журнал событий Windows. Разорванные страницы обычно обнаруживаются при восстановлении базы данных, если они действительно не полностью записаны. Однако другие сбои пути ввода-вывода могут стать причиной разрыва страницы в любое время.
  • NONE
    Страница базы данных при записи не будет формировать значение CHECKSUM или TORN_PAGE_DETECTION. SQL Server не будет проверять контрольную сумму и разрывы страниц при считывании, даже если значение CHECKSUM или TORN_PAGE_DETECTION будет присутствовать в заголовке страницы.

  Рассмотрите следующие важные моменты при использовании параметра PAGE_VERIFY.

  • В SQL Server 2005 значением по умолчанию является CHECKSUM. В SQL Server 2000 значением по умолчанию является TORN_PAGE_DETECTION.
  • Если пользовательская или системная база данных обновлена до SQL Server 2005, значение PAGE_VERIFY (NONE или TORN_PAGE_DETECTION) будет сохранено. Рекомендуется использовать CHECKSUM.
  • Значение TORN_PAGE_DETECTION использует меньше ресурсов, но обеспечивает минимальный вариант защиты CHECKSUM.
  • Параметр PAGE_VERIFY можно установить, не производя перевод базы данных в автономный режим, блокировку или прочие действия, нарушающие ее параллелизм.
  • Значения CHECKSUM и TORN_PAGE_DETECTION являются взаимоисключающими. Оба параметра не могут быть включены одновременно.

  При обнаружении ошибки разрыва страницы или контрольной суммы ее можно устранить с помощью восстановления данных или потенциальной перестройки индекса, если сбой ограничен только страницами индексов. При обнаружении ошибки контрольной суммы выполните инструкцию DBCC CHECKDB, чтобы определить тип поврежденной страницы базы данных. Дополнительные сведения о параметрах восстановления см. в разделе Аргументы инструкции RESTORE (Transact-SQL). Хотя восстановление данных решит проблему нарушения целостности данных, первопричина, например сбой оборудования диска, должна быть обнаружена и исправлена как можно скорее, чтобы предотвратить следующие ошибки.

  SQL Server повторяет любую операцию считывания, которая закончилась ошибкой контрольной суммы, разрыва страницы или другой ошибкой ввода-вывода, четыре раза. Если считывание закончится успешно в любой из попыток, в журнал ошибок будет записано сообщение и команда, вызвавшая считывание, продолжится. Если все повторные попытки закончатся ошибкой, команда закончит работу с сообщением об ошибке 824.

  Дополнительные сведения о контрольной сумме, разрыве страницы, повторном считывании, сообщениях об ошибках 823 и 824 и других характеристиках аудита ввода-вывода SQL Server см. на веб-узле корпорации Майкрософт.

  Состояние этого параметра можно определить с помощью проверки значения столбца page_verify_option в представлении каталога sys.databases или свойства IsTornPageDetectionEnabled функции DATABASEPROPERTYEX.

<database_mirroring_option>::=

Управляет зеркальным отображением базы данных. Значения, указанные с параметрами зеркального отображения базы данных, применяются к обеим копиям базы данных и к сеансу зеркального отображения базы данных в целом. В инструкции ALTER DATABASE допустим только один параметр <database_mirroring_option>: { SET PARTNER <partner_option> | SET WITNESS <witness_option>}.

Важно!
Команда SET PARTNER или SET WITNESS может быть завершена успешно, но позднее привести к ошибке.

Примечание.
Конфигурацию зеркального отображения базы данных рекомендуется выполнять в часы с наименьшей загрузкой, поскольку этот процесс может повлиять на производительность.

Дополнительные сведения о зеркальном отображении базы данных см. в разделе Зеркальное отображение базы данных.

• PARTNER <partner_option>
  Управляет свойствами базы данных, которые определяют партнеров по переключению при сбоях сеанса зеркального отображения базы данных и их поведение. Некоторые параметры инструкции SET PARTNER могут быть установлены на любом партнере; другие разграничены для основного или для зеркального сервера. Дополнительные сведения см. в следующем описании индивидуальных параметров PARTNER. Предложение SET PARTNER влияет на обе копии базы данных независимо от партнера, на которого оно указывает.

  Для выполнения инструкции SET PARTNER требуется, чтобы параметры STATE конечных точек обоих партнеров имели значение STARTED. Также учтите, что параметр ROLE конечной точки зеркального отображения базы данных каждого экземпляра сервера партнера должен быть установлен в состояние PARTNER или ALL. Дополнительные сведения об определении конечной точки см. в разделе Как создать конечную точку зеркального отображения базы данных с проверкой подлинности Windows (язык Transact-SQL). Чтобы узнать роль и состояние базы данных конечной точки зеркального отображения экземпляра сервера, выполните на этом экземпляре следующую инструкцию языка Transact-SQL:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  <partner_option> ::=

  Примечание.
  В предложении SET PARTNER допустим только один аргумент <partner_option>.

  • 'partner_server'
    Указывает сетевой адрес сервера экземпляра SQL Server, чтобы задействовать его как партнера по переключению при сбоях в новом сеансе зеркального отображения базы данных. Для каждого сеанса необходимы два партнера: один запускается как основной сервер, второй как зеркальный сервер. Рекомендуется, чтобы эти партнеры находились на разных компьютерах.

    Этот параметр указывается один раз для сеанса на каждом партнере. Для инициализации сеанса зеркального отображения базы данных необходимо выполнить две инструкции ALTER DATABASE database SET PARTNER ='partner_server'. Их порядок важен. Вначале подключитесь к зеркальному серверу и укажите основной экземпляр сервера как partner_server (SET PARTNER ='principal_server'). Затем подключитесь к основному серверу и укажите зеркальный экземпляр сервера как partner_server (SET PARTNER ='mirror_server'); в результате запустится сеанс зеркального отображения базы данных между этими двумя партнерами. Дополнительные сведения см. в разделе Настройка зеркального отображения базы данных.

    Значением аргумента partner_server является сетевой адрес сервера. Оно имеет следующий синтаксис:

    TCP**://<адрес_системы>:**<порт>

    где

    • <адрес_системы> является строкой, полностью уточняющей имя домена или IP адрес, который однозначно идентифицирует целевую компьютерную систему;
    • <порт> — это номер порта, связанного с конечной точкой зеркального отображения, принадлежащей экземпляру партнерского сервера.

    Дополнительные сведения см. в разделе Указание сетевого адреса сервера (зеркальное отображение базы данных).

    В следующем примере показано предложение SET PARTNER ='partner_server':

    SET PARTNER = 'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'
    

    Важно!

    Если сеанс установлен с помощью инструкции ALTER DATABASE, а не с помощью среды SQL Server Management Studio, он будет установлен с полной безопасностью транзакций по умолчанию (параметр SAFETY установлен в состояние FULL) и будет работать в режиме повышенной безопасности без возможности автоматического перехода на другой ресурс. Чтобы разрешить автоматический переход на другой ресурс, настройте следящий сервер для работы в высокопроизводительном режиме и отключите безопасность транзакций (SAFETY OFF).

  • FAILOVER
    Ручное переключение при сбое с основного сервера на зеркальный. Параметр FAILOVER можно указать только на основном сервере. Этот параметр допустим только в том случае, если параметр SAFETY установлен в состояние FULL (значение по умолчанию).

    Для параметра FAILOVER контекстом должна быть база данных master.

    Дополнительные сведения см. в разделе Переход на другой ресурс вручную.

  • FORCE_SERVICE_ALLOW_DATA_LOSS
    Переключает обслуживание базы данных на зеркальный сервер при сбое синхронизации основного сервера с базой данных или в синхронизированном состоянии, когда автоматический переход на другой ресурс не происходит.

    Рекомендуется включать вынужденное обслуживание только в том случае, если основной сервер больше не работает. Иначе некоторые клиенты могут продолжить обращаться к оригинальной основной базе данных вместо новой основной базы данных.

    Параметр FORCE_SERVICE_ALLOW_DATA_LOSS доступен только на зеркальном сервере и только при соблюдении всех следующих условий:

    • основной сервер вышел из строя;
    • параметр WITNESS установлен в состояние OFF или свидетель подключен к зеркальному серверу.

    Переключайте обслуживание только в случае готовности рискнуть потерей некоторых данных для немедленного восстановления обслуживания базы данных. Дополнительные сведения о других способах переключения обслуживания см. в разделе Асинхронное зеркальное отображение баз данных (режим высокой производительности).

    При переключении обслуживания сеанс приостанавливается, сохраняя все данные в базе данных оригинального главного сервера. Как только оригинальная основная база данных начала обслуживаться и доступна для связи с новым основным сервером, администратор базы данных может возобновить обслуживание. При возобновлении сеанса любые неотправленные записи журнала и соответствующие обновления будут утеряны.

    Дополнительные сведения о рисках принудительного обслуживания см. в разделе Принудительное обслуживание (с вероятностью потери данных).

  • OFF
    Прекращает сеанс зеркального отображения базы данных и отключает зеркальное отображение базы данных. Значение OFF можно указать на любом партнере. Дополнительные сведения о последствиях отключения зеркального отображения см. в разделе Удаление зеркального отображения базы данных.
  • RESUME
    Возобновляет приостановленный сеанс зеркального отображения базы данных. Параметр RESUME можно указать только на основном сервере.

  • SAFETY { FULL | OFF }
    Устанавливает уровень безопасности транзакций. Параметр SAFETY можно указать только на основном сервере.

    Значение по умолчанию FULL. При полном уровне безопасности сеанс зеркального отображения базы данных выполняется синхронно (в режиме повышенной безопасности). Если параметр SAFETY находится в состоянии OFF, сеанс зеркального отображения базы данных выполняется асинхронно (в высокопроизводительном режиме).

    Поведение режима повышенной безопасности частично зависит от следящего сервера, как показано далее.

    • Если безопасность установлена в состояние FULL и для сеанса установлен следящий сервер, то сеанс выполняется в режиме повышенной безопасности с автоматическим переходом на другой ресурс. Когда основной сервер становится недоступным, сеанс автоматически переключается на другой ресурс при условии, что база данных находится в синхронном состоянии, а экземпляры зеркального и следящего серверов остаются подключенными друг к другу (имеют кворум). Дополнительные сведения см. в разделе Кворум: как следящий сервер влияет на доступность базы данных.
      Если свидетель установлен для сеанса, но в настоящее время не подключен, потеря зеркального сервера вынудит основной сервер выключиться.
    • Если безопасность установлена в состояние FULL, а следящий сервер находится в состоянии OFF, сеанс выполняется в режиме повышенной безопасности без возможности автоматического перехода на другой ресурс. При отказе экземпляра зеркального сервера экземпляр основного сервера остается незатронутым. При отказе экземпляра основного сервера можно переключить обслуживание на экземпляр зеркального сервера (с возможной потерей данных).

    Если параметр SAFETY установлен в состояние OFF, сеанс выполняется в высокопроизводительном режиме, в котором ни автоматический, ни ручной переход на другой ресурс не поддерживаются. Однако проблемы на зеркальном сервере не затрагивают основной сервер, и, если экземпляр основного сервера отключается, при необходимости можно переключить обслуживание (с возможной потерей данных) на экземпляр зеркального сервера, если параметр WITNESS установлен в состояние OFF или если следящий сервер в настоящее время подключен к зеркальному серверу. Дополнительные сведения о переключении обслуживания см. в подразделе «FORCE_SERVICE_ALLOW_DATA_LOSS», расположенном выше в этом разделе.

    Важно!
    Высокопроизводительный режим не предназначен для использования свидетеля. Однако всякий раз, когда параметру SAFETY присваивается значение OFF, настоятельно рекомендуется проверить, что параметр WITNESS находится в состоянии OFF.

    Дополнительные сведения см. в разделе Настройки Transact-SQL и режимы зеркального отображения базы данных.

  • SUSPEND
    Приостанавливает сеанс зеркального отображения базы данных.

    Значение SUSPEND можно указать на любом партнере.

  • TIMEOUT integer
    Указывает интервал времени ожидания в секундах. Интервал времени ожидания — это максимальное время, в течение которого экземпляр сервера ожидает получения сообщения PING от другого экземпляра в сеансе зеркального отображения перед тем, как сделать вывод о том, что другой экземпляр отключен.

    Параметр TIMEOUT можно указать только на основном сервере. Если этот параметр не определить, интервал времени по умолчанию — 10 секунд. При указании числа 5 или больше интервал времени ожидания будет установлен в указанное количество секунд. При указании значения интервала времени ожидания от 0 до 4 секунд интервал времени ожидания автоматически будет установлен в 5 секунд.

    Важно!
    Рекомендуется установить интервал времени ожидания в 10 секунд или более. При установке значения меньше 10 секунд возникает вероятность пропуска команды PING в сильно загруженной системе и вероятность ошибочного сообщения об ошибке.

    Дополнительные сведения см. в разделе Возможные неполадки при зеркальном отображении базы данных.

• WITNESS <witness_option>
  Управляет свойствами базы данных, которые определяют свидетеля зеркального отображения базы данных. Предложение SET WITNESS влияет на обе копии базы данных, указать его можно только на основном сервере. Если для сеанса установлен свидетель, для обслуживания базы данных необходим кворум независимо от настройки параметра SAFETY; дополнительные сведения см. в разделе Кворум: как следящий сервер влияет на доступность базы данных.

  Рекомендуется, чтобы свидетель и партнеры по обработке отказа находились на отдельных компьютерах. Сведения о свидетеле см. в разделе Следящий сервер зеркального отображения базы данных. Сведения об автоматической обработке отказа см. в разделе Автоматический переход на другой ресурс.

  Для выполнения инструкции SET WITNESS параметр STATE конечных точек экземпляров основного сервера и свидетеля должен быть установлен в состояние STARTED. Также учтите, что параметр ROLE конечной точки зеркального отображения базы данных экземпляра сервера свидетеля должен быть установлен в состояние PARTNER или ALL. Сведения об определении конечной точки см. в разделе Конечная точка зеркального отображения базы данных.

  Чтобы узнать роль и состояние базы данных конечной точки зеркального отображения экземпляра сервера, выполните на этом экземпляре следующую инструкцию языка Transact-SQL:

  SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints
  

  Примечание.
  Свойства базы данных не могут быть установлены на свидетеле.

  <witness_option> ::=

  Примечание.
  В предложении SET WITNESS допустим только один параметр <witness_option>.

  • 'witness_server'
    Указывает экземпляр компонента Database Engine, чтобы задействовать его в качестве свидетеля для сеанса зеркального отображения базы данных. Инструкции SET WITNESS можно указывать только на основном сервере.

    В инструкции SET WITNESS = 'witness_server' синтаксис параметра witness_server такой же, как синтаксис параметра partner_server.

  • OFF
    Удаляет свидетеля из сеанса зеркального отображения базы данных. При установке свидетеля в состояние OFF отключается автоматическая обработка отказа. Если база данных установлена в состояние FULL SAFETY, а свидетель установлен в состояние OFF, отказ зеркального сервера заставит основной сервер сделать базу данных недоступной.

<service_broker_option>::=

Управляет параметрами компонента Service Broker.

• ENABLE_BROKER
  Указывает, что для заданной базы данных включен компонент Service Broker. Флаг is_broker_enabled в представлении каталога sys.databases установлен в значение TRUE и запущена доставка сообщений.

  Примечание.
  Для включения в любой базе данных компонента SQL Server Service Broker необходима блокировка базы данных. Чтобы включить компонент Service Broker в базе данных msdb, первым делом необходимо остановить службу агента SQL Server, чтобы компонент Service Broker смог получить необходимую блокировку.

• DISABLE_BROKER
  Указывает, что для заданной базы данных компонент Service Broker отключен. Флаг is_broker_enabled в представлении каталога sys.databases установлен в значение FALSE и остановлена доставка сообщений.

• NEW_BROKER
  Указывает, что база данных должна получить новый идентификатор посредника. Поскольку база данных предполагает наличие нового компонента Service Broker, все существующие сеансы связи в базе данных будут немедленно удалены, не выдавая диалоговых сообщений о завершении.

• ERROR_BROKER_CONVERSATIONS
  Указывает, что сеансы связи в базе данных должны получить сообщение об ошибках при вложении базы данных. Это дает возможность приложениям выполнять регулярную очистку существующих сеансов связи.

<date_correlation_optimization_option> ::=

Управляет параметром date_correlation_optimization.

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    SQL Server поддерживает статистику корреляции между любыми двумя таблицами в базе данных, которые связаны ограничением FOREIGN KEY и имеют столбцы datetime. Дополнительные сведения см. в разделе Оптимизация запросов, которые обращаются к коррелируемым столбцам типа datetime.
  • OFF
    Статистика корреляции не поддерживается.

  Для установки параметра DATE_CORRELATION_OPTIMIZATION в состояние ON не должно быть активных соединений с базой данных, за исключением соединения, в котором выполняется инструкция ALTER DATABASE. Впоследствии возможность нескольких соединений будет поддерживаться.

  Текущее состояние этого параметра можно определить с помощью проверки значения столбца is_date_correlation_on в представлении каталога sys.databases.

<parameterization_option> ::=

Управляет параметром PARAMETERIZATION.

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    Запросы параметризуются на основании поведения базы данных по умолчанию. Дополнительные сведения см. в разделе Простая параметризация.
  • FORCED
    SQL Server параметризует все запросы в базе данных. Дополнительные сведения см. в разделе Принудительная параметризация.

  Текущее состояние этого параметра можно определить с помощью проверки значения столбца is_parameterization_forced в представлении каталога sys.databases.

<snapshot_option>::=

Определяет уровень изоляции транзакции.

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    Транзакции могут указывать уровень изоляции SNAPSHOT. Если транзакция выполняется с уровнем изоляции SNAPSHOT, все инструкции видят моментальный снимок данных в том состоянии, в котором он находился при запуске транзакции. Если транзакция выполняется с уровнем изоляции SNAPSHOT и обращается к данным нескольких баз данных, то либо параметр ALLOW_SNAPSHOT_ISOLATION должен быть установлен в состояние ON во всех базах данных, либо каждая инструкция в транзакции должна использовать подсказки блокировки при любом обращении предложения FROM к таблице базы данных, в которой параметр ALLOW_SNAPSHOT_ISOLATION установлен в состояние OFF.
  • OFF
    Транзакции не могут указывать уровень изоляции SNAPSHOT.

  При изменении состояния параметра ALLOW_SNAPSHOT_ISOLATION (из ON в OFF или из OFF в ON) инструкция ALTER DATABASE не возвращает управления вызвавшей ее программе, пока все существующие транзакции в базе данных не будут зафиксированы. Если база данных уже находится в состоянии, указанном в инструкции ALTER DATABASE, управление вызвавшей программе будет возвращено немедленно. Если инструкция ALTER DATABASE долго не возвращает управление, используйте процедуру sys.dm_tran_active_snapshot_database_transactions, чтобы определить наличие длительно выполняющихся транзакций. Если инструкция ALTER DATABASE отменена, база данных останется в состоянии, в котором она находилась при запуске ALTER DATABASE. Представление каталога sys.databases отображает состояние транзакций с уровнем изоляции моментальных снимков в базе данных. Если snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF, операция будет повторена через шесть секунд.

  Изменить состояние ALLOW_SNAPSHOT_ISOLATION невозможно, если база данных находится в режиме OFFLINE.

  При установке параметра ALLOW_SNAPSHOT_ISOLATION в базе данных, находящейся в режиме READ_ONLY, установка будет сохранена при переводе базы данных в режим READ_WRITE.

  Настройки ALLOW_SNAPSHOT_ISOLATION можно изменить и для баз данных master, model, msdb и tempdb. При изменении настройки для базы данных tempdb она будет сохраняться каждый раз при остановке и перезапуске экземпляра компонента Database Engine. При изменении настройки для базы данных model эта настройка становится значением по умолчанию для любых вновь создаваемых баз данных, за исключением tempdb.

  Этот параметр для баз данных master и msdb по умолчанию установлен в состояние ON.

  Текущее состояние этого параметра можно определить с помощью проверки значения столбца snapshot_isolation_state в представлении каталога sys.databases.

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    Транзакции с уровень изоляции READ_COMMITTED вместо блокировки используют управление версиями строк. Если транзакция выполняется с уровнем изоляции READ_COMMITTED, данные моментального снимка видны всем инструкциям в состоянии, которое существовало на момент начала выполнения инструкции.
  • OFF
    Транзакции, указывающие уровень изоляции READ_COMMITTED, используют блокировку.

  Для установки параметра READ_COMMITTED_SNAPSHOT в состояние ON или OFF к базе данных не должно быть активных подключений, за исключением подключения, выполняющего команду ALTER DATABASE. Однако это не означает, что база данных должна находиться в однопользовательском режиме. Изменить состояние этого параметра невозможно, если база данных находится в режиме OFFLINE.

  При установке параметра READ_COMMITTED_SNAPSHOT в базе данных, которая находится в режиме READ_ONLY, это состояние будет сохранено при переводе базы данных в режим READ_WRITE.

  Параметр READ_COMMITTED_SNAPSHOT не может быть установлен в ON для системных баз данных master, tempdb или msdb. При изменении настройки для базы данных model эта настройка становится значением по умолчанию для любых вновь создаваемых баз данных, за исключением tempdb.

  Текущее состояние этого параметра можно определить с помощью проверки значения столбца is_read_committed_snapshot_on в представлении каталога sys.databases.

WITH <termination>::=

Указывает, когда откатывать незавершенные транзакции при переходе базы данных из одного состояния в другое. Если предложение завершения опущено, инструкция ALTER DATABASE бесконечно ожидает блокировки базы данных. Может быть указано только одно предложение завершения, которое должно следовать за предложением SET.

Примечание.
Не все параметры базы данных могут использоваться с предложением WITH <termination>. Дополнительные сведения см. в таблице, расположенной в подразделе «Настройка параметров» раздела «Примечания».

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  Указывает, нужно ли откатить транзакцию через указанное количество секунд или немедленно.

• NO_WAIT
  Указывает, что если требуемое изменение состояния или параметра базы данных не может быть выполнено немедленно без ожидания фиксации или отката содержащей его транзакции, то запрос потерпит неудачу.

Замечания

Чтобы удалить базу данных, используйте инструкцию DROP DATABASE.

Чтобы переименовать базу данных, используйте параметр MODIFY NAME = new_database_name в инструкции ALTER DATABASE.

Чтобы уменьшить размер базы данных, используйте предложение DBCC SHRINKDATABASE.

Добавить или удалить файл во время выполнения инструкции BACKUP невозможно.

Для каждой базы данных можно указать не более 32 767 файлов и 32 767 файловых групп.

Инструкция ALTER DATABASE должна выполняться в режиме автоматической фиксации (режим управления транзакциями по умолчанию) и не разрешена в явной или неявной транзакции. Дополнительные сведения см. в разделе Автоматическая фиксация транзакций.

В SQL Server 2005 состояние файла базы данных (например, оперативное или автономное) поддерживается независимо от состояния базы данных. Дополнительные сведения см. в разделе Состояния файла. Состояние файлов в пределах файловой группы определяет доступность файловой группы в целом. Чтобы файловая группа была доступна, необходимо, чтобы все файлы в файловой группе находились в оперативном режиме. Если файловая группа автономна, то любая попытка обращения к файловой группе с помощью инструкции SQL закончится ошибкой. При создании планов запроса для инструкций SELECT оптимизатор запроса избегает некластеризованных индексов и индексированных представлений, которые находятся в автономных файловых группах. Это позволяет успешно выполнить эти инструкции. Однако, если файловая группа, находящаяся в автономном режиме, содержит область памяти или кластеризованный индекс целевой таблицы, инструкция SELECT не сможет быть выполнена. Кроме того, любая инструкция INSERT, UPDATE или DELETE, производящая изменения в таблице с любым индексом, расположенным в автономной файловой группе, также не будет выполнена.

Если база данных находится в состоянии RESTORING, выполнение большинства инструкций ALTER DATABASE закончится неудачей. Исключением является настройка параметров зеркального отображения базы данных. База данных может находиться в состоянии RESTORING во время выполнения операции восстановления или тогда, когда при операции восстановления базы данных или файла журнала происходит сбой из-за поврежденного файла резервной копии. Дополнительные сведения см. в разделе Действия при ошибках восстановления SQL Server, вызванных повреждением резервных копий.

Задание параметров

Для извлечения текущих настроек для параметров базы данных используйте представление каталога sys.databases или DATABASEPROPERTYEX. Список значений по умолчанию, назначенных базе данных при ее создании, см. в разделе Установка параметров базы данных.

После установки параметра базы данных изменение вступает в силу немедленно.

Чтобы изменить значения по умолчанию любого из параметров для всех вновь создаваемых баз данных, измените соответствующий параметр в базе данных model.

Не все параметры базы данных используют предложение WITH <termination> или могут быть указаны в сочетании с другими параметрами. В следующей таблице перечислены эти параметры.

Категория параметров	Может быть указан с другими параметрами	Может использовать предложение WITH <termination>
<db_state_option>	Да	Да
<db_user_access_option>	Да	Да
db_update_option>	Да	Да
<external_access_option>	Да	Нет
<cursor_option>	Да	Нет
<auto_option>	Да	Нет
<sql_option>	Да	Нет
<recovery_option>	Да	Нет
<database_mirroring_option>	Нет	Нет
ALLOW_SNAPSHOT_ISOLATION	Нет	Нет
READ_COMMITTED_SNAPSHOT	Нет	Да
<service_broker_option>	Да	Нет
DATE_CORRELATION_OPTIMIZATION	Да	Да
<parameterization_option>	Да	Да

Кэш планов для экземпляра SQL Server очищается при установке одного из следующих параметров.

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY

Очистка кэша планов становится причиной перекомпиляции всех последующих планов выполнения и приводит к непредвиденному временному снижению производительности обработки запросов. В SQL Server 2005 с пакетом обновления 2 (SP2) для каждого очищенного хранилища кэша в кэше планов журнал ошибок SQL Server содержит следующее информационное сообщение: «SQL Server обнаружил %d экземпляров, сброшенных на диск хранилищ кэша для хранилища кэша "%s" (части кэша планов) в результате операций по обслуживанию или изменению настройки базы данных». Это сообщение протоколируется каждые пять минут при сбросе кэша в течение этого временного интервала.

Перемещение файлов

В SQL Server 2005 системные или пользовательские данные и файлы журналов можно переместить, указав новое расположение в параметре FILENAME. Это может быть полезным в следующих случаях:

• восстановление при сбоях. Например, база данных находится в подозрительном режиме или завершила работу по причине сбоя оборудования;
• плановое перемещение;
• перемещение для запланированного обслуживания дисков.

Дополнительные сведения см. в разделе Перемещение файлов баз данных.

Инициализация файлов

По умолчанию файлы данных и журналов инициализируются, заполняясь нулями, при выполнении одной из следующих операций:

• создание базы данных;
• добавление файлов к существующей базе данных;
• увеличение размера существующего файла;
• восстановление базы данных или файловой группы.

В SQL Server 2005 файлы данных могут быть инициализированы немедленно. Это разрешено для быстрого выполнения этих файловых операций. Дополнительные сведения см. в разделе Инициализация файлов базы данных.

Изменение параметров сортировки в базе данных

Прежде чем применить другие параметры сортировки к базе данных, убедитесь, что соблюдены следующие условия.

1. Вы являетесь единственным пользователем базы данных в настоящее время.
2. Ни один объект, привязанный к схеме, не зависит от параметров сортировки базы данных.
   Если следующие объекты, зависящие от параметров сортировки базы данных, существуют в базе данных, инструкция ALTER DATABASE database_name COLLATE не будет выполнена. SQL Server возвратит сообщение об ошибке для каждого объекта, блокирующего операцию ALTER.
   • пользовательские функции и представления, созданные с помощью предложения SCHEMABINDING;
   • вычисляемые столбцы;
   • ограничения CHECK;
   • возвращающие табличное значение функции, которые возвращают таблицы с символьными столбцами, имеющими параметры сортировки, унаследованные из параметров сортировки базы данных по умолчанию.
3. При изменении параметров сортировки базы данных дубликаты любых системных имен для объектов базы данных не создаются.
   Следующие пространства имен могут вызвать сбой при изменении параметров сортировки базы данных, если при новых параметрах сортировки дублируются имена результатов:
   • имена объектов, такие как процедуры, таблицы, триггеры или представления;
   • имена схем;
   • участники, такие как группы, роли или пользователи;
   • имена скалярных типов, таких как системные и пользовательские типы;
   • имена полнотекстовых каталогов;
   • имена столбцов или параметров в пределах объекта;
   • имена индексов в пределах таблицы.
     Дублированные имена, полученные с помощью новых параметров сортировки, могут быть причиной сбоя операции изменения, а SQL Server возвратит сообщение об ошибке, указывающее пространство имен, в котором был найден дубликат.

Просмотр сведений о базе данных

Для возврата сведений о базах данных, файлах и файловых группах можно использовать представления каталогов, системные функции и системные хранимые процедуры. Дополнительные сведения см. в разделе Просмотр метаданных базы данных.

Разрешения

Необходимо разрешение ALTER на базу данных.

Примеры

A. Добавление файла к базе данных

В следующем примере к базе данных AdventureWorks добавляется файл данных размером 5 МБ.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = Test1dat2,
    FILENAME = '''+ @data_path + 't1dat2.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

Б. Добавление файловой группы с двумя файлами к базе данных

В следующем примере в базе данных AdventureWorks создается файловая группа Test1FG1 и добавляется два файла по 5 МБ в эту файловую группу.

USE master
GO
ALTER DATABASE AdventureWorks
ADD FILEGROUP Test1FG1;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD FILE 
(
    NAME = test1dat3,
    FILENAME = '''+ @data_path + 't1dat3.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    FILENAME = '''+ @data_path + 't1dat4.ndf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP Test1FG1'
);
GO

В. Добавление двух файлов журнала к базе данных

В следующем примере к базе данных AdventureWorks добавляется два файла журнала размером 5 МБ каждый.

USE master;
GO
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);
EXECUTE (
'ALTER DATABASE AdventureWorks 
ADD LOG FILE 
(
    NAME = test1log2,
    FILENAME = '''+ @data_path + 'test2log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = '''+ @data_path + 'test3log.ldf'',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)'
);
GO

Г. Удаление файла из базы данных

В следующем примере удаляется один из файлов, добавленных в примере Б.

USE master;
GO
ALTER DATABASE AdventureWorks
REMOVE FILE test1dat4;
GO

Д. Изменение файла

В следующем примере увеличивается размер одного из файлов, добавленных в примере Б.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILE
    (NAME = test1dat3,
    SIZE = 20MB);
GO

Е. Перемещение файла в новое расположение

В следующем примере файл Test1dat2, созданный в примере A, перемещается в новый каталог.

Примечание.
Перед выполнением этого примера необходимо физически переместить файл в новый каталог. После выполнения остановите и запустите экземпляр SQL Server или переведите базу данных AdventureWorks в состояние OFFLINE, а затем назад в ONLINE, чтобы осуществить изменения.

USE master;
GO
ALTER DATABASE AdventureWorks
MODIFY FILE
(
    NAME = Test1dat2,
    FILENAME = N'c:\t1dat2.ndf'
);
GO

Ж. Перемещение базы данных tempdb в новое расположение

В следующем примере база данных tempdb перемещается из ее текущего расположения на диске в другое расположение. Так как база данных tempdb повторно создается при каждом запуске службы MSSQLSERVER, нет необходимости физически переносить файлы данных и журнала. Эти файлы создаются при запуске службы на шаге 3. Пока служба не будет запущена повторно, база данных tempdb продолжает функционировать на прежнем месте.

1. Определите логические имена файлов базы данных tempdb и их текущее расположение на диске.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   GO
   

2. Измените местоположение каждого файла с помощью инструкции ALTER DATABASE.

   USE master;
   GO
   ALTER DATABASE tempdb 
   MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
   GO
   ALTER DATABASE  tempdb 
   MODIFY FILE (NAME = templog, FILENAME = 'E:\SQLData\templog.ldf');
   GO
   

3. Остановите и перезапустите экземпляр SQL Server.

4. Проверьте изменение файла.

   SELECT name, physical_name
   FROM sys.master_files
   WHERE database_id = DB_ID('tempdb');
   

5. Удалите файлы tempdb.mdf и templog.ldf из их исходного расположения.

З. Назначение файловой группы по умолчанию

В следующем примере файловая группа Test1FG1, созданная в примере Б, назначается файловой группой по умолчанию. Затем файловая группа по умолчанию будет переназначена на файловую группу PRIMARY. Обратите внимание, что слово PRIMARY должно быть заключено в скобки или в кавычки.

USE master;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks 
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

И. Установка параметров в базе данных

В следующем примере устанавливается модель восстановления и параметры проверки страницы данных для базы данных AdventureWorks .

USE master;
GO
ALTER DATABASE AdventureWorks 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

К. Перевод базы данных в состояние READ_ONLY

Для изменения состояния базы данных или файловой группы в READ_ONLY или READ_WRITE требуется монопольный доступ к базе данных. В следующем примере база данных устанавливается в режим SINGLE_USER для получения монопольного доступа. Затем состояние базы данных AdventureWorks устанавливается в READ_ONLY и возвращается доступ к базе данных всем пользователям.

Примечание.
В этом примере используется параметр завершения WITH ROLLBACK IMMEDIATE в первой инструкции ALTER DATABASE. Произойдет откат всех незавершенных транзакций, а любые другие соединения с базой данных AdventureWorks будут немедленно разорваны.

USE master;
GO
ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

Л. Предоставление изоляции моментального снимка в базе данных

Следующий пример включает параметр платформы изоляции моментального снимка для базы данных AdventureWorks.

USE AdventureWorks;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO
USE master;
GO
ALTER DATABASE AdventureWorks
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks';
GO

Результирующий набор показывает, что платформа изоляции моментального снимка включена.

name            snapshot_isolation_state  description
--------------- ------------------------  -----------
AdventureWorks  1                         ON

М. Создание сеанса зеркального отображения базы данных со свидетелем

Установка зеркального отображения базы данных со свидетелем требует настройки безопасности и подготовки зеркальной базы данных, а также использует инструкцию ALTER DATABASE, чтобы установить партнеров. Пример полностью законченного процесса установки см. в разделе Настройка зеркального отображения базы данных.

Н. Ручной переход на другой ресурс в сеансе зеркального отображения базы данных

Ручной переход на другой ресурс может быть инициализирован любым партнером зеркального отображения базы данных. Перед переходом на другой ресурс необходимо проверить, что сервер, который считается текущим основным сервером, действительно является основным сервером. Например, для базы данных AdventureWorks на экземпляре сервера, который предположительно является основным сервером в настоящий момент, выполните следующий запрос:

SELECT db.name, m.mirroring_role_desc 
FROM sys.database_mirroring m 
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks' 
GO

Если экземпляр сервера действительно является основным, значением mirroring_role_desc будет Principal. Если этот экземпляр сервера был зеркальным сервером, инструкция SELECT возвратит Mirror.

В следующем примере предполагается, что сервер является текущим основным сервером.

1. Ручной переход на другой ресурс партнера зеркального отображения базы данных:

   ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;
   GO
   

2. Чтобы проверить результаты перехода на новое зеркало, выполните следующий запрос:

   SELECT name, mirroring_role_desc 
   FROM sys.databases WHERE name = N'AdventureWorks';
   GO
   

   Текущее значение параметра mirroring_role_desc теперь Mirror.

См. также

Справочник

CREATE DATABASE (Transact-SQL)
DATABASEPROPERTYEX (Transact-SQL)
DROP DATABASE (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
Хранимая процедура sp_configure (Transact-SQL)
sp_spaceused (Transact-SQL)
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.database_mirroring_witnesses (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)
sys.master_files (Transact-SQL)

Другие ресурсы

Включение основанных на управлении версиями строк уровней изоляции
Системные базы данных

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
12 декабря 2006 г.	Новое содержимое В подраздел «Настройка параметров» раздела «Примечания» добавлены сведения о параметрах, очищающих кэш планов, а также добавлено определение параметра AUTO_CLOSE. Добавлены сведения об использовании параметра AUTO_UPDATE_STATISTICS_ASYNC в однопользовательском режиме в определении SINGLE_USER.
14 апреля 2006 г.	Измененное содержимое Обновлено описание параметра FAILOVER для указания того, что в качестве контекста базы данных должна использоваться база данных master. Добавлено важное замечание во введении к разделу «<database_mirroring_option>». Ниже описания аргумента ALLOW_SNAPSHOT_ISOLATION обновлены сведения об определении состояния транзакций с уровнем изоляции моментальных снимков в базе данных и о том, как это состояние влияет на поведение SQL Server при изменении данного параметра. Исправлено определение параметра DATE_CORRELATION_OPTIMIZATION.
5 декабря 2005 г.	Новое содержимое Добавлено примечание к определению параметра ENABLE_BROKER. Добавлена рекомендация по изменению параметра PAGE_VERIFY в обновленных базах данных. Измененное содержимое Удален параметр SUPPLEMENTAL_LOGGING. Исправлен пример Ж. Исправлены сведения о параметре TRUSTWORTHY в системных базах данных. Обновлено определение параметра READ_COMMITTED_SNAPSHOT, которое теперь указывает, что нет необходимости переходить в однопользовательский режим. Обновлено определение параметра <db_state_option>, в котором теперь указано, что параметры OFFLINE, ONLINE и EMERGENCY не могут быть установлены, если база данных находится в состоянии RESTORING.