Как формируется размер файла в ms sql server

Обновлено: 21.11.2024

У меня есть Microsoft SQL Server 2008, который используется для SharePoint. Я хотел бы контролировать размер базы данных. Как я могу это сделать?

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

Заранее спасибо

Создано 5 апреля 2011 г., 15:37:21, Джон Д. Джонсон (2) ● 1

Последнее изменение: 9 декабря 2020 г., 12:22:43, автор Brandy Greger [служба поддержки Paessler]

Лучший ответ

Эта статья применяется с PRTG 21

PRTG Network Monitor поставляется со встроенным сенсором Microsoft SQL v2. Он выполняет определенный запрос, показывает несколько типов времени выполнения запроса, количество адресуемых строк и определенные значения отслеживаемой базы данных Microsoft SQL. Обратитесь к руководству для получения более подробной информации.

Если вы хотите отслеживать текущий размер файла, используемое пространство и свободное пространство (в байтах и процентах), вам необходимо предоставить приведенный ниже запрос для датчика Microsoft SQL v2.

Нажмите, чтобы увеличить.

Выполните следующие действия, чтобы настроить сенсор Microsoft SQL v2:

Требования

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

Запрос Microsoft SQL

История версий

Дата	Версия	Примечания
30 ноября 2016 г.	1.0	Первоначальный выпуск

Настройка датчика

Сохраните указанный выше запрос как PRTG-DatabaseSize.sql в папке %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
В вашей установке PRTG перейдите к устройству Microsoft SQL, которое вы хотите отслеживать, и добавьте новый датчик Microsoft SQL v2.
В настройках датчика настройте датчик следующим образом:

Сохраните настройки и начните мониторинг. Если вы обнаружите какие-либо ошибки, не стесняйтесь поделиться ими.

Примечание. Запрос предоставляется как есть и может работать или не работать с вашей установкой. Мы не можем предоставить всестороннюю техническую поддержку для пользовательских скриптов.

Последнее изменение: 14 января 2022 г., 10:36:56, автор: Frank Hadasch [служба поддержки Paessler]

Размер базы данных можно узнать с помощью системной хранимой процедуры sp_helpdb, включенной в SQL 2008 и 2005 (насчет 2000 не уверен). Мы не можем использовать эту хранимую процедуру напрямую с PRTG, так как она возвращает множество строк и полей, поэтому нам нужно сделать следующее:

Создайте новую хранимую процедуру, используя следующий код

Добавить новый датчик MSQL

На своем сервере PRTG добавьте новый датчик MSQL. Заполните необходимые поля и в поле "Выражение SQL" введите

На вкладке каналов выберите канал значения и введите МБ в качестве единицы измерения.

Последнее изменение: 28 апреля 2011 г., 15:39:54, Торстен Линднер [служба поддержки Paessler]

Я нашел более простой способ выполнить этот запрос после того, как немного поборолся. В поле SQL-запроса я использовал

выберите (convert(float,Size)*(1024*8))/1024/1024 из DatabaseName.dbo.sysfiles, где GroupId = 1

Просто замените "DatabaseName" на базу данных, которую вы хотите запросить.

Спасибо за ответ

Создано 5 апреля 2011 г., 20:15:59, Джон Д. Джонсон (2) ● 1

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

Вы правы, мне нужно сесть и решить, что именно я хочу отслеживать. Спасибо, что указали на это.

Создано 6 апреля 2011 г., 14:03:28, Джон Д. Джонсон (2) ● 1

Если я использую вашу процедуру и выражение sql, я получаю следующее сообщение об ошибке: "Не удалось выполнить запрос: Ошибка сервера базы данных: Zeichenfolgenoder Binärdaten würden abgeschnitten". (я не уверен, что мой перевод будет таким же, как ваш, так что это немецкий)

Создано AGriese 28 апреля 2011 г., 15:01:24 (0) ● 1

В этом случае системная хранимая процедура sp_helpdb возвращает «большие» данные, чем временная таблица @SpaceUsed, для которой настроена.

Запустите процедуру sp_helpdb в новом окне запроса, посмотрите результат и соответствующим образом измените размеры полей временной таблицы.

После корректировки размеров полей (имя до 60 цифр!) ошибка устранена. Но поле значения равно «0» (нет ошибки, но ничего не происходит). Когда я запускаю sp_helpdp, все выглядит нормально, и все значения соответствуют размеру файлов базы данных.

Создано 4 мая 2011 г., 13:43:35, автор: AGriese (0) ● 1

