Как уменьшить размер tempdb в sql server без перезагрузки

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

1. Остановите сервер 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%имя_экземпляра%).
2. Подключитесь к серверу SQL Server с помощью средства Query Analyzer и выполните следующие команды Transact-SQL:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = размер_в_МБ) --Желаемый размер файла данных ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = размер_в_МБ) --Желаемый размер файла журнала

Ограничение этого способа заключается в том, что он работает только с логическими файлами базы данных 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 данной статьи.

Определите место на диске, используемое в настоящий момент базой данных 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 МБ свободного места после уменьшения размера базы данных.

dbcc shrinkdatabase (tempdb, 'target percent') -- Эта команда уменьшает размер базы данных в целом

Существуют определенные ограничения для использования команды 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 для конкретных файлов базы данных, размер которых необходимо уменьшить:

use tempdb go dbcc shrinkfile (tempdev, 'размер в МБ') go -- эта команда уменьшает размер основного файла данных dbcc shrinkfile (templog, 'размер в МБ') go -- эта команда уменьшает размер файла журнала, см. последний абзац.

Преимущество команды 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, включая проблемы с подсистемой ввода-вывода.

Свежие записи

• Топ-8 книг для project-менеджера
• 1С не отправляет почту через сервер timeweb.ru
• Зеленый соус из киви к шашлыку
• Маринованная капуста с уксусом быстрого приготовления
• Онлайн конвертер аудио файлов для астериск
• Копирование ЭЦП с Рутокена
• Выбрать процессор для 1с.
• Cмонтировать папку как диск для всех пользователей

Уменьшение размера temp.db

Добрый день. Как автоматизировать уменьшение размера бд temp.db? Знаю что он уменьшается путем перезагрузки сервера. Но каждый раз запускать ребут, не есть хорошо.

• Дата
• Дата
• Рейтинг всех уровней
• Рейтинг 1-го уровня
• Древо развёрнутое
• Древо свернутое

Свернуть все
4. namazi74 4 23.02.22 14:17 Сейчас в теме

Добрый день. Как автоматизировать уменьшение размера бд temp.db? Знаю что он уменьшается путем перезагрузки сервера. Но каждый раз запускать ребут, не есть хорошо.

5. digorec88 23.02.22 14:34 Сейчас в теме
(4) спасибо. сейчас гляну
16. XAKEP 23.02.22 18:28 Сейчас в теме

(5)
Большие размеры базы данных tempdb не повлияют негативным образом на работоспособность службы SQL Server

из ссылки, которую вам дали 🙂

но вы наверное пропустили этот абзац

2. gybson 23.02.22 14:07 Сейчас в теме
Уменьшать размер tempdb баз не сильно лучше перезагрузки сервера.
3. digorec88 23.02.22 14:10 Сейчас в теме
(2) А если он будет расти? Что тогда?
12. gybson 23.02.22 18:19 Сейчас в теме

(3)тогда надо купить еще дисков

он и должен расти, особенно в случае 1С, когда все виртуальные таблицы располагаются в tempdb

27. starik-2005 2895 24.02.22 13:58 Сейчас в теме
когда все виртуальные таблицы располагаются в tempdb
29. gybson 24.02.22 21:30 Сейчас в теме

(27) абсолютно. Любая таблица остатков, среза последних и т.п. является виртуальной. Со срезами последних есть вариант, но с регистрами накопления и бухгалтерскими нет.

Любой, кто занимается 1С серьезно, не борется с tempdb, а сотрудничает.

31. starik-2005 2895 25.02.22 00:23 Сейчас в теме
Прям все виртуальные таблицы — это инсерты в темпдб, а не соединения с подзапросами?
6. redfred 23.02.22 14:54 Сейчас в теме
(3) А цель уменьшения какая? Место на диске заканчивается? Или просто «чтоб не рос»?
7. digorec88 23.02.22 14:55 Сейчас в теме
(6) нет желания следить за его размером.)
8. XAKEP 23.02.22 15:10 Сейчас в теме

