Параметры инструкции ALTER DATABASE для файлов и файловых групп (Transact-SQL)

Изменяет файлы и файловые группы, связанные с базой данных. Добавляет или удаляет файлы и файловые группы из базы данных и изменяет атрибуты базы данных или ее файлов и файловых групп. См. дополнительные сведения о других параметрах ALTER DATABASE.

Дополнительные сведения о соглашениях о синтаксисе см. в статье Соглашения о синтаксисе в Transact-SQL.

Выберите продукт

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

* SQL Server *  

 

Синтаксис

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}

<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' | 'filestream_path' | 'memory_optimized_data_path' } ]
    [ , 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
        [ CONTAINS FILESTREAM | CONTAINS MEMORY_OPTIMIZED_DATA ]
    | REMOVE FILEGROUP filegroup_name
    | MODIFY FILEGROUP filegroup_name
        { <filegroup_updatability_option>
        | DEFAULT
        | NAME = new_filegroup_name
        | { AUTOGROW_SINGLE_FILE | AUTOGROW_ALL_FILES }
        }
}
<filegroup_updatability_option>::=
{
    { READONLY | READWRITE }
    | { READ_ONLY | READ_WRITE }
}

Аргументы

<add_or_modify_files>::=

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

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

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

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

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

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

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

Предупреждение

Удаление файла базы данных, имеющего связанные с ним резервные копии FILE_SNAPSHOT, выполнится успешно, однако связанные моментальные снимки не будут удалены во избежание объявления недействительными резервных копий, ссылающихся на файл базы данных. Файл усекается, но физически не удаляется, чтобы сохранить резервные копии FILE_SNAPSHOT без изменений. Дополнительные сведения см. в разделе SQL Server Резервное копирование и восстановление с помощью Microsoft Хранилище BLOB-объектов Azure. Применимо к: SQL Server (SQL Server 2016 (13.x); и выше).

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. Не указывайте имя файла в операционной системе.

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

Для файловой группы FILESTREAM значение NAME можно изменять в режиме в сети. Значение FILENAME можно изменять в режиме в сети, но внесенное изменение вступает в силу лишь после того, как будет выполнено физическое перемещение контейнера, а также остановка и последующий перезапуск сервера.

Можно задать значение параметра файла FILESTREAM, равное OFFLINE. Если файл FILESTREAM определен как вне сети, его родительская файловая группа отмечается внутри как вне сети, поэтому любая попытка доступа к данным FILESTREAM в пределах этой файловой группы окончится неудачей.

Примечание

Параметры <add_or_modify_files> недоступны в автономной базе данных.

<filespec>::=

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

NAME logical_file_name — задает логическое имя файла.

logical_file_name — логическое имя, используемое в экземпляре SQL Server при ссылке на файл.

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

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

FILENAME { 'os_file_name' | 'filestream_path' | 'memory_optimized_data_path'} — задает имя (физического) файла в операционной системе.

'os_file_name' — для стандартной файловой группы (ROWS) этот параметр представляет собой путь и имя файла, которые использовались операционной системой при создании файла. Файл должен постоянно храниться на сервере, на котором установлен SQL Server. Указанный путь должен существовать до выполнения инструкции ALTER DATABASE.

Примечание

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

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

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

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

'filestream_path' — для файловой группы FILESTREAM параметр FILENAME указывает путь, где будут храниться данные FILESTREAM. Должен существовать путь вплоть до последнего каталога, но последний каталог существовать не должен. Например, если указан путь C:\MyFiles\MyFilestreamData, то каталог C:\MyFiles должен существовать до выполнения инструкции ALTER DATABASE, но папка MyFilestreamData не должна существовать.

Примечание

Свойства SIZE и FILEGROWTH к файловой группе FILESTREAM неприменимы.

'memory_optimized_data_path' — для файловой группы, оптимизированной для памяти, FILENAME указывает путь, по которому будут храниться данные, оптимизированные для памяти. Должен существовать путь вплоть до последнего каталога, но последний каталог существовать не должен. Например, если указан путь C:\MyFiles\MyData, то каталог C:\MyFiles должен существовать до выполнения инструкции ALTER DATABASE, но папка MyData не должна существовать.

Файловую группу и файл (<filespec>) необходимо создавать в одной инструкции.

Примечание

Свойства SIZE и FILEGROWTH не относятся к файловой группе MEMORY_OPTIMIZED_DATA.

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

SIZE size — указывает размер файла. Параметр SIZE не применяется к файловым группам FILESTREAM.

size — размер файла.

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

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

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

Примечание