Создано 9 апреля 2013 г., 17:59:09, автор: Michael Hogan (0) ● 1

Только что проверил ответ из первого ответа на SQL Server 2008, и он не работает. После некоторой RTFM'ации измените некоторые типы полей в SP. Рабочий код ниже:

Создано 23 апреля 2013 г., 10:44:07 Геннадием (60) ● 2 ● 1

Примечание. Эта статья устарела! См. ответ, опубликованный 30 ноября 2016 г.

Эта статья относится к PRTG Network Monitor 13.2 или более поздней версии

С помощью PRTG вы можете получить размер базы данных MS SQL с помощью встроенного сенсора Microsoft SQL. Можно запросить общий размер вашей базы данных, используемое пространство и свободное пространство, предоставив соответствующие SQL-запросы в настройках сенсора Microsoft SQL.

Что нужно сделать

Для каждого из атрибутов размера, используемого и свободного места добавьте датчик Microsoft SQL в PRTG. Укажите учетные данные для своей базы данных в разделе «База данных» в диалоговом окне добавления датчика и вставьте SQL-запросы, как указано ниже. Вы можете создать все три датчика или только те, которые вам нужны, в зависимости от ваших требований.

Отслеживание размера вашей БД. Создайте датчик с именем DB-FileSize. В разделе SQL-Expression введите следующий запрос: выберите convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB from dbo.sysfiles a, где fileid = 1;

Наблюдение за используемым пространством вашей БД: создайте датчик под названием DB-SpaceUsed. В разделе SQL-Expression введите следующий запрос: выберите convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB from dbo.sysfiles a где fileid = 1 ;

Наблюдение за свободным пространством вашей БД: создайте датчик с именем DB-SpaceFree. В разделе SQL-Expression введите следующий запрос: выберите convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB from dbo. sysfiles a, где идентификатор файла = 1;

Для каждого датчика выберите Обработать числовой результат в разделе Постобработка в диалоговом окне добавления датчика.

Отображаемый результат в PRTG соответствует размеру базы данных, соответственно использованию, в мегабайтах.

Примечания

Как упоминалось выше, достаточно создать, например, только датчик DB-UsedSpace для отслеживания размера вашей базы данных. Этот датчик извлекает только количество данных (возможно, релевантное для дампа БД).

Однако учтите, что в зависимости от настройки вашей БД для автоматического увеличения значение физического размера базы данных также может иметь значение, например, в отношении резервного копирования файлов. Представьте, что у вас есть база данных размером 1 ТБ, а значение по умолчанию для автоматического увеличения составляет 10%. Тогда используемое дисковое пространство увеличится на 100 ГБ сразу за счет увеличения файлов при достижении заданного порога. Таким образом, мы дополнительно рекомендуем добавить датчик DB-SpaceFree (конечно, в зависимости от ваших потребностей).

Примечание. В отличие от решения с хранимыми процедурами, опубликованного 5 апреля 2011 г., описанное выше решение работает без агента.

Можете ли вы сказать мне, как подключить датчик SQL V2?

Создано 10 октября 2016 г., 6:31:51, Torsten Schöne (0)

Это очень похоже на датчик SQL v2, оператор SQL теперь должен быть предоставлен в файле.

Я пытался использовать датчик SQL v2, но безуспешно. Я использую файл запроса SQL:

выберите convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB из dbo.sysfiles a, где fileid = 1;

Не могли бы вы дать больше пояснений?

Создано italert (0) 11 октября 2016 г., 3:14:08

Обратите внимание, что наша поддержка не распространяется на фактическую инструкцию SQL. Если вы получили сообщение об ошибке, связанное с PRTG, свяжитесь с [email protected]. Пожалуйста, приложите скриншоты рассматриваемого датчика. Полезные скриншоты охватывают вкладку «Обзор», вкладку «Настройки» и вкладку «Журналы».

Эта статья применяется с PRTG 21

Нажмите, чтобы увеличить.

Выполните следующие действия, чтобы настроить сенсор Microsoft SQL v2:

Требования

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

Запрос Microsoft SQL

История версий

Дата	Версия	Примечания
30 ноября 2016 г.	1.0	Первоначальный выпуск

Настройка датчика

Сохраните указанный выше запрос как PRTG-DatabaseSize.sql в папке %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
В вашей установке PRTG перейдите к устройству Microsoft SQL, которое вы хотите отслеживать, и добавьте новый датчик Microsoft SQL v2.
В настройках датчика настройте датчик следующим образом:

