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

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

Примечание. Эти способы применимы также в случае использования SQL Server 2005. Однако для выполнения этих операций следует использовать среду SQL Server Management Studio, а не программы Enterprise Manager и Query Analyzer.

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

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

·           хранение временных таблиц, созданных явным образом;

·           хранение рабочих таблиц, содержащих результаты, созданные в процессе обработки запросов и сортировки;

·           хранение материализованных статических курсоров.

Сервер SQL Server записывает в журнал транзакций базы данных tempdb сведения, необходимые только для отката транзакции, но недостаточные для воспроизведения транзакций в процессе восстановления базы данных. Это позволяет повысить производительность выражений INSERT в базе данных tempdb. Кроме того, сведения для воспроизведения каких-либо транзакций не требуется записывать в журнал, поскольку база данных tempdb создается заново каждый раз после перезапуска сервера SQL Server; таким образом, не требуется выполнять накат или откат каких-либо транзакций. При запуске сервера SQL Server база данных tempdb создается заново с помощью копии базы данных model, а ее размер устанавливается равным последнему заданному значению.

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

Уменьшение размера базы данных Tempdb, способ 1

Этот способ требует перезапуска сервера SQL Server.
Остановите сервер SQL Server. Откройте окно командной строки и запустите SQL Server, выполнив следующую команду:
sqlservr -c -f
Параметры -c и -f приводят к запуску SQL Server в режиме минимальной конфигурации, в котором размер файла данных базы данных tempdb составляет 1 МБ, а размер файла журнала составляет 0,5 МБ.
Примечание. При использовании именованного экземпляра SQL Server необходимо изменить соответствующую папку (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) и использовать параметр -s (-s%имя_экземпляра%).

Подключитесь к серверу SQL Server с помощью средства Query Analyzer и выполните следующие команды Transact-SQL:

   ALTER DATABASE tempdb MODIFY FILE

   (NAME = 'tempdev', SIZE = размер_в_МБ)

   --Желаемый размер файла данных

   ALTER DATABASE tempdb MODIFY FILE

   (NAME = 'templog', SIZE = размер_в_МБ)

   --Желаемый размер файла журнала

    

Остановите сервер SQL Server путем нажатия в окне командной строки клавиш Ctrl-C, перезапустите SQL Server как службу и проверьте размер файлов Tempdb.mdf и Templog.ldf.

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

Уменьшение размера базы данных Tempdb, способ 2

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

Внимание! При выполнении команды DBCC SHRINKDATABASE необходимо, чтобы с базой данных tempdb не производились другие операции. Чтобы гарантировать, что другие процессы не смогут использовать базу данных tempdb при выполнении команды DBCC SHRINKDATABASE, необходимо запустить сервер SQL Server в однопользовательском режиме. Дополнительные сведения см. в разделе Последствия выполнения команды DBCC SHRINKDATABASE или DBCCSHRINKFILE в процессе использования базы данных Tempdbданной статьи.

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 с помощью средства Query Analyzer и выполните следующие команды Transact-SQL:

3.                    dbcc shrinkdatabase (tempdb, 'target percent')

4.          -- Эта команда уменьшает размер базы данных в целом

    

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

Уменьшение размера базы данных Tempdb, способ 3

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

Внимание! Команду DBCC SHRINKFILE необходимо выполнять, когда с базой данных tempdb не выполняются другие операции. Чтобы гарантировать, что другие процессы не смогут использовать базу данных tempdb при выполнении команды DBCC SHRINKFILE, необходимо запустить сервер SQL Server в однопользовательском режиме. Дополнительные сведения о команде DBCC SHRINKFILE см. в разделеПоследствия выполнения команды DBCC SHRINKDATABASE или DBCCSHRINKFILE в процессе использования базы данных Tempdb данной статьи.

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

2.                    Подключитесь к серверу SQL Server с помощью средства Query Analyzer и выполните следующие команды Transact-SQL для конкретных файлов базы данных, размер которых необходимо уменьшить:

3.             use tempdb

4.             go

5.           

6.             dbcc shrinkfile (tempdev, 'размер в МБ')

7.             go

8.             -- эта команда уменьшает размер основного файла данных

9.           

10.           dbcc shrinkfile (templog, 'размер в МБ')

11.           go

12.           -- эта команда уменьшает размер файла журнала, см. последний абзац.

     

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

В SQL Server 7.0 уменьшение размера файла журнала транзакций является отложенной операцией, поэтому для выполнения операции уменьшения базы данных необходимо выполнить операцию усечения журнала и резервного копирования. Тем не менее, по умолчанию для базы данных tempdb параметру trunc log on chkpt присвоено значение ON; следовательно, для данной базы данных не требуется выполнять операцию усечения журнала. Дополнительные сведения об уменьшении размера журнала транзакций базы данных в SQL Server 7.0 см. в следующей статье базы знаний Майкрософт:

256650  INF: Уменьшение размера журнала транзакций в SQL Server 7.0 (Эта ссылка может указывать на содержимое полностью или частично на английском языке)

Последствия выполнения команды DBCC SHRINKDATABASE или DBCCSHRINKFILE в процессе использования базы данных Tempdb

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

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.

Хотя ошибка 2501 может не означать наличия повреждений в базе данных tempdb, она приводит к сбою операции уменьшения размера. С другой стороны, ошибка 8909 может быть признаком повреждения в базе данных tempdb. Перезапустите сервер SQL Server, чтобы заново создать базу данных tempdb и очистить ее от ошибок согласованности. Тем не менее, имейте в виду, что могут быть другие причины ошибок физического повреждения данных, таких как ошибка 8909, включая проблемы с подсистемой ввода-вывода.