SIZE невозможно задать:

  • если указан UNC-путь к файлу;
  • для файловых групп FILESTREAM и MEMORY_OPTIMIZED_DATA.

MAXSIZE { max_size| UNLIMITED } — задает максимальное значение, до которого может увеличиваться размер файла.

max_size — максимальный размер файла. Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. По умолчанию — MБ. Укажите целое число без десятичного разделителя. Если аргумент max_size не указан, то размер файла может увеличиваться до тех пор, пока диск не будет заполнен.

UNLIMITED — указывает, что размер файла может увеличиваться вплоть до заполнения диска. В SQL Server файл журнала, для которого задано неограниченное увеличение размера, имеет максимальный размер 2 ТБ, а файл данных — 16 ТБ. Ограничения размера отсутствуют, если этот параметр указан для контейнера FILESTREAM. Размер продолжает увеличиваться до полного заполнения диска.

Примечание

MAXSIZE невозможно задать, если указан UNC-путь к файлу.

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

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

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

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

Если параметр FILEGROWTH не задан, доступны следующие значения по умолчанию.

Версия Значения по умолчанию
Начиная с SQL Server 2016 (13.x); Данные — 64 МБ. Файлы журналов — 64 МБ.
Начиная с SQL Server 2005 (9.x) Данные — 1 МБ. Файлы журналов — 10 %.
До SQL Server 2005 (9.x) Данные — 10 %. Файлы журналов — 10 %.

Примечание

FILEGROWTH невозможно задать:

  • если указан UNC-путь к файлу;
  • для файловых групп FILESTREAM и MEMORY_OPTIMIZED_DATA.

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

Внимание!

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

Параметры <filespec> недоступны в автономной базе данных.

<add_or_modify_filegroups>::=

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

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

CONTAINS FILESTREAM — указывает, что файловая группа хранит большие двоичные объекты (BLOB-объекты) FILESTREAM в файловой системе.

CONTAINS MEMORY_OPTIMIZED_DATA

Применимо к: SQL Server (SQL Server 2014 (12.x) и выше)

Указывает, что файловая группа хранит оптимизируемые для памяти данные в файловой системе. Дополнительные сведения см. в статье In-Memory OLTP (оптимизация в памяти). Для каждой базы данных можно использовать только одну файловую группу MEMORY_OPTIMIZED_DATA. Для создания таблицы, оптимизируемой для памяти, файловая группа не может быть пустой. Должно быть не менее одного файла. filegroup_name содержит путь. Должен существовать путь вплоть до последнего каталога, но последний каталог существовать не должен.

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

Примечание

Если сборщик мусора FILESTREAM не удалил все файлы из контейнера FILESTREAM, операция ALTER DATABASE REMOVE FILE по удалению контейнера FILESTREAM завершится неудачей и возвратит ошибку. См. раздел Удаление контейнера FILESTREAM далее в этой статье.

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.

AUTOGROW_SINGLE_FILE — применимо кSQL Server (SQL Server 2016 (13.x); и выше)

Если файл в файловой группе удовлетворяет требованиям порога автоматического увеличения, увеличивается только этот файл. Это значение по умолчанию.

AUTOGROW_ALL_FILES

Применимо к: SQL Server (SQL Server 2016 (13.x); и выше)

Если файл в файловой группе удовлетворяет требованиям порога автоматического увеличения, все файлы в файловой группе увеличиваются.

Примечание

Это значение по умолчанию для tempdb.

<filegroup_updatability_option>::=

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

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

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

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

Примечание

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

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

Примечание

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

Совет

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

Remarks

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

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

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

Начиная с SQL Server 2005 (9.x) и в более поздних версиях состояние файла базы данных (например, в сети или вне сети) поддерживается независимо от состояния базы данных. Дополнительные сведения см. в разделе Состояния файлов.

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

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

Параметры SIZE и FILEGROWTH невозможно задать для файловых групп, оптимизированных для памяти.

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

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

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

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

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

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

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

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

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

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

Удаление контейнера FILESTREAM

