Как уменьшить tempdb

Исходные:
MS SQL 2000
Задал вручную размер 3 гб для tempdb — ну надо было для больших изменений в базах
Проблема:
Теперь не получается его обратно маленьким сделать (
ни шринк, ни удаление не помогли
реальный размер < 100 Мб
—
1. Как уменьшить
2. насколько безопасна данная операция

а сервер перезапустить?

(1) скульный перезапускал и виндовый тоже
остановил скуль — грохнул tempdb.mdf(ldf) — стартанул скуль — создалась новая tempdb с таким же размером

(2) спасибо, похоже то, что нужно

WTFM.INFO

Write The F* Manual — Заметки о сетях, администрировании и вообще

MS SQL Shrink/сжатие разросшейся базы TempDB

Иногда база TempDB может разрастись (например после выполнения долгих транзакций над большим количеством данных), если место в TempDB уже освободилось, то для освобождения места на диске можно выполнить ее сжатие (shrink). Сделать это можно либо запросом либо в SSMS студии (сжимать нужно файл данных tempdev — tempdb.mdf).

Если операция shrink не привела к уменьшению файла БД, значит необходимо произвести сброс буферов и кешей сервера и повторить shrink :

Создаем checkpoint и сбрасываем буферы страниц и индексов на диск:

CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO

Чистим кеш хранимых процедур:

DBCC FREEPROCCACHE; GO

Очищаем остальные типы кешей:

DBCC FREESYSTEMCACHE ('ALL'); GO

Чистим кеш сессий:

DBCC FREESESSIONCACHE; GO

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

Работа с базой TempDB

Системная база данных TEMPDB участвует в работе пользователей, подключённых ко всем пользовательским базам данных сервера СУБД.

TEMPDB используется при работе с временными таблицами и процедурами, в ней создаются внутренние (internal) и пользовательские объекты (user objects) промежуточных результатов запросов и т.п..

При запуске сервера, TEMPDB создаётся заново, если TEMPDB по каким то причинам не может быть создана, то сервер СУБД не запуститься. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB, однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.

При работе 1С:Предприятия 8 в режиме клиент-сервер широко используются временные таблицы. Кроме того, TEMPDB используется Microsoft SQL Server при выполнении запросов, использующих операторы GROUP BY, ORDER BY, UNION, SORT, DISTINCT и т.п.

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

Какие могут быть решения данной проблемы:

1. Перезапустить MS SQL Server. В этом случае размер базы данных TEMPDB будет установлен по умолчанию.

2. Сжать базу данных TEMPDB. Для этого нужно в Query Analyzer выполнить следующую команду: DBCC SHRINKDATABASE (TEMPDB).

3. Уменьшить размер отдельных файлов. Для этого нужно в Query Analyzer выполнить команды:

DBCC SHRINKFILE (Логическое_Имя_Файла_Данных, Желаемый_Размер_Файла_Данных_В_Мегабайтах)
go
DBCC SHRINKFILE (Логическое_Имя_Файла_Журнала_Транзакций,
Желаемый_Размер_Файла_Журнала_Транзакций_В_Мегабайтах)
go

Пример.

Уменьшение размера файлов базу TEMPDB до 20 мегабайт

USE TempDB
DBCC SHRINKFILE (tempdev, 20)
go
DBCC SHRINKFILE (templog,20)
go

Пункты 2 и з также можно выполнить с помощью Management Studio

4. Переместить базу данных TEMPDB нас диск большего размера. Изменить месторасположение файлов базы данных TEMPDB можно с помощью команды ALTER DATABASE. Для этого нужно в Query Analyzer выполнить следующую последовательность команд и перезапустить сервер СУБД:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘Новый_Диск:\Новый_Каталог\tempdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘Новый_Диск:\Новый_Каталог\templog.ldf’)
GO

В завершении еще парочка советов по работе с базой TEMPDB:

1. Для оптимизации работы базы данных TEMPDB рекомендуется ее вынесение на отдельный жёсткий диск или RAM-диск и разбиение MDF файла на части (одинакового размера) по числу процессоров (ядер): если процессоров < 8, то количество файлов = количество процессоров; если процессоров >8, то количество файлов для начала 8, а затем добавлять по мере необходимости.

2. При использовании временных таблиц используется кеширование, но это не относится к операциям создания индексов, сортировки, группировки и т.п. Например: создали таблицу, построили индекс (что разумно с точки зрения построения плана), то данная таблица кешироваться не будет. Но если таблица очень маленькая и почти наверняка она SQL-сервером будет сканироваться и создается она очень часто, то возможно имеет смыл операцию создания индекса опустить, в этом случае за счет кеширования таблица будет создаваться быстрее.

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

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

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

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

Метод	Требуется перезагрузка?	Дополнительные сведения
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, чтобы уменьшить размер.

Читать:

Как выключить life paint illustrator

Вы можете уменьшить 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.

Остановите SQL Server.
В командной строке запустите экземпляр в минимальном режиме конфигурации. Для этого выполните следующие шаги.
1. В командной строке перейдите в папку, в которой установлен SQL Server (замените и в следующем примере):
```
cd C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binn 
```
```
sqlservr.exe -s -c -f -mSQLCMD 
```
```
sqlservr -c -f -mSQLCMD 
```
Заметка -f Параметры -c вызывают запуск SQL Server в минимальном режиме конфигурации с tempdb размером 1 МБ для файла данных и 0,5 МБ для файла журнала. Параметр -mSQLCMD запрещает любому другому приложению, кроме sqlcmd , принимать однопользовательское подключение.
```
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = ); ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = ); 
```
Использование команды DBCC SHRINKDATABASE

DBCC SHRINKDATABASE получает параметр target_percent . Это требуемый процент свободного места в файле базы данных после того, как база данных сократилась. При использовании DBCC SHRINKDATABASE может потребоваться перезапустить SQL Server.
Заметка В некоторых случаях может потребоваться выполнить sp_spaceused @updateusage = true пересчет пространства, используемого и для получения обновленного отчета. Дополнительные сведения см. в разделе sp_spaceused (Transact-SQL).
```
DBCC SHRINKDATABASE (tempdb, ''); 
```
В команде 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.mdf templog.ldf ) и дополнительных файлов, добавленных tempdb в . Убедитесь, что пространство, используемое в файлах, меньше или равно требуемому целевому размеру.
2. Подключитесь к SQL Server с помощью SQL Server Management Studio, Azure Data Studio или sqlcmd, а затем выполните следующие команды Transact-SQL для определенных файлов базы данных, которые требуется уменьшить. Замените нужным размером:
```
USE tempdb; GO -- This command shrinks the primary data file DBCC SHRINKFILE (tempdev, ''); GO -- This command shrinks the log file, examine the last paragraph. DBCC SHRINKFILE (templog, ''); 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)
- Удаление файлов данных или журнала из базы данных
- Сжатие файла