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

* SQL Server *  

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

 

Синтаксис

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 при ссылке на файл.

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 в пределах этой файловой группы окончится неудачей.

<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.

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

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

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

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

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

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

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

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

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

Если размер не указан для первичного файла, SQL Server использует размер первичного файла в базе данных модели . Если указан дополнительный файл данных или файл журнала, но размер файла не указан для этого файла, ядро СУБД делает файл размером 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 ТБ. Ограничения размера отсутствуют, если этот параметр указан для контейнера FILESTREAM. Размер продолжает увеличиваться до полного заполнения диска.

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 %.

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

<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".

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) и более поздних версий)

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

<filegroup_updatability_option>::=

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

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

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

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

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

Замечания

Чтобы уменьшить размер базы данных, используйте предложение 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, чтобы удалить этот контейнер.

Примеры

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

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

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

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

В следующем примере создается файловая группа 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

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

В следующем примере в базу данных 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

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

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

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

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

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

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

G. Перемещение базы данных 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 из их исходного расположения.

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

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

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

I. Добавление файловой группы с помощью инструкции 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

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

В следующем примере создаются необходимые инструкции 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

См. также

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• Большие двоичные объекты
• DBCC SHRINKFIL
• sp_filestream_force_garbage_collection
• Инициализация файлов базы данных

SQL Server

* Управляемый экземпляр 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 является новым размером файла и должен превышать текущий размер файла.

Если размер не указан для первичного файла, SQL Server использует размер первичного файла в базе данных модели . Если указан дополнительный файл данных или файл журнала, но размер файла не указан для этого файла, ядро СУБД делает файл размером 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.

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

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

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

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

Замечания

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

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

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

Примеры

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

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

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

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

В следующем примере создается файловая группа 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

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

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

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

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

В следующем примере увеличивается размер одного из файлов, добавленных в примере Б. Инструкция 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

F. Добавление файловой группы с помощью инструкции 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

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

В следующем примере создаются необходимые инструкции 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

См. также

• CREATE DATABASE
• DATABASEPROPERTYEX
• DROP DATABASE
• sp_spaceused
• sys.databases
• sys.database_files
• sys.data_spaces
• sys.filegroups
• sys.master_files
• DBCC SHRINKFILE
• Оптимизированная для памяти файловая группа