Даже если контейнер FILESTREAM был очищен с использованием операции "DBCC SHRINKFILE", базе данных по-прежнему могут быть необходимы ссылки на удаленные файлы по различным причинам, связанным с обслуживанием системы. Хранимая процедура sp_filestream_force_garbage_collection запустит сборщик мусора FILESTREAM, чтобы удалить эти файлы, когда это будет безопасно. Если сборщик мусора FILESTREAM не удалил все файлы из контейнера FILESTREAM, операция ALTER DATABASE REMOVE FILE по удалению контейнера FILESTREAM завершится неудачей и вернет ошибку. Для удаления контейнера FILESTREAM рекомендуется следующий процесс.

  1. Выполните инструкцию DBCC SHRINKFILE с параметром EMPTYFILE, чтобы переместить активное содержимое этого контейнера в другие контейнеры.
  2. Убедитесь, что в модели восстановления FULL или BULK_LOGGED было выполнено резервное копирование журналов.
  3. Убедитесь, что было запущено задание чтения журнала репликации, если это необходимо.
  4. Выполните хранимую процедуру sp_filestream_force_garbage_collection, чтобы сборщик мусора удалил все файлы, которые больше не нужны в этом контейнере.
  5. Выполните инструкцию ALTER DATABASE с параметром REMOVE FILE, чтобы удалить этот контейнер.
  6. Повторите шаги с 2 по 4, чтобы завершить сборку мусора.
  7. Используйте инструкцию ALTER Database...REMOVE FILE, чтобы удалить этот контейнер.

