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

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

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

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

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

Замечания

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

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

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

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

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

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

Примечание.

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

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

Примечание.

Вы можете подключиться к экземпляру SQL Server с помощью любого знакомого клиентского средства SQL Server, например sqlcmd, SQL Server Management Studio (SSMS) или расширения MSSQL для Visual Studio Code.

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
      

      Примечание.

      -c Параметры -f вызывают запуск 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. Используйте хранимую sp_spaceused процедуру, чтобы проверить пространство, используемое tempdbв данный момент. Затем вычислите процент свободного места для использования в качестве параметра 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. Выполните следующую команду 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 SHRINKFILE принимает параметр target_size. Этот параметр задает требуемый окончательный размер файла базы данных.

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

2. Подключитесь к SQL Server с помощью SSMS, Visual Studio Code или 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)
• Удаление файлов данных или журнала из базы данных
• Сжатие файла