Сжатие базы данных tempdb

Область применения: SQL Server Управляемый экземпляр SQL Azure

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

Для изменения размера tempdbможно использовать любой из следующих методов. Первые три варианта описаны в этой статье. Если вы хотите использовать SQL Server Management Studio (SSMS), следуйте инструкциям в статье "Сжатие базы данных".

Способ	Требуется перезагрузка?	Дополнительные сведения
ALTER DATABASE	Да	Предоставляет полный контроль над размером файлов по умолчанию tempdb (tempdev и templog).
DBCC SHRINKDATABASE	No	Работает на уровне базы данных.
DBCC SHRINKFILE	No	Позволяет сжимать отдельные файлы.
SQL Server Management Studio	No	Сжатие файлов базы данных с помощью графического пользовательского интерфейса.

Замечания

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

При запуске tempdb SQL Server повторно создается с помощью копии model базы данных и tempdb сбрасывается до последнего настроенного размера. Настроенный размер — это последний явный размер, заданный с помощью операции изменения размера файла, например ALTER DATABASE для использования MODIFY FILE параметра или DBCC SHRINKFILE DBCC SHRINKDATABASE инструкций. Таким образом, если вам не придется использовать различные значения или получить немедленное разрешение в большой tempdb базе данных, можно ожидать следующего перезапуска службы SQL Server, чтобы уменьшить размер.

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

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

Дополнительные сведения об управлении и мониторинге tempdbсм. в разделе "Планирование емкости" и "Мониторинг tempdb".

Использование команды ALTER DATABASE

Примечание.

Эта команда работает только в логических файлах tempdev по умолчанию tempdb иtemplog. Если в нее добавляются tempdbдополнительные файлы, их можно сжать после перезапуска SQL Server в качестве службы. Все tempdb файлы создаются повторно во время запуска. Однако они пусты и могут быть удалены. Чтобы удалить дополнительные файлы, tempdbиспользуйте ALTER DATABASE команду с параметром REMOVE FILE .

Для этого метода требуется перезапустить SQL Server.

1. Остановите SQL Server.

2. В командной строке запустите экземпляр в минимальном режиме конфигурации. Для этого выполните следующие шаги.

   1. В командной строке перейдите в папку, в которой установлен SQL Server (замените <VersionNumber> и <InstanceName> в следующем примере):

      cd C:\Program Files\Microsoft SQL Server\MSSQL<VersionNumber>.<InstanceName>\MSSQL\Binn
      

   2. Если экземпляр является именованным экземпляром SQL Server, выполните следующую команду (замените <InstanceName> в следующем примере):

      sqlservr.exe -s <InstanceName> -c -f -mSQLCMD
      

   3. Если экземпляр является экземпляром SQL Server по умолчанию, выполните следующую команду:

      sqlservr -c -f -mSQLCMD
      

      Примечание.

      -f Параметры -c вызывают запуск SQL Server в минимальном режиме конфигурации с tempdb размером 1 МБ для файла данных и 0,5 МБ для файла журнала. Параметр -mSQLCMD запрещает любому другому приложению, кроме sqlcmd , принимать однопользовательское подключение.

3. Подключитесь к SQL Server с помощью sqlcmd, а затем выполните следующие команды Transact-SQL. Замените <target_size_in_MB> нужным размером:

   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'tempdev', SIZE = <target_size_in_MB>);
   
   ALTER DATABASE tempdb MODIFY FILE
   (NAME = 'templog', SIZE = <target_size_in_MB>);
   

4. Остановите SQL Server. Для этого нажмите в Ctrl+C окне командной строки, перезапустите SQL Server в качестве службы, а затем проверьте размер tempdb.mdf и templog.ldf файлы.

Использование команды DBCC SHRINKDATABASE

DBCC SHRINKDATABASE получает параметр target_percent. Это требуемый процент свободного места в файле базы данных после того, как база данных сократилась. При использовании DBCC SHRINKDATABASEможет потребоваться перезапустить SQL Server.

