Недавно ко мне обратился пользователь, обеспокоенный приближающимся автоматическим расширением на томе, на котором уже не хватало места. При наличии файла данных размером 5 Тбайт оставшееся свободное пространство измерялось лишь мегабайтами. Это была не просто потенциальная опасность, а неизбежность.
Здесь надо пояснить ситуацию с файлами данных и файловыми группами в SQL Server. База данных Microsoft SQL Server состоит из по крайней мере одного файла данных и одного файла журнала транзакций. Прежде чем мы перейдем к основной теме статьи, я хочу установить между базами данных и файлами журнала транзакций соотношение 1:1. Не создавайте в своих базах данных многочисленных файлов журнала транзакций.
Надеюсь, я выразился достаточно ясно.
Совершенно нормально (и привычно, если не рекомендуемо) иметь несколько файлов данных, связанных с базой данных. Файлы данных могут располагаться на одном или нескольких логических томах, которые могут быть одним или различными физическими томами. Создаваемый файл данных назначается файловой группе. Файловая группа — всего лишь логическая конструкция для хранения данных, к которой можно приписать один или несколько файлов данных. В базе данных может существовать несколько файловых групп.
В файловых группах SQL Server применяется подход пропорционального заполнения. Это означает, что по умолчанию данные записываются в файловую группу пропорционально имеющемуся размеру свободного пространства. Однако так происходит не всегда, и на одном полезном исключении основано предложенное мною решение.
Без паники!
С этими словами я обратился к пользователю, заявившему об опасности останова производственной базы данных из-за заполнения файла данных. Я спросил, подключен ли сервер, на котором размещена база данных, к каким-либо другим томам. Выяснилось, что подключение было, и мы приготовились быстро устранить проблему. Я просто посоветовал создать другой файл на доступном томе и обязательно назначить его той же файловой группе.
Вы можете возразить, что при пропорциональном заполнении часть данных все равно будет записываться в старый файл.
Однако на данном этапе вступает в действие вторая часть решения: отключить автоматическое расширение на существующем файле данных после добавления нового файла. Таким образом обеспечивается запись данных на диск, но не в старый, полный файл. Посмотрим, как выглядит этот метод.
Первое, что следует сделать: создать очень маленькую тестовую базу данных. Я уже подготовил базу данных с двумя файлами данных. Оба они назначены файловой группе PRIMARY, которая создается по умолчанию при формировании новой базы данных (см. листинг 1).
Затем создается таблица в базе данных (в файловой группе по умолчанию, PRIMARY), как показано в листинге 2.
На данном этапе у меня есть все необходимое для тестирования, но, прежде чем начать заполнять таблицу данными, нужно получить моментальные снимки состояний файлов до и после и их соответствующие размеры. Для этого используется приведенный в листинге 3 программный код, а результаты при пустой таблице выглядят так, как показано на экране 1.
.jpg) |
| Экран 1. Состояние файлов перед загрузкой данных |
Обратите внимание на столбец size_mb и столбец mb_before_full здесь и в конце статьи. Сначала загрузим в таблицу достаточное количество данных, чтобы увидеть пропорциональное заполнение файлов (см. листинг 4).
Чтобы загрузить данные, я применяю малоизвестный прием с использованием кода пакета GO. Если поместить численное значение после GO, то пакет, заключенный между командами GO, будет выполнен указанное число раз. В данном случае команда INSERT будет выполнена 10 000 раз. Достаточно, чтобы показать увеличение данной таблицы. Мы сможем увидеть увеличение файлов данных, связанных с файловой группой PRIMARY (см. экран 2).
.jpg) |
| Экран 2. Состояние файлов после вставки 10 000 строк |
Вспомните миф о пропорциональном заполнении: размер небольшого файла данных не изменился. Все данные были добавлены в файл данных, для которого не устанавливался максимальный размер.
Пользователь, который обратился ко мне, остался доволен, что не ему пришлось объясняться с руководством относительно причин несвоевременного обслуживания базы данных. Кроме того, он узнал о возможностях разделения ввода-вывода и масштабирования базы данных с учетом роста. И самый главный усвоенный урок: ключевой принцип успешного администратора баз данных компании — без паники!
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
CREATE TABLE [Fill_Er_Up].dbo.Filling ( Beefy char(5120) NOT NULL ) ON [PRIMARY] GO
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
INSERT INTO [Fill_Er_Up].dbo.Filling(Beefy) VALUES (‘halloween candy’) GO 10000