Как очистить tempdb sql server
Работа с базой данных TEMPDB
TEMPDB представляет собой системную базу данных Microsoft SQL Server, в которой хранятся временные таблицы созданные как самим сервером, так и пользователями. Эта база данных создается заново при каждом перезапуске Microsoft SQL Server. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB, однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.
Проблема
Причина
Причиной увеличения размера базы данных TEMPDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства в базе данных TEMPDB из-за наличия активных транзакций, использующих объекты этой базы данных. Основные причины, вызывающие длительную блокировку работы этих механизмов базы данных TEMPDB, заключаются в следующем:
Решение
Уменьшить размер базы данных TEMPDB до требуемой величины можно следующими способами:
В этом случае размер базы данных TEMPDB будет установлен по умолчанию или, если эта величина переопределена пользователем, размер будет установлен в соответствии с заданными параметрами.
DBCC SHRINKDATABASE (TEMPDB)
DBCC SHRINKFILE ( Имя_Файла_Данных, Желаемый_Размер_Файла_Данных )
go
DBCC SHRINKFILE ( Имя_Файла_Журнала_Транзакций, Желаемый_Размер_Файла_Журнала_Транзакций )
go
Следует отметить, что эти команды рекомендуется выполнять в период наименьшей активности пользователей, и для их выполнения необходимо обладать правами администратора.
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.
Уменьшение размера базы данных tempdb в SQL Server.
Уменьшение размера базы данных Tempdb, способ 1
Этот способ требует перезапуска сервера SQL Server.
Ограничение этого способа заключается в том, что он работает только с логическими файлами базы данных 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 данной статьи.
Существуют определенные ограничения для использования команды 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 данной статьи.
Преимущество команды DBCC SHRINKFILE заключается в том, что она позволяет уменьшить размер файла до размера, меньшего, чем исходный размер. Команду DBCC SHRINKFILE можно выполнять для любых файлов данных и файлов журнала. Ограничение команды DBCC SHRINKFILE заключается в том, что размер базы данных нельзя сделать меньше, чем размер базы данных model.
В SQL Server 7.0 уменьшение размера файла журнала транзакций является отложенной операцией, поэтому для выполнения операции уменьшения базы данных необходимо выполнить операцию усечения журнала и резервного копирования. Тем не менее, по умолчанию для базы данных tempdb параметру trunc log on chkpt присвоено значение ON; следовательно, для данной базы данных не требуется выполнять операцию усечения журнала. Дополнительные сведения об уменьшении размера журнала транзакций базы данных в SQL Server 7.0 см. в следующей статье базы знаний Майкрософт:
Последствия выполнения команды DBCC SHRINKDATABASE или DBCCSHRINKFILE в процессе использования базы данных Tempdb
Если база данных tempdb используется и предпринимается попытка уменьшить ее размер с помощью команды DBCC SHRINKDATABASE или DBCC SHRINKFILE, может появиться множество сообщений об ошибках согласованности, подобных приведенным ниже, а операцию уменьшения размера выполнить не удастся:
Хотя ошибка 2501 может не означать наличия повреждений в базе данных tempdb, она приводит к сбою операции уменьшения размера. С другой стороны, ошибка 8909 может быть признаком повреждения в базе данных tempdb. Перезапустите сервер SQL Server, чтобы заново создать базу данных tempdb и очистить ее от ошибок согласованности. Тем не менее, имейте в виду, что могут быть другие причины ошибок физического повреждения данных, таких как ошибка 8909, включая проблемы с подсистемой ввода-вывода.
Как сжать базу данных 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 или DBCC SHRINKDATABASE.
По умолчанию база данных tempdb настроена для автоувеличения размера по мере необходимости. Таким образом, со временем размер этой базы данных может превысить желаемый. Простой перезапуск сервера SQL Server устанавливает размер базы данных равным последнему заданному значению.
В SQL Server 2005 и более поздних версиях можно использовать любой из следующих способов изменения размера базы данных tempdb:
Необходима ли перезагрузка?
Полный контроль размера файлов базы данных tempdb по умолчанию (tempdev и templog).
Работает на уровне базы данных.
Позволяет сжать отдельные файлы.
Способ сжатия файлов базы данных через графический пользовательский интерфейс.
Примечание. Средство SQL Server Management Studio в SQL Server 2005 не показывает правильный размер файлов базы данных tempdb после выполнения операции сжатия. Значение параметра «Выделенное в данный момент место» всегда берется из динамического административного представления sys.master_files и не обновляется после выполнения операции по сжатию размера для базы данных tempdb. Чтобы узнать правильный размер файлов базы данных tempdb после сжатия, в SQL Server Management Studio выполните следующую инструкцию:
Здесь рассказывается о первых трех методах.
Примечание. Для установок SQL Server 2000 вместо SQL Server Management Studio нужно использовать анализатор запросов. Кроме того, для использования команд DBCC базу данных потребуется перевести в однопользовательский режим.
Следующие три способа можно использовать для сжатия базы данных tempdb до значения ниже заданного:
Способ 1. Используйте команды Transact-SQL
Примечание. Для этого способа нужно перезапустить SQL Server.
Остановите SQL Server.
Из командной строки запустите экземпляр в режиме минимальной конфигурации. Для этого выполните следующие действия:
В командной строке перейдите к следующей папке:
Если этот экземпляр SQL Server является именованным, выполните следующую команду:
Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL:
Остановите 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.
Определите место на диске, используемое в настоящий момент базой данных tempdb, с помощью хранимой процедуры sp_spaceused. Затем рассчитайте долю в процентах свободного места на диске, доступного для использования, как значение параметра команды DBCC SHRINKDATABASE. Этот расчет основан на желаемом размере базы данных.
Примечание. В некоторых случаях потребуется выполнить команду sp_spaceused @updateusage=true для повторного расчета используемого места на диске, чтобы получить обновленный отчет. Дополнительные сведения о хранимой процедуре sp_spaceused см. на веб-сайте электронной документации на SQL Server.
Рассмотрим следующий пример.
Предположим, что база данных tempdb содержит два файла: основной файл данных (Tempdb.mdf) размером 100 МБ и файл журнала (Tempdb.ldf) размером 30 МБ. Предположим, что команда sp_spaceused сообщает, что основной файл данных содержит 60 МБ данных. Также предположим, что необходимо сжать основной файл данных до 80 МБ. Рассчитаем желаемую долю в процентах свободного места на диске, которое останется после уменьшения размера: 80 МБ – 60 МБ = 20 МБ. Теперь поделим 20 МБ на 80 МБ = 25 % и получим значение параметра target_percent. Размер файла журнала транзакций уменьшается соответствующим образом, оставляя 25 % или 20 МБ свободного места после сжатия базы данных.
Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL:
Существуют определенные ограничения для использования команды 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. Это желаемый окончательный размер файла базы данных.
Определите желаемый размер основного файла данных (tempdb.mdf), файла журнала (templog.ldf) и дополнительных файлов, добавленных к базе данных tempdb. Убедитесь, что используемое файлами место на диске меньше желаемого размера или равно ему.
Подключитесь к серверу SQL Server с помощью анализатора запросов и выполните следующие команды Transact-SQL для конкретных файлов базы данных, которые необходимо сжать:
Преимущество команды DBCC SHRINKFILE заключается в том, что она позволяет уменьшить размер файла до размера ниже исходного. Команду DBCC SHRINKFILE можно выполнять для любых файлов данных и файлов журнала. Ограничение команды DBCC SHRINKFILE заключается в том, что размер базы данных нельзя сделать меньше, чем размер шаблона базы данных.
Ошибки 2501 и 8909 при выполнении операций сжатия
Если база данных tempdb используется и предпринимается попытка сжать ее с помощью команды DBCC SHRINKDATABASE или DBCC SHRINKFILE, могут появиться сообщения, подобные приведенным ниже (в зависимости от используемой версии SQL Server).
SQL Server 2005 и более поздние версии
Сервер: Сообщение 2501, уровень 16, состояние 1, строка 1 Не удается найти таблицу с именем ‘1525580473’. Проверьте таблицу sysobjects.
Сервер: Сообщение 8909, уровень 16, состояние 1, строка 0 Таблица повреждена: идентификатор объекта 1, идентификатор индекса 0, идентификатор страницы %S_PGID. PageId в заголовке страницы = %S_PGID.
Эти ошибки не означают наличие реальных повреждений в базе данных tempdb. Тем не менее, имейте в виду, что могут быть другие причины ошибок физического повреждения данных, таких как ошибка 8909, включая проблемы с подсистемой ввода-вывода. Поэтому, если ошибка происходит не в процессе выполнения операции сжатия, необходимо дополнительно исследовать проблему.
В SQL Server 2005 и более поздних версиях при выполнении операции сжатия может отображаться сообщение об ошибке 8909, однако это не говорит о наличии ошибок такой операции.
В SQL Server 2000 и более ранних версиях эти ошибки вызовут сбой операций сжатия. Поэтому для сжатия базы данных tempdb необходимо будет перезапустить SQL Server, чтобы повторно создать базу данных tempdb.
Рекомендации для уменьшения содержимого распределения в SQL Server базе данных tempdb
В этой статье помогают устранить проблему, при которой при большой нагрузке на сервере наблюдается серьезная блокировка.
Оригинальная версия продукта: SQL Server
Исходный номер КБ: 2154845
Симптомы
На сервере, который работает Microsoft SQL Server, вы заметите серьезные блокировки, когда сервер испытывает тяжелую нагрузку. Динамические представления управления [или] указывают на то, что эти запросы или задачи sys.dm_exec_request sys.dm_os_waiting_tasks ждут ресурсов tempdb. Кроме того, тип ожидания — и ресурс ожидания указывает на PAGELATCH_UP страницы в tempdb. Эти страницы могут иметь формат 2:1:1, 2:1:3 и т. д. (страницы PFS и SGAM в tempdb).
Если страница имеет 8088, она является страницей PFS. Например, страница 2:3:905856 является PFS в file_id=3 в tempdb.
В следующих операциях активно используется tempdb:
Эти действия могут вызывать проблемы с раздором.
Причина
Когда база данных tempdb активно используется, SQL Server может возникнуть раздор при выделении страниц. В зависимости от степени раздора это может привести к тому, что запросы и запросы, в которых задействован tempdb, будут ненадолго безответными.
Во время создания объекта необходимо выделить две (2) страницы из смешанной степени и приурочить к новому объекту. Одна страница для карты распределения индексов (IAM), а вторая — для первой страницы объекта. SQL Server отслеживает смешанные масштабы с помощью страницы Общая глобальная карта распределения (SGAM). Каждая страница SGAM отслеживает около 4 гигабайт данных.
Чтобы выделить страницу из смешанной степени, SQL Server необходимо просмотреть страницу Page Free Space (PFS), чтобы определить, какая смешанная страница может быть выделена бесплатно. На странице PFS отслеживается свободное пространство, доступное на каждой странице, и каждая страница PFS отслеживает около 8000 страниц. Для внесения изменений на страницы PFS и SGAM поддерживается соответствующая синхронизация; и это может привести к срыву других модификаторов в течение коротких периодов времени.
Когда SQL Server для выделения смешанной страницы, она всегда запускает сканирование на одной и той же странице файла и SGAM. Это приводит к интенсивному раздору на странице SGAM, когда происходит несколько распределений на смешанных страницах. Это может привести к проблемам, которые описаны в разделе Симптомы.
Кроме того, необходимо изменить страницы. Это может способствовать увеличению содержимого.
Дополнительные данные о различных механизмах распределения, используемых SQL Server (SGAM, GAM, PFS, IAM), см. в разделе Ссылки.
Решение
SQL Server 2016 и более поздних версиях:
Оптимизация производительности базы данных tempdb в SQL Server.
Дополнительные сведения об этих рекомендациях и других изменениях, внесенных в SQL 2016 г.
SQL Server 2014 и более ранних версиях:
Чтобы улучшить concurrency tempdb, попробуйте следующие методы:
Увеличение количества файлов данных в tempdb, чтобы увеличить пропускную способность диска и уменьшить раздор в структурах распределения. Как правило, если число логических процессоров меньше или равно восьми (8), используйте такое же количество файлов данных, как и логические процессоры. Если число логических процессоров больше восьми (8), используйте восемь файлов данных. Если раздор продолжается, увеличите количество файлов данных на несколько 4 (4) до количества логических процессоров до тех пор, пока содержание не будет уменьшено до допустимых уровней. Кроме того, внести изменения в рабочей нагрузке или коде.
Рассмотрите возможность реализации рекомендаций по наилучшей практике в работе с tempdb в SQL Server 2005 г.
Если предыдущие действия не уменьшают существенное количество раздора выделения, а раздор находится на страницах SGAM, реализуйте флаг трассировки -T1118. Под этим флагом трассировки SQL Server в полном объеме каждому объекту базы данных, тем самым устраняя раздор на страницах SGAM.
Этот флаг трассировки влияет на каждую базу данных в экземпляре SQL Server. Сведения о том, как определить, находится ли раздор выделения на страницах SGAM, см. в странице Мониторинговый контент, вызванный операциями DML.
В SQL Server 2014 г. убедитесь, что вы применяйте Пакет обновления 3, чтобы воспользоваться исправлением, задокументированным в следующей статье KB. Дальнейшее улучшение снижает уровень содержимого в SQL Server 2014 г. В дополнение к распределению круговой развязки во всех файлах данных tempdb исправление улучшает распределение страниц PFS путем выполнения распределений кругового робина на нескольких страницах PFS в одном файле данных.
Блог команды тигра MSSQL: файлы и флаги трассировки и обновления в SQL Server tempdb
Увеличение количества файлов данных tempdb с равным размером
Например, если размер одного файла данных tempdb составляет 8 ГБ, а размер файла журнала — 2 ГБ, необходимо увеличить количество файлов данных до восьми (каждый из 1 ГБ для поддержания равного размера) и оставить файл журнала таким, как есть. Наличие различных файлов данных на отдельных дисках будет предоставлять дополнительные преимущества производительности. Однако это не требуется. Файлы могут сосуществовать на одном томе диска.
Оптимальное количество файлов данных tempdb зависит от степени раздора в tempdb. В качестве отправной точки можно настроить tempdb как минимум на количество логических процессоров, которые назначены для SQL Server. Для более высокой системы начальный номер может быть восемь (8). Если раздор не уменьшается, может потребоваться увеличить количество файлов данных.
Рекомендуется использовать равное размер файлов данных. SQL Server 2000 Пакет обновления 4 (SP4) ввел исправление, использующее алгоритм кругового робина для смешанных выделений страниц. В связи с этим начальный файл отличается для каждого последовательного смешанного распределения страниц (если существует несколько файлов). Новый алгоритм распределения для SGAM — это чистый круглая малиновка и не использует пропорциональный заполняемость для поддержания скорости. Рекомендуется создавать все файлы данных tempdb с одинаковым размером.
Как увеличение количества файлов данных tempdb уменьшает раздор
В следующем списке объясняется, как увеличение числа файлов данных tempdb с равным размером уменьшает раздор:
Если у вас есть один файл данных для tempdb, у вас есть только одна страница GAM и одна страница SGAM для каждого 4 ГБ пространства.
Увеличение количества файлов данных с одинаковыми размерами для tempdb эффективно создает одну или несколько страниц GAM и SGAM для каждого файла данных.
Алгоритм распределения для GAM выделяет по одной степени (восемь соразмерных страниц) из числа файлов в круговой моды, в то время как в честь пропорционального заполнения. Таким образом, если у вас 10 файлов одинакового размера, первое выделение из File1, второе из File2, третье из File3 и так далее.
Раздор ресурсов страницы PFS уменьшается, так как восемь страниц одновременно помечены как FULL, так как GAM раздает страницы.
Этот раздел применим только к SQL Server 2014 и более ранним версиям.
Недостатки
Недостатком использования -T1118 является то, что размер базы данных может увеличиться, если верны следующие условия:
Если эти условия верны, можно выделить 64 КБ (восемь страниц * 8 КБ = 64 КБ) для объекта, для чего требуется только 8 КБ пространства, в результате чего объем хранилища составляет 56 КБ. Однако если новый объект использует более 64 КБ (восемь страниц) в своей жизни, нет недостатка в флаге трассировки. Поэтому в худшем случае SQL Server при первом выделении можно выделить семь (7) дополнительных страниц только для новых объектов, которые никогда не выходят за рамки одной (1) страницы.