Последнее изменение: 14 января 2022 г., 10:36:56, автор: Frank Hadasch [служба поддержки Paessler]

Я попробовал метод Мартины здесь, но получаю сообщение об ошибке: Столбец "FILESIZE" не найден в возвращенных 1 таблицах данных.

Хотя при выполнении этого запроса я вижу в списке столбец "FILESIZE". Что не так?

Создано tomer (0) 25 декабря 2016 г., 14:27:39 ● 1

Убедитесь, что вы правильно установили все параметры и настройки, как описано в руководстве Мартины. Если это установлено правильно, активируйте «Записать результат датчика на диск (имя файла: «Результат датчика [ID].txt»)» в настройках датчика и покажите нам журналы, расположенные на соответствующем удаленном датчике в «C:\ProgramData». \Paessler\PRTG Network Monitor\Журналы (Датчики)\".

Я начал использовать этот скрипт в PRTG, и он работает для получения информации о размере базы данных, вывод в mssms показывает информацию о размере базы данных и размере журнала. Как получить информацию об обеих строках в PRTG? мне нужно отслеживать размер журнала и базы данных, и я не хочу создавать для этого еще один датчик.

log xyzdb_log null e: \ log \ xyzdblog.ldf 158976.00 383.77 158592.23 99.77 158592.23 99.76 к 2048 мб - ограничен 2048 ГБ строки XYXDB Primary D: \ DATA \ XYZDB.MDF 1261568.00 1217775.63 43792.38 3.47 на 4096 MB - неограниченный

Создано 17 мая 2019 г., 9:14:37, автор: Gilles Mongeon (0)

С небольшими изменениями должно быть возможно выдавать оба результата в столбцах и обрабатывать их в SQL Sensor.

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

Однако - при этом вы столкнетесь с некоторыми проблемами - у вас может быть несколько файлов, а не только два, для каждой базы данных, например. несколько файлов журнала.. это может быть довольно сложно..

Как бы то ни было, я люблю вызовы. посмотрите на скрипт ниже - думаю, он вам поможет.

Этот первый оператор USE [master] — вы можете заменить его на USE @PRTG (я думаю, что @prtg был вводом параметра для SQL, но я могу ошибаться) и внедрить ИМЯ БАЗЫ ДАННЫХ в качестве параметра в сценарий SQL непосредственно из ПРТГ.

вы получаете следующее:

< th>LOG_FILESIZE

DATABASENAME	DATA_FILESIZE	DATA_USEDSPACE	DATA_FREESPACE	DATA_FREESPACE_PERCENT	LOG_USEDSPACE	LOG_FREESPACE	LOG_FREESPACE_PERCENT
1	мастер	4194304,00	3019898,88	1184890,88	28,13	786432,00	398458,88	398458,88	50,00

Надеюсь, это поможет. С уважением

Создано 21 мая 2019 г., 14:03:58, автор Florian Rossmark (4 369) ● 4 ● 2

Могу ли я создать запрос не к конкретной базе данных, а только к SQL-серверу, который будет показывать только состояние баз данных? Например, я знаю состояние хранилища основной базы данных любых других баз данных на сервере.

Создано Василием (48) 25 июля 2019 г., 6:19:26 ● 1

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

Пожалуйста, проверьте это

Создано 9 октября 2021 г., 11:23:05, автор: coreyalex (0)

Пожалуйста, войдите или зарегистрируйтесь, чтобы ввести свой ответ.

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

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

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

Обзор виртуального файла журнала

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

При записи новой записи в журнал SQL Server Engine будет использовать существующие неиспользуемые файлы VLF SQL Server. Если все существующие виртуальные файлы журнала SQL активны и требуется расширение файла журнала транзакций, будут созданы новые файлы VLF SQL Server на основе размера записываемого журнала транзакций.

Размер файла журнала транзакций

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

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

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

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

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

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

Размер файла виртуального журнала

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

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

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

Виртуальный файл журнала и производительность

Наличие большого количества виртуальных файлов журнала SQL в файле журнала транзакций может привести к проблемам с производительностью. Механизм SQL Server Engine старается поддерживать как можно меньшее количество VLF SQL Server, поскольку он может более эффективно обрабатывать меньшее количество этих VLF, особенно в условиях сбоя и восстановления. Когда служба SQL Server перезапускается, база данных будет находиться в состоянии восстановления, в котором SQL Server Engine считывает виртуальные файлы журнала SQL, чтобы поддерживать базы данных в согласованном состоянии, путем повтора совершенных транзакций и отката неудачных транзакций. . Если файл журнала транзакций содержит большое количество файлов VLF SQL Server, механизму SQL Server Engine потребуется много времени для восстановления переходов базы данных.