1. Определите пространство, которое в настоящее время используется tempdb с помощью хранимой sp_spaceused процедуры. Затем вычислите процент свободного пространства, которое осталось для использования в качестве параметра DBCC SHRINKDATABASE. Это вычисление основано на требуемом размере базы данных.

   Примечание.

   В некоторых случаях может потребоваться выполнить sp_spaceused @updateusage = true пересчет пространства, используемого и для получения обновленного отчета. Дополнительные сведения см. в sp_spaceused.

   Рассмотрим следующий пример:

   Предположим, что tempdb имеется два файла: основной файл данных (tempdb.mdf), который составляет 1024 МБ и файл журнала (tempdb.ldf) составляет 360 МБ. Предположим, что sp_spaceused отчеты о том, что основной файл данных содержит 600 МБ данных. Кроме того, предположим, что необходимо сократить основной файл данных до 800 МБ. Вычислите требуемый процент свободного места, оставшееся после сжатия: 800 МБ - 600 МБ = 200 МБ. Теперь разделите 200 МБ на 800 МБ = 25 процентов, и это ваш target_percent. Файл журнала транзакций сократился соответствующим образом, оставив 25 процентов или 200 МБ свободного места после того, как база данных сократилась.

2. Подключитесь к SQL Server с помощью SSMS, Azure Data Studio или sqlcmd, а затем выполните следующую команду Transact-SQL. Замените <target_percent> нужным процентом:

   DBCC SHRINKDATABASE (tempdb, '<target_percent>');
   

В команде DBCC SHRINKDATABASE tempdbесть ограничения. Целевой размер файлов данных и журналов не может быть меньше, чем размер, указанный при создании базы данных, или меньше последнего размера, который был явно задан с помощью операции изменения размера файла, такой как ALTER DATABASE этот MODIFY FILE параметр. Другим ограничением DBCC SHRINKDATABASE является вычисление target_percentage параметра и его зависимость от текущего пространства, используемого.

Использование команды DBCC SHRINKFILE

DBCC SHRINKFILE Используйте команду для сжатия отдельных tempdb файлов. DBCC SHRINKFILE обеспечивает большую гибкость, чем DBCC SHRINKDATABASE из-за того, что его можно использовать в одном файле базы данных, не затрагивая другие файлы, принадлежащие той же базе данных. DBCC SHRINKFILEtarget_size получает параметр. Это требуемый окончательный размер файла базы данных.

1. Определите требуемый размер основного файла данных (), файла журнала (tempdb.mdftemplog.ldf) и дополнительных файлов, добавленных tempdbв . Убедитесь, что пространство, используемое в файлах, меньше или равно требуемому целевому размеру.

2. Подключитесь к SQL Server с помощью SSMS, Azure Data Studio или sqlcmd, а затем выполните следующие команды Transact-SQL для определенных файлов базы данных, которые требуется уменьшить. Замените <target_size_in_MB> нужным размером:

   USE tempdb;
   GO
   
   -- This command shrinks the primary data file
   DBCC SHRINKFILE (tempdev, '<target_size_in_MB>');
   GO
   
   -- This command shrinks the log file, examine the last paragraph.
   DBCC SHRINKFILE (templog, '<target_size_in_MB>');
   GO
   

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

Ошибка 8909 при выполнении операций сжатия

Если tempdb используется и если вы пытаетесь сжать его с помощью DBCC SHRINKDATABASE команд или DBCC SHRINKFILE команд, вы можете получать сообщения, похожие на следующие, в зависимости от используемой версии SQL Server:

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (6:8040) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

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

Хотя сообщение 8909 возвращается приложению или пользователю, выполняющему операцию сжатия, операции сжатия не завершаются ошибкой.

Связанный контент

• Рекомендации по настройке автоувеличения и автосжатия в SQL Server
• Файлы и файловые группы базы данных
• sys.databases (Transact-SQL)
• sys.database_files (Transact-SQL)
• Сжатие базы данных
• DBCC SHRINKDATABASE (Transact-SQL)
• DBCC SHRINKFILE (Transact-SQL)
• Удаление файлов данных или журнала из базы данных
• Сжатие файла