Filegrowth sql что это
Создание и удаление БД
В SQL Server‘е базы данных хранятся в виде обычных файлов на диске. Как минимум на одну БД приходится таких файлов 2: *.mdf и *.ldf. В первом хранятся сами данные, таблицы, индексы и пр., а во втором находится т.н. transaction log, в котором находится информация необходимая для восстановления БД.
Как можно создать новую БД? Это можно сделать 2 способами:
Использование SQL Server Enterprise Manager
Использование SQL оператора CREATE DATABASE
Оператор CREATE DATABASE может повторить все ваши действия, описанные выше. Собственно Enterprise Manager «перевел» ваши нажатия в этот оператор и передал его SQL Server’у. Почти все функции Enterprise Manager’а именно так и выполняются: у пользователя запрашиваются данные, формируется оператор замечательного языка SQL (который мы рассмотрим позднее) и передается для выполнения SQL Server, а результаты выполнения показывают пользователю.
Рассмотрим поближе синтаксис оператора CREATE DATABASE.
Все что мы создали можно и уничтожить. Удаление БД очень просто, как говорится «ломать не строить». SQL оператор:
Набирается в Query Analyzer и запускается на выполнение. Фю-ю-и-ить, и БД уже нет.
В Enterprise Manager’e выберите БД щелкните правой кнопкой мыши и в контекстном меню выберите «Delete» и все. пропало.
На следующем этапе мы рассмотрим создание таблиц, типы данных в Transact-SQL.
Файлы и файловые группы базы данных
Каждая база данных SQL Server имеет как минимум два рабочих системных файла: файл данных и файл журнала. Файлы данных содержат данные и объекты, такие как таблицы, индексы, хранимые процедуры и представления. Файлы журнала содержат сведения, необходимые для восстановления всех транзакций в базе данных. Файлы данных могут быть объединены в файловые группы для удобства распределения и администрирования.
Файлы базы данных
SQL Server имеют три типа файлов.
| Файл | Описание |
|---|---|
| Первичная | Содержит сведения, необходимые для запуска базы данных, и ссылки на другие файлы в базе данных. В каждой базе данных имеется один первичный файл данных. Для имени первичного файла данных рекомендуется расширение MDF. |
| Вторичная | Необязательные определяемые пользователем файлы данных. Данные могут быть распределены на несколько дисков, в этом случае каждый файл записывается на отдельный диск. Для имени вторичного файла данных рекомендуется расширение NDF. |
| Журнал транзакций | Журнал содержит информацию для восстановления базы данных. Для каждой базы данных должен существовать хотя бы один файл журнала. Для файлов журнала транзакций рекомендуется расширение LDF. |
Например, простая база данных с именем Sales включает один первичный файл, содержащий все данные и объекты, и один файл журнала, содержащий сведения журнала транзакций. Более сложная база данных с именем Orders может содержать один первичный файл и пять вторичных файлов. Данные и объекты внутри базы данных распределяются по всем шести файлам, а четыре файла журнала содержат сведения журнала транзакций.
По умолчанию и данные, и журналы транзакций помещаются на один и тот же диск и имеют один и тот же путь для обработки однодисковых систем. Для производственных сред это может быть неоптимальным решением. Рекомендуется помещать данные и файлы журнала на разные диски.
Логические и физические имена файлов
Файлы SQL Server имеют два типа имен файлов.
logical_file_name: имя, используемое для ссылки на физический файл во всех инструкциях Transact-SQL. Логическое имя файла должно соответствовать правилам для идентификаторов SQL Server и быть уникальным среди логических имен файлов в соответствующей базе данных.
os_file_name: имя физического файла, включающее путь к каталогу. Оно должно соответствовать правилам для имен файлов операционной системы.
Дополнительные сведения об аргументах NAME и FILENAME см. в статье Параметры ALTER DATABASE ((Transact-SQL)) для файлов и файловых групп.
Файлы данных и файлы журналов SQL Server могут использоваться как в файловой системе FAT, так и в системе NTFS. В системах Windows рекомендуется использовать файловую систему NTFS по причинам ее большей безопасности.
Файловые группы, доступные как для чтения, так и для записи, а также файлы журналов не поддерживаются со сжатой файловой системой NTFS. В сжатую файловую систему NTFS разрешено помещать лишь доступные только для чтения базы данных и доступные только для чтения вторичные файловые группы. Для экономии места настоятельно рекомендуется использовать сжатие данных вместо сжатия файловой системы.
Если на одном компьютере запущено несколько экземпляров SQL Server, каждый экземпляр получает отдельный каталог по умолчанию для хранения файлов баз данных, созданных в этом экземпляре. Дополнительные сведения см. в разделе Расположение файлов для экземпляра по умолчанию и именованных экземпляров SQL Server.
Страницы файлов данных
Страницы файлов данных SQL Server нумеруются последовательно; первая страница файла получает нулевой номер (0). Каждый файл базы данных имеет уникальный цифровой идентификатор. Чтобы уникальным образом определить страницу базы данных, необходимо использовать как идентификатор файла, так и номер этой страницы. В следующем примере показаны номера страниц базы данных, содержащей первичный файл данных объемом в 4 МБ и вторичный файл данных объемом в 1 МБ.
Страница заголовка файла — это первая, содержащая сведения об атрибутах данного файла. Некоторые другие страницы, расположенные в начале файла, тоже содержат системные сведения, например карты размещения. Одна из системных страниц, хранимых как в первичном файле данных, так и в первом файле журнала, представляет собой загрузочную страницу базы данных, которая содержит сведения об атрибутах этой базы данных.
Размер файла
Файлы SQL Server могут автоматически увеличиваться в размерах, превосходя первоначально заданные показатели. При определении файла пользователь может указывать требуемый шаг роста. Каждый раз при заполнении файла его размер увеличивается на указанный шаг роста. Если в файловой группе имеется несколько файлов, их автоматический рост начинается лишь по заполнении всех файлов.
Дополнительные сведения о страницах и их типах см. в разделе Руководство по архитектуре страниц и экстентов.
Кроме того, можно указать максимальный размер каждого файла. Если максимальный размер файла не указан, файл может продолжать увеличиваться в размерах, пока не займет все доступное место на диске. Эта функция особенно полезна в случаях, когда SQL Server используется в качестве базы данных, внедренной в приложение, где пользователь не имеет удобного доступа к системному администратору. По мере необходимости пользователь может предоставить файлам возможность увеличиваться в размерах автоматически, тем самым снимая с администратора часть забот по наблюдению за свободным пространством базы данных и по распределению дополнительного пространства вручную.
Дополнительные сведения об управлении файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
Файлы моментального снимка базы данных
Вид файла, используемый для хранения копируемых во время записи данных моментального снимка базы данных, зависит от того, создается ли моментальный снимок пользователем или используется внутренними механизмами.
Файловые группы
Все файлы данных хранятся в файловых группах, перечисленных в следующей таблице.
| Файловая группа | Описание |
|---|---|
| Первичная | Файловая группа, содержащая первичный файл. Все системные таблицы являются частью первичной файловой группы. |
| Данные, оптимизированные для памяти | В основе оптимизированной для памяти файловой группы лежит файловая группа файлового потока. |
| Файловый поток | |
| Определяемые пользователем маршруты | Любая файловая группа, созданная пользователем при создании или изменении базы данных. |
Файловая группа по умолчанию (первичная)
Если в базе данных создаются объекты без указания файловой группы, к которой они относятся, они назначаются файловой группе по умолчанию. В любом случае только одна файловая группа создается как файловая группа по умолчанию. Файлы в файловой группе по умолчанию должны быть достаточно большими, чтобы вмещать новые объекты, не назначенные другим файловым группам.
Файловая группа PRIMARY является группой по умолчанию, если только она не была изменена инструкцией ALTER DATABASE. Системные объекты и таблицы распределяются внутри первичной файловой группы, а не новой файловой группой по умолчанию.
Файловая группа данных, оптимизированных для памяти
Дополнительные сведения об оптимизированных для памяти файловых группах см. в разделе Оптимизированные для памяти файловые группы.
Файловая группа файлового потока
Дополнительные сведения о файловых группах файлового потока см. в статьях FILESTREAM и Создание базы данных с поддержкой FILESTREAM.
Пример файлов и файловых групп
Данная иллюстрация обобщает все вышесказанное (кроме данных файлового потока).
Стратегия заполнения файлов и файловых групп
В файловых группах для каждого файла используется стратегия пропорционального заполнения. При записи данных в файловую группу компонент Компонент SQL Server Database Engine записывает в каждый файл количество данных, пропорциональное свободному пространству этого файла, вместо записи всех данных в первый файл до его заполнения. Затем запись производится в следующий файл. Например, если в файле f1 свободно 100 МБ, а в файле f2 — 200 МБ, то в файл f1 записывается одна часть данных, а в файл f2 — две части, и так далее. Таким образом, оба файла будут заполнены примерно в одно и то же время, и достигается простейшее распределение данных между хранилищами.
Например, файловая группа состоит из трех файлов, для всех разрешено автоматическое увеличение. Когда свободное пространство во всех файлах группы закончится, будет расширен только первый файл. Когда заполнится первый файл и в файловую группу снова нельзя будет записывать новые данные, будет расширен второй файл. Когда заполнится второй файл и в файловую группу опять нельзя будет записывать новые данные, будет расширен третий файл. Когда заполнится третий файл и в файловую группу нельзя будет записывать новые данные, будет снова расширен первый файл и т. д.
Правила проектирования файлов и файловых групп
Для файлов и файловых групп действуют следующие правила:
Рекомендации
Рекомендации при работе с файлами и файловыми группами:
Дополнительные рекомендации по управлению файлами журнала транзакций см. в разделе Управление размером файла журнала транзакций.
1.3. Редактирование параметров базы данных
Изменения в настройках базы данных происходят не часто, но они происходят. В этой главе мы научимся вносить изменения в базе данных с помощью SQL. Для изменения используется оператор ALTER DATABASE. В общем виде этот оператор можно увидеть в листинге 1.8.
Листинг 1.8. Общий вид команды ALTER DATABASE
В первой строке параметр database – это имя, базы данных, которую надо изменить. Далее построчно идут параметры или свойства базы данных, которые можно, а иногда и нужно изменить. Давайте рассматривать возможности команды с одновременным рассмотрением примеров, потому что возможностей у ALTER DATABASE столько же, сколько и у команды создания базы данных CREATE DATABASE.
1.3.1. Изменение файла
Когда данные увеличиваются или увеличивается активность изменения данных, вам может понадобиться увеличить размер файлов данных или журнала. Для увеличения размер файлов можно использовать программу управления базой данных SQL Server Enterprise Manager, которая обладает визуальным интерфейсом или воспользоваться оператором ALTER DATABASE.
Вы можете контролировать размер базы данных с помощью:
Вы можете установить автоматическое приращение с помощью оператора ALTER DATABASE. Использование автоматического приращения уменьшает администраторские задачи, заключенные в ручном увеличении размера базы данных, но сложнее контролировать количество используемого сервером MS SQL Server пространства.
Увеличение лучше всего контролировать самостоятельно. Да, это отнимет лишнее рабочее время и потребует некоторых усилий, но контроль никогда не бывает лишним. Спокойно спит только тот администратор, у которого все находиться под контролем.
Если вы не сконфигурировали существующие файлы на автоматическое приращение, вы все еще можете увеличить размер. Если указан ноль для значения автоматического увеличения, значит, автоматического увеличения нет.
Вы можете создать вторичные файлы базы данных, для расширения размера базы данных. Но лучше использовать дополнительные файлы базы данных, чтобы расположить файлы на отдельном физическом диске или выделить объект базы данных из общей массы.
Следующий пример увеличивает текущий размер журнала базы данных NewDB:
Теперь попробуем добавить дополнительный файл. Да, и это возможно. Допустим, что свободное пространство на жестком диске, но у вас есть дисковый массив, который позволяет управлять массивом на горячую. Прямо во время работы вставляем новый диск, создаем на нем файл для базы данных и продолжаем спать спокойно. Следующий пример показывает, как можно добавить файл к базе данных Sample.
Вот тут необходимо сделать одно замечание, если для переноса данных с одного сервера на другой захотите воспользоваться функциями отключения и подключения базы данных, то не забудьте скопировать в новое место все файлы.
Допустим, что вам необходимо произвести массовую загрузку данных. База данных на данный момент занимает 10 мегабайт, а приращение, указанное в настройках в случае нехватки места составляет 1 мегабайт. Если вы будете загружать еще 10МБ данных, то во время загрузки произойдет 10 увеличений по одному мегабайту файла данных. Это отнимет лишнее время, поэтому вполне логичным будет увеличить размер файла вручную.
Для ручного увеличения размера файла до 200Мб необходимо выполнить следующую команду:
Вторая строка содержит команду MODIFY FILE, что означает изменения файла. В скобках необходимо указать имя файла, размер которого надо изменить в параметре NAME и новый размер. Новый размер обязательно должен быть больше текущего, потому что команда позволяет только увеличивать файл, но не уменьшать.
Если вы хотите узнать текущий размер файлов или не помните имена, выполните команду:
Состояния базы данных можно увидеть выполнив команду: EXEC sp_helpdb TestDatabase.
Помните, что таким образом вы можете только увеличивать размер файлов, но не уменьшать. Уменьшение файлов данных и журнала – это отдельная тема. Итак, если вы создали базу данных с файлом в 1 Мб, и потом увеличили ее размер до 5 мегабайт то вернуть изначальный размер командой ALTER DATABASE невозможно. Это значит, что следующий запрос вернет ошибку, если размер файла FlenovSQLBook больше 2Мб:
С помощью команды ALTER DATABASE можно изменять и размер файла журнала, только в параметре NAME нужно указать имя файла журнала, который нужно увеличить:
Для оптимальной производительности сервера и уменьшения количества увеличения файлов (это отнимает лишнее время и может затормозить работу сервера):
Когда база данных увеличивается или когда увеличивается активность изменений, вам может понадобиться расширить журнал транзакций. Внимательно следите, чтобы журнал транзакций не оказался слишком маленьким. Регулярное наблюдение за журналом поможет вам определить оптимальное увеличение журнала и не допустить его переполнения. Если ваш журнал транзакций не может быть увеличен, то SQL Server не сможет записывать транзакции и не позволить делать изменения в базе данных.
Вы можете следить за журналом транзакций с помощью SQL Server Enterprise Manager, оператора DBCC SQLPERF (LOGSPACE) (подробней о команде в разделе 4.2.5) или системный монитор Windows.
Если данные изменяются достаточно часто, то необходимо выделить максимально возможное (но разумное) пространство и следить, чтобы всегда было достаточно свободного места в журнале даже для выполнения самой массовой операции обновления данных. Если база используется редко, и изменения происходят время от незначительно, то журнал можно выделить небольшим. Помните, что журнал транзакций пополняется только во время добавления, изменения и удаления данных. Во время выборки журнал не используется.
Некоторые ситуации, которые увеличивают активность журнала транзакций:
1.3.2. Добавление/удаление файла
Когда вы используете автоматическое увеличение, и база данных состоит из нескольких файлов, SQL Server использует стратегию пропорционального заполнения между всеми фалами каждой файловой группы. По мере записи данных в файловую группу, сервер записывает результат пропорционально свободному пространству в каждом файле файловой группы и после этого записывает в следующий файл. Тут есть и положительные и отрицательные моменты. Положительное видно сразу, ведь если диски подключены к разным контроллерам, то мы добиваемся параллельности, а жесткие диски являются самым слабым звеном. Но отрицательный момент может возникнуть при автоматическом увеличении файлов. Чаще всего размеры дисков разные, да и свободное пространство на них распределяется не равномерно (если на один диск установить пару игр, то свободного места будет меньше, чем на другом). Теперь, один диск будет заполнен раньше, чем другой. Нет, база данных в этом случае останется доступной, и запись будет только на один диск, но произойдет резкое падение производительности во время записи.
Допустим, что на вашем основном диске, где расположена база данных, заканчивается место на диске. В этом случае, если есть другие логические разделы или жесткие диски, можно добавить к серверу новый файл и расположить его на этом диске. Добавление чем-то похоже на определение файла при создании базы данных:
Для удаления файла из базы данных совместно с ALTER DATABASE используется ключевое слово REMOVE FILE. Если вы создали файл и поместили в него таблицу, то файл уже нельзя удалить, пока он не станет пустым, то есть, пока из файла не будут удалены все таблицы.
Нам в данной книге лишний файл не нужен, поэтому давайте удалим то, что создали. Для этого нужно указать оператор REMOVE FILE и имя удаляемого файла:
1.3.3. Добавление/удаление файловых групп
Для добавления файловой группы используется оператор ADD FILEGROUP. Например, следующий сценарий добавляет группу с именем fgNewGroup:
Теперь посмотрим, как можно добавить файл в эту группу:
Принцип такой же, как и у добавления файла, но после скобок указывается TO FILEGROUP и имя группы, в которую добавляется файл.
Файловую группу нельзя удалять, если в ней есть файл. Сначала необходимо удалить этот файл, а потом уже удалять группу. Для удаления группы используется REMOVE FILEGROUP, например:
1.3.4. Переименование базы данных
Иногда бывает необходимость переименовать базу данных. В моей практике это очень редко приходилось делать, но все же. Переименовать можно с помощью оператора MODIFY NAME. Например, следующий сценарий изменяет имя базы данных TestDatabase на MyDatabase:
При этом вы не должны быть подключены к этой базе данных, лучше всего, если подключение будет к базе данных master. Если к базе данных, которую необходимо переименовать будет подключен хоть один пользователь, то переименование не сможет быть выполнено.
Если вы попробовали выполнить этот сценарий, то верните ей старое имя TestDatabase, потому что в дальнейшем при тестировании сценариев мы будем ссылаться на него.
1.3.5. Изменение свойств базы данных
У базы данных существует множество свойств, которые мы не задавали во время создания, но которые можно изменить уже у существующей базы. К таким свойствам относятся уровень доступа, модель восстановления и т.д. Давайте рассмотрим, что и как можно изменять.
Для изменения свойства используется оператор SET. Команда будет выглядеть следующим образом:
После ALTER DATABASE указывается имя базы данных, свойства которой нужно изменить, а после оператора SET нужно указать имя свойства.
Давайте посмотрим имена свойств которые нужно подставить вместо параметра имя_свойства:
Это основные параметры, которые можно изменить. Более подробно о моделях восстановления можно узнать из файла Doc/BackupRestore.pdf на компакт диске.
Теперь давайте посмотрим на примеры использования этих свойств:
Следующий пример разрешает доступ только одному пользователю:
Доступ только только пользователям ролей db_owner, dbcreator или sysadmin:
Возвращаем нормальный многопользовательский режим:
Вывести базу данных в off-line, т.е. доступ будет запрещен всем пользователям:
Возобновить доступ к базе данных:
Перевести базу данных в режим только для чтения, любые изменения будут отклонены:
Вернуть базе данных полный доступ на запись и чтение:
По завершении транзакции (принятии или откате) все открытые курсоры будут закрываться:
Установить полную модель восстановления:
Установить модель восстановления BULK_LOGGED:
Установить простую модель восстановления:
И последнее, что нам предстоит узнать – это возможность изменения раскладки (кодировки) по умолчанию для базы данных. Для этого выполняется команда:
ALTER DATABASE (Transact-SQL) File and Filegroup Options
Modifies the files and filegroups associated with the database. Adds or removes files and filegroups from a database, and changes the attributes of a database or its files and filegroups. For other ALTER DATABASE options, see ALTER DATABASE.
For more information about the syntax conventions, see Transact-SQL Syntax Conventions.
Select a product
In the following row, select the product name you’re interested in, and only that product’s information is displayed.
* SQL Server * В
Syntax
Arguments
Specifies the file to be added, removed, or modified.
database_name Is the name of the database to be modified.
ADD FILE Adds a file to the database.
TO FILEGROUP < filegroup_name > Specifies the filegroup to which to add the specified file. To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.
ADD LOG FILE Adds a log file be added to the specified database.
REMOVE FILE logical_file_name Removes the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.
logical_file_name Is the logical name used in SQL Server when referencing the file.
Removing a database file that has FILE_SNAPSHOT backups associated with it will succeed, but any associated snapshots will not be deleted to avoid invalidating the backups referring to the database file. The file will be truncated, but will not be physically deleted in order to keep the FILE_SNAPSHOT backups intact. For more information, see SQL Server Backup and Restore with Microsoft Azure Blob Storage Service. Applies to: SQL Server ( SQL Server 2016 (13.x) and later).
MODIFY FILE Specifies the file that should be modified. Only one property can be changed at a time. NAME must always be specified in the to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
To modify the logical name of a data file or log file, specify the logical file name to be renamed in the NAME clause, and specify the new logical name for the file in the NEWNAME clause. For example:
To move a data file or log file to a new location, specify the current logical file name in the NAME clause and specify the new path and operating system file name in the FILENAME clause. For example:
When you move a full-text catalog, specify only the new path in the FILENAME clause. Do not specify the operating-system file name.
For more information, see Move Database Files.
For a FILESTREAM filegroup, NAME can be modified online. FILENAME can be modified online; however, the change does not take effect until after the container is physically relocated and the server is shutdown and then restarted.
You can set a FILESTREAM file to OFFLINE. When a FILESTREAM file is offline, its parent filegroup will be internally marked as offline; therefore, all access to FILESTREAM data within that filegroup will fail.
Controls the file properties.
NAME logical_file_name Specifies the logical name of the file.
logical_file_name Is the logical name used in an instance of SQL Server when referencing the file.
NEWNAME new_logical_file_name Specifies a new logical name for the file.
new_logical_file_name Is the name to replace the existing logical file name. The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.
FILENAME < ‘os_file_name‘ | ‘filestream_path‘ | ‘memory_optimized_data_path‘> Specifies the operating system (physical) file name.
‘ os_file_name ‘ For a standard (ROWS) filegroup, this is the path and file name that is used by the operating system when you create the file. The file must reside on the server on which SQL Server is installed. The specified path must exist before executing the ALTER DATABASE statement.
System databases cannot reside in UNC share directories.
Data files should not be put on compressed file systems unless the files are read-only secondary files, or if the database is read-only. Log files should never be put on compressed file systems.
If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be put on each raw partition.
The SIZE and FILEGROWTH properties do not apply to a FILESTREAM filegroup.
The filegroup and file ( ) must be created in the same statement.
The SIZE and FILEGROWTH properties do not apply to a MEMORY_OPTIMIZED_DATA filegroup.
For more information on memory-optimized filegroups, see The Memory Optimized Filegroup.
SIZE size Specifies the file size. SIZE does not apply to FILESTREAM filegroups.
size Is the size of the file.
When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.
When size is not supplied for the primary file, the SQL Server uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB.
The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).
SIZE cannot be set:
MAXSIZE < max_size| UNLIMITED > Specifies the maximum file size to which the file can grow.
max_size Is the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. If max_size is not specified, the file size will increase until the disk is full.
UNLIMITED Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB. There is no maximum size when this option is specified for a FILESTREAM container. It continues to grow until the disk is full.
MAXSIZE cannot be set when a UNC path is specified for the file.
FILEGROWTH growth_increment Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. FILEGROWTH does not apply to FILESTREAM filegroups.
growth_increment Is the amount of space added to the file every time new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.
A value of 0 indicates that automatic growth is set to off and no additional space is allowed.
If FILEGROWTH is not specified, the default values are:
| Version | Default values |
|---|---|
| Starting with SQL Server 2016 (13.x) | Data 64 MB. Log files 64 MB. |
| Starting with SQL Server 2005 (9.x) | Data 1 MB. Log files 10%. |
| Prior to SQL Server 2005 (9.x) | Data 10%. Log files 10%. |
FILEGROWTH cannot be set:
OFFLINE Sets the file offline and makes all objects in the filegroup inaccessible.
Use this option only when the file is corrupted and can be restored. A file set to OFFLINE can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE.
options are not available in a Contained Database.
Add, modify, or remove a filegroup from the database.
ADD FILEGROUP filegroup_name Adds a filegroup to the database.
CONTAINS FILESTREAM Specifies that the filegroup stores FILESTREAM binary large objects (BLOBs) in the file system.
Applies to: SQL Server ( SQL Server 2014 (12.x) and later)
REMOVE FILEGROUP filegroup_name Removes a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first. For more information, see «REMOVE FILE logical_file_name,» earlier in this topic.
Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASE REMOVE FILE operation to remove a FILESTREAM container will fail and return an error. See the Removing a FILESTREAM Container section later in this topic.
MODIFY FILEGROUP filegroup_name < | DEFAULT | NAME =new_filegroup_name > Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.
Sets the read-only or read/write property to the filegroup.
DEFAULT Changes the default database filegroup to filegroup_name. Only one filegroup in the database can be the default filegroup. For more information, see Database Files and Filegroups.
NAME = new_filegroup_name Changes the filegroup name to the new_filegroup_name.
AUTOGROW_SINGLE_FILE Applies to: SQL Server ( SQL Server 2016 (13.x) and later)
When a file in the filegroup meets the autogrow threshold, only that file grows. This is the default.
Applies to: SQL Server ( SQL Server 2016 (13.x) and later)
When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.
This is the default value for TempDB.
Sets the read-only or read/write property to the filegroup.
READ_ONLY | READONLY Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Because a read-only database does not allow data modifications:
READ_WRITE | READWRITE Specifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
The keyword READWRITE will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.
The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX function.
Remarks
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a BACKUP statement is running.
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
Starting with SQL Server 2005 (9.x), the state of a database file (for example, online or offline), is maintained independently from the state of the database. For more information, see File States.
SIZE, MAXSIZE, and FILEGROWTH parameters cannot be set when a UNC path is specified for the file.
SIZE and FILEGROWTH parameters cannot be set for memory optimized filegroups.
The keyword READONLY will be removed in a future version of MicrosoftSQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.
The keyword READWRITE will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.
Moving Files
You can move system or user-defined data and log files by specifying the new location in FILENAME. This may be useful in the following scenarios:
For more information, see Move Database Files.
Initializing Files
By default, data and log files are initialized by filling the files with zeros when you perform one of the following operations:
Data files can be initialized instantaneously. This enables for fast execution of these file operations. For more information, see Database File Initialization.
Removing a FILESTREAM Container
Even though FILESTREAM container may have been emptied using the «DBCC SHRINKFILE» operation, the database may still need to maintain references to the deleted files for various system maintenance reasons. sp_filestream_force_garbage_collection will run the FILESTREAM Garbage Collector to remove these files when it is safe to do so. Unless the FILESTREAM Garbage Collector has removed all the files from a FILESTREAM container, the ALTER DATABASE REMOVE FILE operation will fail to remove a FILESTREAM container and will return an error. The following process is recommended to remove a FILESTREAM container.
Examples
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
B. Adding a filegroup with two files to a database
The following example creates the filegroup Test1FG1 in the AdventureWorks2012 database and adds two 5-MB files to the filegroup.
C. Adding two log files to a database
The following example adds two 5-MB log files to the AdventureWorks2012 database.
D. Removing a file from a database
The following example removes one of the files added in example B.
E. Modifying a file
The following example increases the size of one of the files added in example B. The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.
This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.
F. Moving a file to a new location
The following example moves the Test1dat2 file created in example A to a new directory.
You must physically move the file to the new directory before running this example. Afterward, stop and start the instance of SQL Server or take the AdventureWorks2012 database OFFLINE and then ONLINE to implement the change.
G. Moving tempdb to a new location
The following example moves tempdb from its current location on the disk to another disk location. Because tempdb is re-created each time the MSSQLSERVER service is started, you do not have to physically move the data and log files. The files are created when the service is restarted in step 3. Until the service is restarted, tempdb continues to function in its existing location.
Determine the logical file names of the tempdb database and their current location on disk.
Stop and restart the instance of SQL Server.
Verify the file change.
Delete the tempdb.mdf and templog.ldf files from their original location.
H. Making a filegroup the default
The following example makes the Test1FG1 filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note that PRIMARY must be delimited by brackets or quotation marks.
I. Adding a Filegroup Using ALTER DATABASE
The following example adds a FILEGROUP that contains the FILESTREAM clause to the FileStreamPhotoDB database.
The following example adds a FILEGROUP that contains the MEMORY_OPTIMIZED_DATA clause to the xtp_db database. The filegroup stores memory optimized data.
J. Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow
The following example generates the required ALTER DATABASE statements to modify read-write filegroups with the AUTOGROW_ALL_FILES setting.
See Also
* SQL Managed Instance *
В
Azure SQL Managed Instance
Use this statement with a database in Azure SQL Managed Instance.
Syntax for Azure SQL Managed Instance
Arguments
Specifies the file to be added, removed, or modified.
database_name Is the name of the database to be modified.
ADD FILE Adds a file to the database.
TO FILEGROUP < filegroup_name > Specifies the filegroup to which to add the specified file. To display the current filegroups and which filegroup is the current default, use the sys.filegroups catalog view.
REMOVE FILE logical_file_name Removes the logical file description from an instance of SQL Server and deletes the physical file. The file cannot be removed unless it is empty.
logical_file_name Is the logical name used in SQL Server when referencing the file.
MODIFY FILE Specifies the file that should be modified. Only one property can be changed at a time. NAME must always be specified in the to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size.
Controls the file properties.
NAME logical_file_name Specifies the logical name of the file.
logical_file_name Is the logical name used in an instance of SQL Server when referencing the file.
NEWNAME new_logical_file_name Specifies a new logical name for the file.
new_logical_file_name Is the name to replace the existing logical file name. The name must be unique within the database and comply with the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier.
SIZE size Specifies the file size.
size Is the size of the file.
When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file, and must be larger than the current file size.
When size is not supplied for the primary file, the SQL Server uses the size of the primary file in the model database. When a secondary data file or log file is specified but size is not specified for the file, the Database Engine makes the file 1 MB.
The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. To specify a fraction of a megabyte, convert the value to kilobytes by multiplying the number by 1024. For example, specify 1536 KB instead of 1.5 MB (1.5 x 1024 = 1536).
MAXSIZE < max_size| UNLIMITED > Specifies the maximum file size to which the file can grow.
max_size Is the maximum file size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number and do not include a decimal. If max_size is not specified, the file size will increase until the disk is full.
UNLIMITED Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
FILEGROWTH growth_increment Specifies the automatic growth increment of the file. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.
growth_increment Is the amount of space added to the file every time new space is required.
The value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.
A value of 0 indicates that automatic growth is set to off and no additional space is allowed.
If FILEGROWTH is not specified, the default values are:
Add, modify, or remove a filegroup from the database.
ADD FILEGROUP filegroup_name Adds a filegroup to the database.
The following example creates a filegroup that is added to a database named sql_db_mi, and adds a file to the filegroup.
REMOVE FILEGROUP filegroup_name Removes a filegroup from the database. The filegroup cannot be removed unless it is empty. Remove all files from the filegroup first. For more information, see «REMOVE FILE logical_file_name,» earlier in this topic.
MODIFY FILEGROUP filegroup_name < | DEFAULT | NAME =new_filegroup_name > Modifies the filegroup by setting the status to READ_ONLY or READ_WRITE, making the filegroup the default filegroup for the database, or changing the filegroup name.
Sets the read-only or read/write property to the filegroup.
DEFAULT Changes the default database filegroup to filegroup_name. Only one filegroup in the database can be the default filegroup. For more information, see Database Files and Filegroups.
NAME = new_filegroup_name Changes the filegroup name to the new_filegroup_name.
When a file in the filegroup meets the autogrow threshold, only that file grows. This is the default.
When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow.
Sets the read-only or read/write property to the filegroup.
READ_ONLY | READONLY Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
Because a read-only database does not allow data modifications:
The keyword READONLY will be removed in a future version of MicrosoftSQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.
READ_WRITE | READWRITE Specifies the group is READ_WRITE. Updates are enabled for the objects in the filegroup. To change this state, you must have exclusive access to the database. For more information, see the SINGLE_USER clause.
The keyword READWRITE will be removed in a future version of MicrosoftSQL Server. Avoid using READWRITE in new development work, and plan to modify applications that currently use READWRITE to use READ_WRITE instead.
The status of these options can be determined by examining the is_read_only column in the sys.databases catalog view or the Updateability property of the DATABASEPROPERTYEX function.
Remarks
To decrease the size of a database, use DBCC SHRINKDATABASE.
You cannot add or remove a file while a BACKUP statement is running.
A maximum of 32,767 files and 32,767 filegroups can be specified for each database.
Examples
A. Adding a file to a database
The following example adds a 5-MB data file to the AdventureWorks2012 database.
B. Adding a filegroup with two files to a database
The following example creates the filegroup Test1FG1 in the AdventureWorks2012 database and adds two 5-MB files to the filegroup.
C. Removing a file from a database
The following example removes one of the files added in example B.
D. Modifying a file
The following example increases the size of one of the files added in example B. The ALTER DATABASE with MODIFY FILE command can only make a file size bigger, so if you need to make the file size smaller you need to use DBCC SHRINKFILE.
This example shrinks the size of a data file to 100 MB, and then specifies the size at that amount.
E. Making a filegroup the default
The following example makes the Test1FG1 filegroup created in example B the default filegroup. Then, the default filegroup is reset to the PRIMARY filegroup. Note that PRIMARY must be delimited by brackets or quotation marks.
F. Adding a Filegroup Using ALTER DATABASE
The following example adds a FILEGROUP to the MyDB database.
G. Change filegroup so that when a file in the filegroup meets the autogrow threshold, all files in the filegroup grow
The following example generates the required ALTER DATABASE statements to modify read-write filegroups with the AUTOGROW_ALL_FILES setting.