Большое количество виртуальных файлов журнала SQL является результатом небольшого начального размера и небольшого объема файла журнала транзакций с автоматическим увеличением. В этом случае файл журнала транзакций будет очень часто увеличиваться небольшими порциями каждый раз, когда требуется дополнительное пространство. В результате файл журнала транзакций будет состоять из большого количества небольших файлов VLF SQL Server, что приведет к снижению производительности. Чтобы решить эту проблему, настоятельно рекомендуется установить начальный размер файла журнала транзакций на достаточно большой размер, который, по вашему мнению, соответствует росту журнала транзакций вашей базы данных с большим значением автоматического роста, не менее 1 ГБ для баз данных с большим количеством транзакций.

Мониторинг файлов виртуального журнала SQL

Для активного администратора базы данных очень важной задачей, которую, к сожалению, многие упускают из виду, является отслеживание количества VLF SQL Server в ваших базах данных и обеспечение того, чтобы оно находилось в приемлемом диапазоне, например менее 50. VLF на каждые 10 ГБ, что не окажет негативного влияния на производительность ваших баз данных.

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

В этой статье мы обсудим фактический размер файла базы данных и свободное место в размере файла базы данных с помощью скрипта мониторинга и решений для сжатия. При создании базы данных начальный размер файлов базы данных (файл данных и файл журнала) может быть определен нами с помощью параметров Autogrowth и MAXSIZE. Начальный размер будет таким же, как у базы данных модели, если пользователь случайно забыл определить параметр начального размера при создании базы данных или сделал то же самое намеренно.

Сначала размер базы данных можно настроить с помощью параметра SIZE, а максимальное пространство, которое может занимать база данных, можно определить с помощью параметра MAXSIZE. Насколько быстро файл базы данных может достичь максимального размера, можно определить с помощью параметра FILEGROWTH. Нам легче определить параметр FILEGROWTH. Это оставляет нам два варианта: либо по конкретному абсолютному значению, либо по процентам, при этом в обоих случаях мы должны определить значение в формате МБ. Не будет никаких ограничений на рост файла базы данных, если мы не позаботимся об определении параметра MAXSIZE, так как значение по умолчанию — UNLIMITED.

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

Данные таблицы гигантского размера будут распределены по нескольким файлам на более крупной платформе, чтобы уменьшить количество конфликтов на диске. Чтобы повысить производительность ввода-вывода, SQL Server поддерживает несколько файловых групп со вторичными файлами, клиентские данные и индекс могут храниться во вторичной файловой группе.Та же тактика может быть применена к файлу журнала базы данных, в котором пользователь может создавать несколько файлов с одной базой данных, и рекомендуется хранить файлы журнала на другом диске, отличном от основного файла данных (mdf).

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

Свободное место в файле данных или файле журнала
Файл данных журнала тяжелых транзакций
Увеличение размера файла журнала без его уменьшения из-за зависания тяжелой транзакции

Размер файла журнала базы данных будет постоянно увеличиваться до тех пор, пока не произойдет следующее резервное копирование транзакции, если модель восстановления базы данных не является простой и резервное копирование транзакций или доставка журналов не настроены. Однако при создании резервной копии журнала обязательно будет свободное место. Например, рассмотрите возможность создания резервной копии журнала для файла размером 1024 МБ. Что произойдет после этого, так это то, что все журналы будут удалены внутри файла, и в результате этого будет много места.

Чтобы сжать файл в SQL Server, мы всегда используем команду DBCC SHRINKFILE(). Эта команда DBCC SHRINKFILE() освободит свободное место для входного параметра.

DBCC SHRINKFILE ([FileName/FileID], [EMPTYFILE/[nMB (Сумма для сжатия), NOTRUNCATE/TRUNCATEONLY]])

Файл будет уменьшен либо по имени файла, либо по его идентификатору с помощью приведенной выше команды. Размер суммы сжатия будет считаться указанным в команде в мегабайтах. Теперь какой может быть сумма для сжатия в DBCC SHRINKFILE()?

