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

Здесь надо пояснить ситуацию с файлами данных и файловыми группами в SQL Server. База данных Microsoft SQL Server состоит из по крайней мере одного файла данных и одного файла журнала транзакций. Прежде чем мы перейдем к основной теме статьи, я хочу установить между базами данных и файлами журнала транзакций соотношение 1:1. Не создавайте в своих базах данных многочисленных файлов журнала транзакций.

Надеюсь, я выразился достаточно ясно.

Совершенно нормально (и привычно, если не рекомендуемо) иметь несколько файлов данных, связанных с базой данных. Файлы данных могут располагаться на одном или нескольких логических томах, которые могут быть одним или различными физическими томами. Создаваемый файл данных назначается файловой группе. Файловая группа — всего лишь логическая конструкция для хранения данных, к которой можно приписать один или несколько файлов данных. В базе данных может существовать несколько файловых групп.

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

Без паники!

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

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

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

Первое, что следует сделать: создать очень маленькую тестовую базу данных. Я уже подготовил базу данных с двумя файлами данных. Оба они назначены файловой группе PRIMARY, которая создается по умолчанию при формировании новой базы данных (см. листинг 1).

Затем создается таблица в базе данных (в файловой группе по умолчанию, PRIMARY), как показано в листинге 2.

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

 

Состояние файлов перед загрузкой данных
Экран 1. Состояние файлов перед загрузкой данных

Обратите внимание на столбец size_mb и столбец mb_before_full здесь и в конце статьи. Сначала загрузим в таблицу достаточное количество данных, чтобы увидеть пропорциональное заполнение файлов (см. листинг 4).

Чтобы загрузить данные, я применяю малоизвестный прием с использованием кода пакета GO. Если поместить численное значение после GO, то пакет, заключенный между командами GO, будет выполнен указанное число раз. В данном случае команда INSERT будет выполнена 10 000 раз. Достаточно, чтобы показать увеличение данной таблицы. Мы сможем увидеть увеличение файлов данных, связанных с файловой группой PRIMARY (см. экран 2).

 

Состояние файлов после вставки 10 000 строк
Экран 2. Состояние файлов после вставки 10 000 строк

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

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

Листинг 1. Создание тестовой базы данных
CREATE DATABASE [Fill_Er_Up] ON  PRIMARY
        (
                NAME = N'Fill_Er_Up',
                FILENAME = N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\Fill_Er_Up.mdf',
                SIZE = 4MB,
                FILEGROWTH = 1MB
        ),
        (
                NAME = N'Fill_Er_Up_2',
                FILENAME = N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\Fill_Er_Up_2.ndf',
                SIZE = 1MB,
                MAXSIZE = 1MB,
                FILEGROWTH = 1MB
        )
LOG ON
        (
                NAME = N'Fill_Er_Up_log',
                FILENAME = N'C:\Data\MSSQL12.MSSQLSERVER\MSSQL\DATA\Fill_Er_Up_log.ldf',
                SIZE = 1MB,
                FILEGROWTH = 10240KB
        )
GO
ALTER DATABASE [Fill_Er_Up] SET RECOVERY SIMPLE
GO
Листинг 2. Создание таблицы в базе данных
CREATE TABLE [Fill_Er_Up].dbo.Filling
        (
        Beefy char(5120) NOT NULL
        )  ON [PRIMARY]
GO
Листинг 3. Получение моментального снимка состояния файлов
USE [Fill_Er_Up];
GO
SELECT
        [file_id],
        [name] AS [logical_name],
        physical_name,
        type_desc,
        CAST(CAST(size AS BIGINT)*8/1024 AS bigint) AS [size_mb],
        CASE is_percent_growth
                WHEN 0 THEN CAST((growth*8/1024) AS varchar(30)) +' Mb'
                ELSE CAST(growth AS varchar(30)) + ' %'
        END AS growth,
        CASE max_size
                WHEN -1 THEN 'Unlimited'
                ELSE CAST(CAST(max_size AS BIGINT)*8/1024 AS varchar(30)) + ' Mb'
        END AS max_size,
        CASE max_size
                WHEN -1 THEN NULL
                ELSE (CAST(max_size AS BIGINT)*8/1024) - (CAST(size AS BIGINT)*8/1024)
                  --+ ' Mb'
        END AS mb_before_full,
FROM sys.master_files
WHERE database_id = DB_ID('Fill_Er_Up')
ORDER BY 9 ASC
GO
Листинг 4. Загрузка данных в таблицу
INSERT INTO [Fill_Er_Up].dbo.Filling(Beefy)
VALUES  (‘halloween candy’)
GO 10000