(7)
если его размер 500Гб на диске 750Гб — тогда ,да -уменьшайте 🙂

и да,он должен быть на отдельном диске ( не разделе диска ) .

9. digorec88 23.02.22 15:12 Сейчас в теме

(8) он на отдельном диске. Но вопрос в другом. Есть еще какой нибудь способ, кроме перезагрузки виртуалки?

10. XAKEP 23.02.22 15:14 Сейчас в теме

как узнать способ, если не известно ни размера ни вообще ничего .

не рассчитали размеров дискового пространства для «хозяйства» , да ?

11. digorec88 23.02.22 15:21 Сейчас в теме
(10) места много. Я же говорю. Я не хочу мониторить прирост temp.db
15. gybson 23.02.22 18:24 Сейчас в теме
(11) А кто мешает форматнуть диски во избежание вообще всех проблем?
muskul; XAKEP; + 2 – Ответить
14. gybson 23.02.22 18:23 Сейчас в теме

(8) уменьшение tempdb это нонсенс и ужасающая некомпетентность

13. gybson 23.02.22 18:19 Сейчас в теме
(7) Тогда надо спрашивать на форуме образец заявления на увольнение.
18. digorec88 24.02.22 08:21 Сейчас в теме
(13)шутки за 300
17. redfred 23.02.22 19:05 Сейчас в теме

(11) Ок, прирост вы мониторить не хотите, и, при этом, так же не хотите, чтоб tempdb разрослась на весь диск. И как вам тут поможет её периодический shrink?

Если какой-то кривой запрос начнёт раздувать tempdb, то при активной транзакции особо сжать её вы всё равно не сможете. Тут два варианта — либо диск закончится и всё встанет колом, либо диска хватит, запрос отработает и оставит после себя пустое место, которое в любом случае будет переиспользоваться, хоть с шринком, хоть без.

MSSQL — уменьшаем Initial Size в tempdb

Срочно понадобилось уменьшить размер tempdb. Можно выполнить сжатие, перезапуск сервера, танцы с бубнами. Всё это уменьшит размер tempdb, но не сделает его меньше Initial Size. И это большая проблема, особенно для тех экземпляров, где база tempdb вынесена в оперативную память:

Есть полезная статья от Microsoft, в которой есть решение по уменьшению Initial Size:

Но о подводных камнях там ни слова. Печаль меня настигла, когда я дошёл до пункта: «Подключитесь к серверу SQL Server с помощью анализатора запросов». Сложно найти на сервере анализатор запросов, особенно если он там не установлен. Но можно обойтись без него, читаем.

Сжимаем tempdb

Для уменьшения Initial Size базы tempdb нужно:

1. Остановить службы SQL Server.
2. Запустить SQL Server в режиме минимальной конфигурации.
3. Подключиться к SQL Server от имени администратора (при этом нужно не дать подключиться к серверу другим администраторам раньше вас).
4. Выполнить SQL запросы для уменьшения базы tempdb:

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB) --Desired target size for the data file ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB) --Desired target size for the log file

Перед тем как остановить 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 МБ.

Простой у меня составил четыре минуты.

Как уменьшить размер tempdb в sql server без перезагрузки

База 1с ТИС переделанный на 7.7 , скуль 2005.

неожиданно при работе одного отчета ( универсальный по докам выбирает разные доки по продажам с фильтрами по ка, манагерам, собирает суммы и кг отгрузки. В общем а ля олап) стал расти на скуле темп дб, да так, что при базе 120 гиг становится 140 и более..

от чего место на дисках заканчивается и все нафик валится.

Что с этим можно сделать?
очищать как-то темп дб. или настроить скуль чтобы этого не делал. от чего все это?

(0) очевидно исправить отчет

(0) Не знаю как в 77, но думаю так же как и в 8:
— Переписать запрос — где то слишком большая выборка во ВТ.
— Запретить запуск отчета без отборов
— Убедится что отборы накладываются туда куда надо