Давайте обсудим на примере. На приведенном выше изображении база данных AdventureWorks имеет один файл DATA AdventureWorks2016CTP3_Data с пространством в файле на 500 МБ больше, а также имеет свободное пространство 208,8 МБ. Где файл журнала AdventureWorks2016CTP3_Log существует с размером 600 МБ, а свободное место в файле составляет 362,9 МБ. Приведенный ниже сценарий может быть использован пользователем для получения свободного места для файлов базы данных.

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

Узнайте из 20-летнего опыта о том, как превратить наихудшие практики SQL Server, такие как повреждение базы данных, безопасность, масштабирование, мониторинг, управление затратами в облаке и мониторинг агента SQL, в лучшие практики для правильного управления средой вашей базы данных.

Проблема

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

Однако вы заметили, что при сбросе старых записей общим объемом 20 ГБ размер вашего файла на жестком диске не изменился. Допустим, размер файла для таблицы был 60 ГБ, и вы удалили 20 ГБ старых записей. Вы ожидаете, что размер файла уменьшится до 40 ГБ, но вместо этого он останется на уровне 60 ГБ.

Это также верно, если вы «усекаете» таблицу, размер файла останется таким же, каким он был до того, как вы его усекли.

Решение

При создании базы данных вы изначально устанавливаете базовый размер для файлов mdf и ldf или полагаетесь на значения по умолчанию, установленные SQL Server. В любом случае это всего лишь минимальная отправная точка, и размер файла будет увеличиваться по мере добавления данных в таблицы. После удаления большого количества данных из базы данных вы заметите, что размеры файлов mdf и ldf остаются такими же, какими они были до удаления данных. Это связано с тем, что SQL Server сохраняет этот максимальный размер файла в качестве зарезервированного пространства. Если вам абсолютно необходимо восстановить это зарезервированное пространство, вы можете использовать SHRINKFILE и SHRINKDATABASE, чтобы вернуть его. При желании вы можете позволить SQL Server сохранить зарезервированное пространство для использования, когда количество записей в базе данных снова возрастет.

Меры предосторожности

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

Что нужно знать:

Вы не можете сжать базу данных, для которой в данный момент выполняется резервное копирование, а также нельзя создать резервную копию базы данных, которая в данный момент находится в процессе сжатия.
База данных не может быть уменьшена до размера меньше минимального или исходного размера базы данных. Например, если база данных была создана с начальным размером 8 МБ, но увеличилась до 4 ГБ, наименьший размер, до которого вы можете сжать базу данных, — исходный размер 8 МБ.
Чтобы использовать функцию SHRINK, у вас должно быть разрешение на фиксированную роль сервера "sysadmin" или роль базы данных "db_owner". Пользователь с правами "только чтение" не может использовать функции "SHRINKFILE" или "SHRINKDATABASE".

ВНИМАНИЕ:

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

Создание тестовой среды

В этом примере мы создадим тестовую базу данных с именем SampleDataBase для работы.

Получить размер баз данных

Прежде чем мы начнем вставлять данные, нам нужно посмотреть и задокументировать размер файлов "mdf" и "ldf" для базы данных. Это даст нам основу для сравнения. У нас есть несколько способов сделать это, я перечислил два из них для вашего удобства.

Вариант 1. Использование T-SQL

Запустите этот простой скрипт, чтобы узнать начальный или текущий размер файла "SampleDataBase".

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

Вариант 2. Использование диспетчера файлов Windows (GUI)

Перейдите к папке, содержащей файлы "mdf" и "ldf", и сделайте снимок экрана, как на изображении 001 ниже. По умолчанию это будет путь к папке, в которой SQL Server хранит ваши файлы данных. В зависимости от конкретной конфигурации и версии SQL Server.

Вот где он находится на моей машине: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\DATA\

Или, если вы помещаете файлы на другой диск во время создания, просто перейдите к диску/папке, куда вы их поместили. На снимке экрана ниже мои файлы данных находятся на диске E:

Ниже показан снимок экрана с доступным пространством на жестком диске до вставки каких-либо данных.

Теперь давайте заполним таблицу десятью миллионами строк данных со случайными датами в диапазоне от 2001 до 2021 года. Я использую десять миллионов, поэтому для справки будет очевидная разница в размере файла. Перед запуском этой вставки не забудьте убедиться, что у вас достаточно свободного места на диске. Кроме того, этот процесс может занять от 20 до 25 минут в зависимости от производительности вашего компьютера.

(Бонус: если в вашем арсенале еще нет универсального генератора данных, вот бесплатная версия, которую вы можете использовать, изменять и делиться ею.)

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