Примеры

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

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = Test1dat2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

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

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

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat3.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),  
(  
    NAME = test1dat4,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat4.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

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

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD LOG FILE
(
    NAME = test1log2,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\test2log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1log3,
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test3log.ldf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

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

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

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

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

В следующем примере увеличивается размер одного из файлов, добавленных в примере Б. Инструкция ALTER DATABASE с командой MODIFY FILE может только увеличить размер файла, поэтому, если нужно уменьшить размер файла, следует использовать DBCC SHRINKFILE.

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

В этом примере показано уменьшение размера файла данных до 100 МБ и определение размера при этом объеме.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

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

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

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

Примечание

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
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 AdventureWorks2022
MODIFY FILEGROUP Test1FG1 DEFAULT;
GO
ALTER DATABASE AdventureWorks2022
MODIFY FILEGROUP [PRIMARY] DEFAULT;
GO

И. Добавление файловой группы с помощью инструкции ALTER DATABASE

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

--Create and add a FILEGROUP that CONTAINS the FILESTREAM clause.
ALTER DATABASE FileStreamPhotoDB
ADD FILEGROUP TodaysPhotoShoot
CONTAINS FILESTREAM;
GO

--Add a file for storing database photos to FILEGROUP
ALTER DATABASE FileStreamPhotoDB
ADD FILE
(
  NAME= 'PhotoShoot1',
  FILENAME = 'C:\Users\Administrator\Pictures\TodaysPhotoShoot.ndf'
)
TO FILEGROUP TodaysPhotoShoot;
GO

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

--Create and add a FILEGROUP that CONTAINS the MEMORY_OPTIMIZED_DATA clause.
ALTER DATABASE xtp_db
ADD FILEGROUP xtp_fg
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

--Add a file for storing memory optimized data to FILEGROUP
ALTER DATABASE xtp_db
ADD FILE
(
  NAME='xtp_mod',
  FILENAME='d:\data\xtp_mod'
)
TO FILEGROUP xtp_fg;
GO

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

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

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
  SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

  SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
  INSERT INTO #tmpfgs
  EXEC (@query)

  UPDATE #tmpdbs
  SET isdone = 1
  WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
  WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
  BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

    SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

    PRINT @query

    UPDATE #tmpfgs
    SET isdone = 1
    WHERE [dbid] = @dbid AND fgname = @fgname
  END
END;
GO

См. также:

* Управляемый экземпляр SQL *
 

 

Управляемый экземпляр SQL Azure

Используйте эту инструкцию с базой данных в Управляемом экземпляре SQL Azure.

Синтаксис для Управляемого экземпляра SQL Azure

ALTER DATABASE database_name
{
    <add_or_modify_files>
  | <add_or_modify_filegroups>
}
[;]

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

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

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

Аргументы

<add_or_modify_files>::=

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

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

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

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

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

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

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

<filespec>::=

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

NAME logical_file_name — задает логическое имя файла.

logical_file_name — логическое имя, используемое в экземпляре SQL Server при ссылке на файл.

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

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

SIZE size — указывает размер файла.

size — размер файла.

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

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

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

MAXSIZE { max_size| UNLIMITED } — задает максимальное значение, до которого может увеличиваться размер файла.

max_size — максимальный размер файла. Суффиксы KB, MB, GB и TB могут использоваться для указания килобайтов, мегабайтов, гигабайтов или терабайтов. По умолчанию — MБ. Укажите целое число без десятичного разделителя. Если аргумент max_size не указан, то размер файла может увеличиваться до тех пор, пока диск не будет заполнен.

UNLIMITED — указывает, что размер файла может увеличиваться вплоть до заполнения диска. В SQL Server файл журнала, для которого задано неограниченное увеличение размера, имеет максимальный размер 2 ТБ, а файл данных — 16 ТБ.

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

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

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

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

Если параметр FILEGROWTH не задан, доступны следующие значения по умолчанию.

  • Данные — 16 МБ.
  • Файлы журналов — 16 МБ.

<add_or_modify_filegroups>::=

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

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

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

ALTER DATABASE sql_db_mi ADD FILEGROUP sql_db_mi_fg;
GO
ALTER DATABASE sql_db_mi ADD FILE (NAME='sql_db_mi_mod') TO FILEGROUP sql_db_mi_fg;

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.

AUTOGROW_SINGLE_FILE

Если файл в файловой группе удовлетворяет требованиям порога автоматического увеличения, увеличивается только этот файл. Это значение по умолчанию.

AUTOGROW_ALL_FILES

Если файл в файловой группе удовлетворяет требованиям порога автоматического увеличения, все файлы в файловой группе увеличиваются.

<filegroup_updatability_option>::=

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

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

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

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

Примечание

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

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

Примечание

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

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

Remarks

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

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

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

Примеры

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

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

USE master;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
  NAME = Test1dat2,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
);
GO

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

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

USE master
GO
ALTER DATABASE AdventureWorks2022
ADD FILEGROUP Test1FG1;
GO
ALTER DATABASE AdventureWorks2022
ADD FILE
(
    NAME = test1dat3,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
),
(
    NAME = test1dat4,
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)  
TO FILEGROUP Test1FG1;
GO

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

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

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

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

В следующем примере увеличивается размер одного из файлов, добавленных в примере Б. Инструкция ALTER DATABASE с командой MODIFY FILE может только увеличить размер файла, поэтому, если нужно уменьшить размер файла, следует использовать DBCC SHRINKFILE.

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

В этом примере показано уменьшение размера файла данных до 100 МБ и определение размера при этом объеме.

USE AdventureWorks2022;
GO

DBCC SHRINKFILE (AdventureWorks2022_data, 100);
GO

USE master;
GO

ALTER DATABASE AdventureWorks2022
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
GO

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

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

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

Е. Добавление файловой группы с помощью инструкции ALTER DATABASE

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

--Create and add a FILEGROUP
ALTER DATABASE MyDB
ADD FILEGROUP NewFG;
GO

--Add a file to FILEGROUP
ALTER DATABASE MyDB
ADD FILE
(
    NAME= 'MyFile',
)
TO FILEGROUP NewFG;
GO

Ж. Изменение файловой группы таким образом, что если файл в файловой группе удовлетворяет требованиям порога автоматического увеличения, увеличиваются все файлы в файловой группе

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

--Generate ALTER DATABASE ... MODIFY FILEGROUP statements
--so that all read-write filegroups grow at the same time.
SET NOCOUNT ON;

DROP TABLE IF EXISTS #tmpdbs
CREATE TABLE #tmpdbs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, isdone BIT);

DROP TABLE IF EXISTS #tmpfgs
CREATE TABLE #tmpfgs (id INT IDENTITY(1,1), [dbid] INT, [dbname] sysname, fgname sysname, isdone BIT);

INSERT INTO #tmpdbs ([dbid], [dbname], [isdone])
SELECT database_id, name, 0 FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0 AND state = 0;

DECLARE @dbid INT, @query VARCHAR(1000), @dbname sysname, @fgname sysname

WHILE (SELECT COUNT(id) FROM #tmpdbs WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [dbname], @dbid = [dbid] FROM #tmpdbs WHERE isdone = 0

    SET @query = 'SELECT ' + CAST(@dbid AS NVARCHAR) + ', ''' + @dbname + ''', [name], 0 FROM [' + @dbname + '].sys.filegroups WHERE [type] = ''FG'' AND is_read_only = 0;'
    INSERT INTO #tmpfgs
    EXEC (@query)

    UPDATE #tmpdbs
    SET isdone = 1
    WHERE [dbid] = @dbid
END;

IF (SELECT COUNT(ID) FROM #tmpfgs) > 0
BEGIN
    WHILE (SELECT COUNT(id) FROM #tmpfgs WHERE isdone = 0) > 0
    BEGIN
        SELECT TOP 1 @dbname = [dbname], @dbid = [dbid], @fgname = fgname FROM #tmpfgs WHERE isdone = 0

        SET @query = 'ALTER DATABASE [' + @dbname + '] MODIFY FILEGROUP [' + @fgname + '] AUTOGROW_ALL_FILES;'

        PRINT @query

        UPDATE #tmpfgs
        SET isdone = 1
        WHERE [dbid] = @dbid AND fgname = @fgname
    END
END;
GO

См. также: