Уменьшение размера базы данных tempdb в SQL Server

Аннотация
В этой статье приведены различные способы, которые можно использовать для уменьшения размера базы данных tempdb в Microsoft SQL Server. Прежде чем уменьшить размер базы данных tempdb с помощью методов, описанных в этой статье, ознакомьтесь со следующей информацией:
  • Размер базы данных tempdb устанавливается равным последнему заданному значению (то есть размеру по умолчанию или последнему размеру, установленному с помощью команды alter database) после каждого перезапуска. Поэтому, если нет необходимости использовать другие значения или немедленно уменьшить размер, не следует выполнять действия, приведенные в этой статье. Для уменьшения размера базы данных можно подождать следующего перезапуска службы SQL Server. Большие размеры базы данных tempdb не повлияют негативным образом на работоспособность службы SQL Server.
  • В SQL Server 2005 и более поздних версиях уменьшение размера tempdb ничем не отличается от уменьшения базы данных пользователя, кроме того, что для размера базы данных tempdb устанавливается заданное значение после каждого перезапуска экземпляра SQL Server.
  • Команду shrink можно безопасно выполнить в базе данных tempdb во время ее работы. Однако могут возникнуть другие ошибки, например блокирование, взаимоблокировка и др., из-за которых команда shrink не будет выполнена. Поэтому, чтобы команда shrink была выполнена правильно наверняка, мы советуем запускать ее, когда сервер работает в однопользовательском режиме или после прекращения всех действий с базой данных tempdb.

Сведения о базе данных tempdb

База данных tempdb является временной рабочей областью. Сервер SQL Server использует базу данных tempdb для выполнения многих задач. Вот некоторые из них:
  • хранение временных таблиц, созданных явным образом;
  • хранение рабочих таблиц, содержащих результаты, созданные в процессе обработки запросов и сортировки;
  • хранение материализованных статических курсоров;
  • хранение записей о версиях при использовании уровней изоляции моментальных снимков или уровней изоляции моментальных снимков зафиксированного чтения.
Сервер SQL Server записывает в журнал транзакций базы данных tempdb сведения, необходимые только для отката транзакции, но недостаточные для воспроизведения транзакций в процессе восстановления базы данных. Это позволяет повысить производительность выражений INSERT в базе данных tempdb. Кроме того, сведения для воспроизведения каких-либо транзакций не требуется записывать в журнал, поскольку база данных tempdb создается заново каждый раз после перезапуска сервера SQL Server. Таким образом, в ней нет транзакций для наката или отката. При запуске сервера SQL Server база данных tempdb создается заново с помощью копии базы данных model, а ее размер устанавливается равным последнему заданному значению. Заданный размер является последним значением размера, установленным явным образом при выполнении операций, изменяющих размер файла, таких как ALTER DATABASE с параметром MODIFY FILE или выражение DBCC SHRINKFILE.

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

В SQL Server 2005 и более поздних версиях можно использовать любой из следующих способов изменения размера базы данных tempdb:

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

Примечание. Средство SQL Server Management Studio в SQL Server 2005 не показывает правильный размер файлов базы данных tempdb после выполнения операции уменьшения размера. Значение параметра "Выделенное в данный момент место" всегда берется из динамического административного представления sys.master_files и не обновляется после выполнения операции по сжатию размера для базы данных tempdb. Чтобы узнать правильный размер файлов базы данных tempdb после сжатия, в SQL Server Management Studio выполните следующий оператор:

use tempdbselect (size*8) as FileSizeKB from sys.database_files
Здесь рассказывается о первых трех методах.

Примечание. Для установок SQL Server 2000 вместо SQL Server Management Studio необходимо использовать анализатор запросов. Кроме того, для использования команд DBCC базу данных необходимо перевести в однопользовательский режим.

Следующие три способа можно использовать для уменьшения размера базы данных tempdb до значения ниже заданного:

Способ 1. С помощью команд Transact-SQL
Примечание. Для этого способа необходимо перезапустить SQL Server.
  1. Остановите SQL Server.
  2. Из командной строки запустите экземпляр в режиме минимальной конфигурации. Для этого выполните указанные ниже действия.
    1. В командной строке перейдите к следующей папке:
    2. Если этот экземпляр SQL Server является именованным, выполните следующую команду:
      sqlservr.exe -s имя_экземпляра -c -f
      Если этот экземпляр SQL Server является экземпляром по умолчанию, выполните следующую команду:
      sqlservr -c -f
      Примечание.
      Параметры -c и -f приводят к запуску SQL Server в режиме минимальной конфигурации, в котором размер файла данных базы данных tempdb составляет 1 МБ, а файла журнала — 0,5 МБ.
  3. Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL:
       ALTER DATABASE tempdb MODIFY FILE   (NAME = 'tempdev', SIZE = размер_в_МБ)     --Желаемый размер файла данных   ALTER DATABASE tempdb MODIFY FILE   (NAME = 'templog', SIZE = размер_в_МБ)   --Желаемый размер файла журнала					
  4. Остановите SQL Server. Для этого в окне командной строки нажмите клавиши CTRL+C, перезапустите SQL Server как службу и проверьте размер файлов Tempdb.mdf и Templog.ldf.
Ограничение этого способа заключается в том, что он работает только с логическими файлами базы данных tempdb по умолчанию, tempdev и templog. Если к базе данных tempdb были добавлены дополнительные файлы, их размер можно уменьшить после перезапуска сервера SQL Server как службы. Все файлы базы данных tempdb заново создаются во время запуска. Однако они являются пустыми и могут быть удалены. Чтобы удалить дополнительные файлы в базе данных tempdb, выполните команду ALTER DATABASE с помощью параметра REMOVE FILE.

Способ 2. С помощью команды DBCC SHRINKDATABASE
Использование команды DBCC SHRINKDATABASE для уменьшения размера базы данных tempdb. DBCC SHRINKDATABASE получает параметр target_percent. Этот параметр указывает желаемый размер свободного места в процентах, который останется в файле базы данных после уменьшения ее размера. При использовании команды DBCC SHRINKDATABASE может потребоваться перезапуск сервера SQL Server.
  1. Определите место на диске, используемое в настоящий момент базой данных tempdb, при помощи хранимой процедуры sp_spaceused. Затем рассчитайте долю в процентах свободного места на диске, доступную для использования, как значение параметра команды DBCC SHRINKDATABASE. Этот расчет основан на желаемом размере базы данных.

    Примечание. В некоторых случаях потребуется выполнить команду sp_spaceused @updateusage=true для повторного расчета используемого места на диске, чтобы получить обновленный отчет. Дополнительные сведения о хранимой процедуре sp_spaceused см. на веб-узле SQL Server Books Online.

    Рассмотрим следующий пример:
    предполагается, что база данных tempdb содержит два файла: основной файл данных (Tempdb.mdf), размер которого составляет 100 МБ, и файл журнала (Tempdb.ldf), размер которого составляет 30 МБ. Предположим, что команда sp_spaceused сообщает, что основной файл данных содержит 60 МБ данных. Также предположим, что необходимо уменьшить размер основного файла данных до 80 МБ. Рассчитаем желаемую долю в процентах свободного места на диске, которое останется после уменьшения размера: 80 МБ – 60 МБ = 20 МБ. Теперь поделим 20 МБ на 80 МБ = 25 % и получим значение параметраtarget_percent. Размер файла журнала транзакций уменьшается соответствующим образом, оставляя 25 % или 20 МБ свободного места после уменьшения размера базы данных.
  2. Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL:
       dbcc shrinkdatabase (tempdb, 'target percent')     -- Эта команда уменьшает размер базы данных tempdb					
Существуют определенные ограничения для использования команды DBCC SHRINKDATABASE для базы данных tempdb. Конечный размер файла данных и файла журнала не может быть меньше размера, указанного при создании базы данных, или последнего размера, явным образом установленного при выполнении операций, изменяющих размер файлов, например команды ALTER DATABASE с параметром MODIFY FILE. Другим ограничением команды BCC SHRINKDATABASE является расчет значения параметра target_percentage и его зависимость от текущего используемого места на диске.

Способ 3. С помощью команды DBCC SHRINKFILE
Использование команды DBCC SHRINKFILE для уменьшения размера отдельных файлов базы данных tempdb. Команда DBCC SHRINKFILE обеспечивает большую гибкость, чем команда DBCC SHRINKDATABASE, поскольку ее можно использовать для отдельного файла базы данных, не затрагивая другие файлы, относящиеся к той же базе данных. Команда DBCC SHRINKFILE использует параметр target size. Это желаемый окончательный размер файла базы данных.
  1. Определите желаемый размер основного файла данных (tempdb.mdf), файла журнала (templog.ldf) и дополнительных файлов, добавленных к базе данных tempdb. Убедитесь, что используемое файлами место на диске меньше желаемого размера или равно ему.
  2. Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL для конкретных файлов базы данных, размер которых необходимо уменьшить:
       use tempdb   go   dbcc shrinkfile (tempdev, 'размер в МБ')   go   -- эта команда уменьшает размер основного файла данных   dbcc shrinkfile (templog, 'размер в МБ')   go   -- эта команда уменьшает размер файла журнала, см. последний абзац.						
Преимущество команды DBCC SHRINKFILE заключается в том, что она позволяет уменьшить размер файла до размера ниже исходного. Команду DBCC SHRINKFILE можно выполнять для любых файлов данных и файлов журнала. Ограничение команды DBCC SHRINKFILE заключается в том, что размер базы данных не удастся сделать меньше, чем размер базы данных model.

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


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

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

SQL Server 2005 и более поздние версии

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).

SQL Server 2000

Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.
Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.
Примечания
  • Эти ошибки не означают наличие реальных повреждений в базе данных tempdb. Тем не менее, имейте в виду, что могут быть другие причины ошибок физического повреждения данных, таких как ошибка 8909, включая проблемы с подсистемой ввода-вывода. Поэтому, если ошибка происходит не в процессе выполнения операции уменьшения размера, необходимо дополнительно исследовать проблему.
  • В SQL Server 2005 и более поздних версиях при выполнении операции уменьшения размера может отображаться сообщение об ошибке 8909, однако это не говорит о наличии ошибок такой операции.
  • В SQL Server 2000 и более ранних версиях эти ошибки вызовут сбой операций уменьшения размера. Поэтому для уменьшения размера базы данных tempdb необходимо будет перезапустить SQL Server, чтобы повторно создать базу данных tempdb. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
    277848 При выполнении команды DBCC DBREINDEX в SQL Server отображается сообщение об ошибке "Table Corrupt Object ID 0, index ID 0, page ID"


Свойства

Номер статьи: 307487 — последний просмотр: 06/24/2014 06:37:00 — редакция: 7.0

Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 64-bit Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 R2 Datacenter, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Parallel Data Warehouse, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Web, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Parallel Data Warehouse, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Web

  • kbsqlsetup kbhowtomaster KB307487
Отзывы и предложения