(2) Как показала практика пока этого было достаточно при подобной проблеме в 8
(2) ВТ?
— какие обороты в запросе по докам? отчет по полям доков
(1) так он 8 лет работал а счас стал работать непоймикак
(4) Я про обороты ничего не говорил.
ВТ — временная таблица.

(5) В какой то момент при накоплении данных такие вещи происходят, отчет который работал годами ломается.
Пока помогала только переписка.
Но я не спец по 7.7

Боюсь надо лезть в профайлер и смотреть что реально творится в SQL
Да здравствует ОЛАП на 77
реиндексация была? Регистры используются или операции? Может выборка с начала времен?

для начала общую статистику глянуть, когда начинает пухнуть (а лучше в лог писать, каждые n минут)
типа
SELECT
(SUM(unallocated_extent_page_count)*1.0/128) FreeSpace ,

sum(user_object_reserved_page_count) * 8 / 1024 UserObjects,
sum(internal_object_reserved_page_count) * 8 / 1024 InternalObjects,
sum(version_store_reserved_page_count) * 8 / 1024 VersionStore,
sum(mixed_extent_page_count) * 8 / 1024 MixedExtent
FROM
tempdb.sys.dm_db_file_space_usage

Если 99% — InternalObjects, то я не знаю что делать 🙂
Боролись тут не так давно. как появилось, так и пропало, ничо не поняли.
Разве что версию SQL повысить.

lazy table spool приветствует вас, идущие на бой.

гм. тут разбор показал, что один прог в запрос подсчет суммы вставил внешнюю функцию из кода 1с. Из за этого такой финт может быть?

Сумма(ПолучитьСуммуПродаж(СуммаЗаяв,СуммаВклНДС,НДСЗаяв)) Когда (ФлагСписания <> 1)
(12) Не исключено.
(13) Попробуйте удалите этот код и проверьте, возможно оно
(14) Любопытства ради, можно код функции?
(4) все временные данные что не поместились в память могут быть сброшены в темпы.
(12) гы-гы-гы, ага

(14) жесть жёсткая. скорее всего 1с постоянно дёргает эту функцию. возможно там за кадром просто Содом и Гомора. с темпами неявными.

может как-то можно выгрузить например в ТЗ а потом всё это обработать кодом?

(20) я не хочу лезть в этот отчем. это франями написанный инструмент для тиса еще 10 лет назад..

(20) она дергает эту функцию на каждой строке документа, которых 3000 в день, за период 2 месяца. при среднем числе строк в доке 30. вот посчитай.

но эту доработку без моего контроля сделал другой прог а я теперь этот караул разгребаю
Если не дорабатывать. только если темпы на более емкий диск пересилить.

Во-первых размер tempdb нужно ограничивать, уменьшить стоимость запроса в mssql. Посмотреть — какой размер tempdb песле перезагрузки сервера. Бывает, что эта проблема появилась после некорректного перезапуска сервера и описание такой проблемы и ее решение есть на официальном сайте Microsoft.

(25) ограничение темп дб приводит к вылету 1с со скулевой ошибкой
(23) кросс-джойн какой-нить наверняка получился
(29) запросы в клюшках — вещь в себе. Без трассировки ничего определенного сказать нельзя

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

замечено, что темп дб растет только если большая нагрузка на базу.

т.е. когда и сложные отчеты и текущая работа..

как-то модно понять, какие запросы в какое время эту базу разнесли?

В процессе работы 1С:Предприятия 8 возможно значительное увеличение размера базы данных TEMPDB.
Причина

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

«Большие» транзакции, использующие TEMPDB, выполнение которых занимает большой промежуток времени.
Сетевые ошибки, из-за которых Microsoft SQL Server не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, то Microsoft SQL Server будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Время обнаружения подобной ситуации зависит от настроек параметров сетевого протокола, используемого Windows . Например, при использовании протокола TCP/IP это время составляет 2 часа.

Если для завершения активных транзакций не хватает места в базе данных, Microsoft SQL Server автоматически увеличивает размер TEMPDB на величину, заданную в параметрах этой базы данных (по умолчанию — 10% от текущего размера).