Обратите внимание, что размер файла увеличился с 4096 КБ до более чем 4 ГБ, а объем доступного места на диске уменьшился с 220 ГБ до 216 ГБ (около 4 ГБ, как и ожидалось).

Внесите некоторые изменения в базу данных

Теперь, когда у нас есть тестовый стенд из десяти миллионов строк данных, давайте предположим, что мы хотим удалить все, что старше пяти лет. Это удалит из нашей базы данных около восьми миллионов записей. Что будет соответствовать примерно 3 ГБ в зависимости от того, сколько строк было создано случайным образом с датами старше пяти лет. Помните, что это "случайный" генератор записей, поэтому каждый раз, когда вы запускаете генератор, вы получаете разные результаты.

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

Оглядываясь назад на размеры файлов после удаления примерно восьми миллионов строк данных, мы видим, что размер файла mdf не изменился. И наоборот, файл ldf значительно вырос. Помните, что файл журнала выглядит так, как кажется, он регистрирует все операции вставки, обновления, удаления и т. д., которые произошли с базой данных.Наш ldf-файл вырос до такого размера, потому что для таблицы Sales.Customers в базе данных было выполнено около восьми миллионов операций удаления.

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

Теперь, когда у нас есть готовый сценарий, давайте начнем уменьшать размеры этих файлов до чего-то близкого к тому, что было в начале.

Уменьшить базу данных SQL Server

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

Вариант 1. Использование графического интерфейса в SQL Server Management Studio

На левой панели, где перечислены ваши базы данных, щелкните правой кнопкой мыши "SampleDataBase" и в разделе "Задачи" выберите "Сжать", затем "Файлы", как показано на рисунке ниже.

В следующем диалоговом окне убедитесь, что для параметра "Тип файла" установлено значение "Данные", чтобы сжать файл mdf. В нижней части диалогового окна есть три переключателя с параметрами «Освободить неиспользуемое пространство», «Реорганизовать страницы перед освобождением неиспользуемого пространства» или «Очистить файл путем переноса данных в другие файлы в той же файловой группе». Если вы не уверены, просто оставьте выбранным по умолчанию параметр "Освободить неиспользуемое пространство".

Еще раз, на левой панели, где перечислены ваши базы данных, щелкните правой кнопкой мыши "SampleDataBase" и в разделе "Задачи" выберите "Сжать", затем "Файлы", как показано на изображении ниже.

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

Вариант 2. Использование T-SQL для уменьшения размера файла

Оптимально (на мой взгляд) лучше всего использовать команды T-SQL.

Результаты должны выглядеть примерно так, как показано ниже.

Результаты. Ваши значения могут отличаться из-за удаленных данных.

Теперь давайте вернемся и еще раз проверим размеры наших файлов и доступное место на жестком диске.

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

Глядя на доступное пространство на жестком диске, мы должны вернуться к тому, что у нас было до вставки десяти миллионов строк данных, за исключением 4 ГБ зарезервированного пространства.

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

Еще один шаг

Хорошо, значит, на вашем диске должны быть освобождены дополнительные 3 ГБ, которые держит в заложниках mdf-файл. Есть способ сделать это с помощью нескольких дополнительных шагов, но будьте очень осторожны и выполняйте эти шаги в том порядке, в котором они изложены.

Создайте новую таблицу для хранения оставшихся 2 миллионов записей, которые вы хотите сохранить; мы назовем эту таблицу Sales.TempHold.

Скопируйте все строки данных из Sales.Customers в Sales.TempHold
Сократите таблицу Sales.Customers
Запустите команды сжатия файла для файлов mdf и ldf
Скопируйте все строки данных из Sales.TempHold обратно в Sales.Customers
Усечь таблицу Sales.TempHold
Удалить таблицу Sales.TempHold
Повторно запустите команды сжатия файла в последний раз.

На изображениях ниже показаны новый размер файла mdf, размер файла ldf и свободное место на жестком диске.

Обратите внимание, у нас все еще используется 1 ГБ. Это охватывает примерно 2,5 миллиона строк данных, которые мы сохранили.

Обзор

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

Если вам действительно нужно освободить место на диске (как в этом примере), используйте VARCHAR вместо CHAR для ваших типов данных. Это простое изменение перед тестом, который мы сделали, дает разницу в три гигабайта памяти. Поэтому тщательно спланируйте базу данных и таблицы перед их созданием.

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

Читайте также: