Сжатие базы данных 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, чтобы уменьшить размер.
Вы можете уменьшить 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.
- В командной строке запустите экземпляр в минимальном режиме конфигурации. Для этого выполните следующие шаги.
- В командной строке перейдите в папку, в которой установлен SQL Server (замените и в следующем примере):
cd C:\Program Files\Microsoft SQL Server\MSSQL.\MSSQL\Binnsqlservr.exe -s -c -f -mSQLCMDsqlservr -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.
-
Определите пространство, которое в настоящее время используется tempdb с помощью хранимой sp_spaceused процедуры. Затем вычислите процент свободного пространства, которое осталось для использования в качестве параметра DBCC SHRINKDATABASE . Это вычисление основано на требуемом размере базы данных.
Заметка В некоторых случаях может потребоваться выполнить 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 получает параметр. Это требуемый окончательный размер файла базы данных.
- Определите требуемый размер основного файла данных (), файла журнала ( tempdb.mdf templog.ldf ) и дополнительных файлов, добавленных tempdb в . Убедитесь, что пространство, используемое в файлах, меньше или равно требуемому целевому размеру.
- Подключитесь к 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)
- Удаление файлов данных или журнала из базы данных
- Сжатие файла
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 и сброса буфера страниц).
Gilev.ru
Вячеслав как обычно лаконичен.
На самом деле методика есть начиная от найти и сбросить сеанс зависший или активно пишущий в tempdb и заканчивая использованием инструкций DBCC.Лобанов Игорь4 Сообщений: 8 Зарегистрирован: 27 июл 2017, 08:13 Откуда: Россия, Рязань
Re: Очистка TempDb
Гилёв Вячеслав » 08 апр 2019, 12:41
у него якобы «аварийное» завершение
таблица живет в рамках «сессии»
если клиент «умер» то замучатесь объяснять как отстрелить такую сесиию, разве что тупо ждать когда время старта превысит порог и по истечению времени отстреливать сверхстарыеГилёв Вячеслав Сообщений: 2727 Зарегистрирован: 11 фев 2013, 15:40 Откуда: Россия, Москва
Re: Очистка TempDb
javawin » 11 апр 2019, 04:11
На скл ру в одноименном топике похоже объяснили, как отстрелить сессию. Ссылку не помещаю, по моему с ними трудности на форуме, но найти нетрудно кому понадобится.Ну или хозяева могут ее положить, если захотят.
javawin Сообщений: 11 Зарегистрирован: 23 мар 2019, 03:30
Re: Очистка TempDb
Гилёв Вячеслав » 11 апр 2019, 12:04
отстрелить сессию нет ничего проще https://docs.microsoft.com/ru-ru/sql/t- . erver-2017
вопрос не в отстреле, а в идентификации идентификатора в условиях пуллирования соединений сервером 1СГилёв Вячеслав Сообщений: 2727 Зарегистрирован: 11 фев 2013, 15:40 Откуда: Россия, Москва
Re: Очистка TempDb
javawin » 15 апр 2019, 13:08
Это понятно, там ссылка на запрос который сессии сожравшие темпдб показывает, вот текст
Код: выделить все ;WITH task_space_usage AS (
— SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
— Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), »
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESCjavawin Сообщений: 11 Зарегистрирован: 23 мар 2019, 03:30
Re: Очистка TempDb
Гилёв Вячеслав » 17 апр 2019, 13:52
MSSQL – уменьшаем tempdb
Срочно понадобилось уменьшить размер tempdb. Можно выполнить сжатие, перезапуск сервера, танцы с бубнами. Всё это уменьшит размер tempdb, но не сделает его меньше Initial Size. И это большая проблема.
Печаль меня настигла, когда я дошёл до пункта: “Подключитесь к серверу SQL Server с помощью анализатора запросов”. Сложно найти на сервере анализатор запросов, особенно если он там не установлен. Но можно обойтись без него, читаем.
Сжимаем tempdb
Для уменьшения Initial Size базы tempdb нужно:
- Остановить службы SQL Server.
- Запустить SQL Server в режиме минимальной конфигурации.
- Подключиться к SQL Server от имени администратора (при этом нужно не дать подключиться к серверу другим администраторам раньше вас).
- Выполнить SQL запросы для уменьшения базы tempdb:
ALTER DATABASE tempdb MODIFY FILE
(NAME = ‘tempdev’, SIZE = target_size_in_MB)
–Desired target size for the data fileALTER DATABASE tempdb MODIFY FILE
(NAME = ‘templog’, SIZE = target_size_in_MB)
–Desired target size for the log file - Остановить SQL Server.
- Запустить SQL Server в обычном режиме.
Перед тем как остановить SQL Server подумайте, как сделать так, чтобы никто другой потом не смог установить соединение раньше вас. Особенно 1С.
- Вы можете зайти на сервер через консоль KVM и отключить сеть.
- Вы можете запретить доступ на сервер извне с помощью Firewall.
- Вы можете остановить все приложения, которые работают с данным SQL сервером.
- Вы можете сменить порт SQL сервера.
- Вы можете сменить пароль администратора SQL сервера.
- Вы можете ничего не делать, но при этом действовать быстро и подключиться к серверу первым.
Перед началом работ решите, какой установите Initial Size для tempdev и templog.
У меня начальный размер tempdb 12 ГБ, уменьшу до 1 ГБ.
Останавливаем службы SQL Server.
Открываем командную строку под администратором. Переходим в рабочую директорию:
cd "C:\Program Files\Microsoft SQL Server\MSSQL12.DL1CSQL00\MSSQL\Binn"
Запускаем SQL сервер в режиме минимальной конфигурации:
sqlservr -c -f
Теперь нужно подключиться к SQL серверу. Если используем SQL Server Management Studio, то получим ошибку:
Login failed for user User. Reason: Server is in single user mode. Only one administrator can connect at this time.
Вероятно, студия выполняет несколько коннектов, что недопустимо в режиме single user mode. Похожая ошибка возникнет и в том случае, если кто-то успеет выполнить соединение раньше вам.
Запускаем вторую командную строку под администратором. Выполняем:
sqlcmd -S localhost -E
Если видим “1>“, то подключение успешно. Вводим:
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 1024) ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 100) GO
Нажимаем в этом окне Ctrl+C, соединение завершается.
Нажимаем в окне с запущенным SQL сервером Ctrl+C, на вопрос об остановке SQL сервера пишем “Y”, SQL сервер останавливается.
Запускаем SQL сервер в обычном режиме.
Проверяем размер tempdb. Initial Size теперь 1024 МБ.
Простой у меня составил четыре минуты.