Возможно база данных tempdb достигла предела памяти или системная таблица не согласована
TEMPDB представляет собой системную базу данных Microsoft SQL Server, в которой хранятся временные таблицы созданные как самим сервером, так и пользователями. Эта база данных создается заново при каждом перезапуске Microsoft SQL Server. По умолчанию размер этой базы данных неограничен и увеличение его осуществляется при необходимости автоматически, порциями по 10% от текущего размера TEMPDB, однако эти параметры могут быть переопределены пользователем. По умолчанию, минимальный размер этой базы данных, который устанавливается при старте Microsoft SQL Server, определяется размером системной базы данных MODEL. Очистка журнала транзакций в этой базе данных производится автоматически, при этом удаляются только неактивные записи журнала транзакций.
При работе 1С:Предприятия 8 в режиме клиент-сервер широко используются временные таблицы . Кроме того, TEMPDB используется Microsoft SQL Server при выполнении запросов, использующих операторы GROUP BY, UNION, DISTINCT и т.п.
Оптимизация производительности базы данных tempdb в SQL Server
Размер и физическое размещение базы данных tempdb может влиять на производительность системы. Например, если для базы данных tempdb установлен слишком малый размер, часть системной нагрузки может приходиться на автоувеличение tempdb до размера, требуемого для поддержки рабочей нагрузки при каждом перезапуске экземпляра SQL Server.
По возможности используйте мгновенную инициализацию файлов, чтобы повысить производительность операций увеличения файлов данных.
Заранее выделите место для всех файлов tempdb , установив для файла размер, достаточный для обеспечения обычной рабочей нагрузки в среде. Предварительное выделение позволяет избежать слишком частого расширения tempdb , способного повлиять на производительность. Следует установить автоувеличение для базы данных tempdb , чтобы увеличить место на диске для незапланированных исключений.
Файлы данных в каждой файловой группе должны иметь одинаковый размер, так как SQL Server использует алгоритм пропорционального заполнения, который повышает вероятность выделения памяти в файлах с большим объемом свободного пространства. Разделение tempdb на множество файлов данных равного размера обеспечивает эффективное выполнение использующих tempdb операций с высокой степенью параллелизма.
Установите приемлемое значение шага увеличения размера файла, чтобы оно не было слишком низким для файлов базы данных tempdb . Если увеличение размера файлов будет слишком малым по сравнению с объемом записываемых в tempdb данных, tempdb может постоянно требовать расширения. Это скажется на производительности.
Чтобы проверить текущий размер и параметры увеличения для tempdb , используйте следующий запрос:
Поместите базу данных tempdb в быструю подсистему ввода-вывода. Если имеется много непосредственно присоединенных дисков, то используйте чередование дисков. Отдельные файлы данных tempdb или их группы не обязательно должны располагаться на разных дисках или шпинделях, если только у вы не наблюдаете узкие места в подсистеме ввода-вывода.
Расположите базу данных tempdb на дисках, отличающихся от используемых пользовательскими базами данных.
Ограничения
С базой данных tempdb нельзя выполнять следующие операции:
- Добавление файловых групп.
- Резервное копирование и восстановление из копии.
- Изменение параметров сортировки. Параметрами сортировки по умолчанию являются параметры сортировки сервера.
- Изменение владельца базы данных. Владельцем tempdb является sa
- Создание моментального снимка базы данных.
- Удаление базы данных.
- Удаление пользователя guest из базы данных.
- Включение отслеживания измененных данных.
- Участие в зеркальном отображении базы данных.
- Удаление первичной файловой группы, первичного файла данных или файла журнала.
- Переименование базы данных или первичной файловой группы.
- Выполнение DBCC CHECKALLOC .
- Выполнение DBCC CHECKCATALOG .
- Перевод базы данных в режим OFFLINE .
- Перевод базы данных или первичной файловой группы в режим READ_ONLY .
Ограничения оптимизированной для памяти базы данных tempdb
Включение и отключение функции не является динамическим. Из-за внутренних изменений, которые необходимо внести в структуру tempdb , для включения или отключения этой функции требуется перезапуск.
Отдельная транзакция не может обратиться к таблицам, оптимизированным для памяти, в более чем одной базе данных. Все транзакции, связанные с таблицей, оптимизированной для памяти, в пользовательской базе данных, не смогут обратиться к системным представлениям tempdb в той же транзакции. Если вы попытаетесь обратиться к системным представлениям tempdb в транзакции с участием таблицы, оптимизированной для памяти, в пользовательской базе данных, возникнет следующая ошибка:
Запросы к таблицам, оптимизированным для памяти, не поддерживают указания блокировки и изоляции, поэтому запросы к представлениям каталога оптимизированной для памяти tempdb не будут учитывать указания блокировки и изоляции. Как и в случае с другими системными представлениями каталога в SQL Server, все транзакции для системных представлений будут находиться в изоляции READ COMMITTED (или READ COMMITTED SNAPSHOT в нашем случае).
Если оптимизированные для памяти метаданные tempdb включены, индексы columnstore нельзя создавать во временных таблицах.
В связи с ограничением на индексы columnstore использование системной хранимой процедуры sp_estimate_data_compression_savings с параметром сжатия данных COLUMNSTORE или COLUMNSTORE_ARCHIVE не поддерживается, если включены оптимизированные для памяти метаданные tempdb .
Эти ограничения применяются только при создании ссылок на системные представления tempdb . При необходимости вы сможете создать временную таблицу в той же транзакции, где обращаетесь к таблице, оптимизированной для памяти, в пользовательской базе данных.
Оптимизированные для памяти метаданные tempdb
Состязание метаданных tempdb всегда было узким местом для масштабируемости многих рабочих нагрузок, выполняющихся в SQL Server. В SQL Server 2019 (15.x) появилась новая функция оптимизированных для памяти метаданных tempdb, входящая в семейство функций выполняющейся в памяти базы данных.
Она эффективно устраняет существующее узкое место и открывает новый уровень масштабируемости для рабочих нагрузок, активно использующих tempdb. В SQL Server 2019 (15.x) системные таблицы, связанные с управлением метаданными временных таблиц, можно переместить в неустойчивые таблицы без кратковременной блокировки, оптимизированные для памяти.
Сейчас функция оптимизированных для памяти метаданных tempdb недоступна для Базы данных SQL Azure и Управляемых экземпляров SQL Azure.
Просмотрите это 7-минутное видео, чтобы узнать, как и когда следует использовать метаданные tempdb, оптимизированные для памяти:
Решение
Уменьшить размер базы данных TEMPDB до требуемой величины можно следующими способами:
В этом случае размер базы данных TEMPDB будет установлен по умолчанию или, если эта величина переопределена пользователем, размер будет установлен в соответствии с заданными параметрами.
DBCC SHRINKDATABASE (TEMPDB)
DBCC SHRINKFILE ( Имя_Файла_Данных, Желаемый_Размер_Файла_Данных )
go
DBCC SHRINKFILE ( Имя_Файла_Журнала_Транзакций, Желаемый_Размер_Файла_Журнала_Транзакций )
go
Следует отметить, что эти команды рекомендуется выполнять в период наименьшей активности пользователей, и для их выполнения необходимо обладать правами администратора.
Более подробное описание и рекомендации по использованию этих команд можно найти в документации по Microsoft SQL Server.
Системная база данных 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 = ‘Новый_Диск:Новый_Каталог empdb.mdf’)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘Новый_Диск:Новый_Каталог emplog.ldf’)
GO
В завершении еще парочка советов по работе с базой TEMPDB:
1. Для оптимизации работы базы данных TEMPDB рекомендуется ее вынесение на отдельный жёсткий диск или RAM-диск и разбиение MDF файла на части (одинакового размера) по числу процессоров (ядер): если процессоров 8, то количество файлов для начала 8, а затем добавлять по мере необходимости.
2. При использовании временных таблиц используется кеширование, но это не относится к операциям создания индексов, сортировки, группировки и т.п. Например: создали таблицу, построили индекс (что разумно с точки зрения построения плана), то данная таблица кешироваться не будет. Но если таблица очень маленькая и почти наверняка она SQL-сервером будет сканироваться и создается она очень часто, то возможно имеет смыл операцию создания индекса опустить, в этом случае за счет кеширования таблица будет создаваться быстрее.
Специальные предложения
Дааа :) Писал своим подобную инструкцию, когда перенесли tempdb на RAM-диск. Всё, как полагается: сделали на RAM-диске папку TEMP, положили туда tempdb, но вот незадача - после перезагрузки сервера папка-то сама не создаётся :-D
По статье бы сделал два дополнения:
1) Как узнать, что не запускается именно из-за tempdb - тут надо лезть в журнал событий Windows
2) Лучше предварительно остановить службу сервера 1С:Предприятия, потому что если 1Ска уже успеет подключиться к MS SQL, то вы со своим SQLCMD пролетаете :)
В нашем случае нельзя останавливать сервер 1С, потому как есть боевые базы, которые работают на другом SQL и должны продолжать работать. Тогда начинаются пляски с песнями:
1: остановил SQL, 2: запустил SQL, 3: запустил SQLCMD, 4: Успех? - работаем, нет? - goto :1.
(4)
простите негодяя, ночь, жена, дети.
конечно же проще просто добавить файл в файловую группу.
(5) прогу завтра уточню. помню только, что с бараном. у нас её админы ставили, потому я не запомнил.
3) в MS SQL Management Studio в свойствах базы tempdb будет показываться неестественный размер базы (вплоть до отрицательного) - это какая-то особенность работы на RAM-диске, проблем из-за этого не возникает.
(5) такую же штуку делал. SuperSpeed RamDisk Pro. Не бесплатная конечно, но есть серверная версия вызывает хоть какое-то доверие.
Правда всё равно проблемы при перезагрузке сервера возникают :(. "Разорились" в итоге на IO Accelerator. Разница особо не ощутима. программку же успешно используем на отдельном сервере для "монопольного восстановления последовательности" :).
(3) baton_pk,
Привет опыт бесценный, не мог бы ты подробно расписать порядок создания файловой группы.
Правильно ли я понимаю, что если temp.db вдруг съел всю ram память, то он начинает использовать вторую файловую группу?
некоторые RАМ-диски не понравились, например этот пришлось отключить
p.s. сам "рецепт" из статьи давно пора уже на ИТС 1С-овцам разместить. а то чем только люди не занимаются получив подобную ошибку.
так самого главного не написали )
хорош ли "выхлоп" от переноса tempdb ?
в моем случае имею сервер с 30 гб ОЗУ, из них 20 не особо то используются,
баз много, пользователей тоже
tempdb показывает размер порядка 600 мб (мдф + лог)
как думаете стоит заморачиваться с переносом ?
(14) так весь "выхлоп" и заключается в том, что освобождается место от гигантских объемов tempdb, переносом tempdb.mdf и tempdb.ldf на другой диск (а то и сами базу и журнал - еще по разным дискам можно разнести).
Скорость здесь не особо повысится - разве что сами диски будут существенно быстрее, типа SSD (для чего, например, и заморачиваются с переносом на RAM диск).
В вашем случае вообще беспокоится не о чем - 600 Мб tempdb - это критично, если диск размером в 1ГБ )
А так - tempdb.mdf может достигать и нескольких десятков ГБ, и сотен ГБ (да еще размер журнала tempdb.ldf тоже не маленький), вот в этом случае и занимаются отделением tempdb и переносом на более емкие диски.
По прошествии времени могу поделится наблюдениями - то что я описывал относилось к второстепенному серверу. Решился перенести на основном
для начала надо понять что происходит на сервере для этого используем монитор ресурсов и т.п. он показывает нагрузку на диск - заморачиваться стоит, если эта нагрузка велика, плюс наблюдаем за средней очередью диска в утилите perfmon, в идеале она должна "болтатся" не больше десятых (0.1)
от чего это зависит нагрузка понять конечно не просто, есть подозрение что нагрузка идет при большом количестве пользователей и обилии запросов к виртуальным таблицам, в теории файл этот виртуальные таблицы и обслуживает, почему это надо делать на диске, а не в памяти, для меня загадка
на практике у меня
база объемом 200+ ГБ
tempdb 16ГБ - вот этот размер тоже не понятно откуда берется, ОЗУ менялась с 32 на 64 гб, ограничения в самом SQL сняты, ограничений по дискам ессно нет смотрим по факту что имеем то имеем опять же
пользователей около 250
в принципе, имеется избыточная оператива при 64 гб сами rphost жрут весьма скромно гигобайты, sql может расти до 30 гб, т.е. эти 16 гб можно было бы выделить и в оперативе
у меня в периоды наибольшей нагрузки идет постоянная запись/чтение порядка 20мБ - казалось бы, цифра не велика, но надо учитывать, что это чтение состоит из мелких операций у "бытовых" винтов рандомное чтение может не тянуть и больше 5мБ, серверных около 20 может быть пределом - это при том что последовательная запись/чтение более 600мБ
таким образом анализируя обращение к файлам temp.db и следует принимать решение - если оно велико толк однозначно будет
у меня после переноса на отдельный ssd очередь диска стала весьма мала до этого прыгала до единиц и десятков - есть подозрение, что именно в эти моменты происходит подвисание
tempdb в Azure SQL
Поведение tempdb в Базе данных SQL Azure отличается от ее поведения в SQL Server, Управляемом экземпляре SQL Azure и в SQL Server на виртуальных машинах Azure.
Параметры базы данных для tempdb в SQL Server
В следующей таблице приводится список значений по умолчанию для каждого параметра базы данных tempdb , а также возможность его изменения. Чтобы просмотреть текущие настройки этих параметров, используйте представление каталога sys.databases .
Заметки о сетях, администрировании и вообще
Иногда база TempDB может разрастись (например после выполнения долгих транзакций над большим количеством данных), если место в TempDB уже освободилось, то для освобождения места на диске можно выполнить ее сжатие (shrink). Сделать это можно либо запросом либо в SSMS студии (сжимать нужно файл данных tempdev — tempdb.mdf).
Если операция shrink не привела к уменьшению файла БД, значит необходимо произвести сброс буферов и кешей сервера и повторить shrink :
Создаем checkpoint и сбрасываем буферы страниц и индексов на диск:
Чистим кеш хранимых процедур:
Очищаем остальные типы кешей:
Чистим кеш сессий:
После этого можно повторно запустить сжатие файла — место на диске должно освободиться (способ чаще всего срабатывает и без первого пункта — без создания checkpoint и сброса буфера страниц).
После того, как посыпался жесткий диск, возникли проблемы с БД на SQL 2012. Программа Астрал (собственно ее БД), перестала запускаться. Подключаюсь SQL Managment Studio, делаю сканирование БД, получаю CHECKDB обнаружил 244 ошибок размещения и 1 ошибок согласованности в базе данных. REPAIR_REBUILD не принесли никаких результатов, но.. Имеется рабочая резервная копия БД, месяцем ранее (к сожалению раньше нет) и у меня возникает вопрос, можно ли как то попытаться выгрузить данные за последний месяц из "битой" БД и загрузить ее в рабочую, но неактуальную? Если, конечно, эти данные неповреждённые.
Средний 2 комментария
@esp23
"REPAIR_REBUILD не принесли никаких результатов" - если не секрет, что ответила БД ?
"REPAIR_ALLOW_DATA_LOSS" - Пробовали ? (очищает битые страницы)
!Рекомендую сначала сделать физическую копию файлов БД (mdf,ldf)!
Как вариант определить битые таблицы по ID, и попробовать Checktable на конкретных таблицах сначала Repair_rebuild, если СУБД ответит что минимальный уровень - REPAIR_ALLOW_DATA_LOSS , значит сделать Checktable(,REPAIR_ALLOW_DATA_LOSS ). Покоцанные страницы таблиц будут очищены, но те, что не покоцаны, должны стать доступны для работы (часть данных потеряется, но БД должна стать доступна для работы).
Если определить битые таблицы по ID - проблематично (по каким-то причинам) можно сразу на БД зарядить :
DBCC CheckDB (, REPAIR_ALLOW_DATA_LOSS).
Т.е. скрипт примерно такой:
CHECKDB обнаружил 246 ошибок размещения и 3 ошибок согласованности в базе данных "AstralReport" В конце запроса
Если вы смогли подключить актуальную базу данных, то удаляйте из неё всё, что есть в резервной копии. Потом сливайте всё в одну.
Ошибки исправляйте по мере поступления (может места с ошибками и не нужны будут).
В этой статье описывается системная база данных tempdb . Она является глобальным ресурсом, доступным всем пользователям, которые подключены к экземпляру SQL Server, Базе данных SQL Azure или Управляемому экземпляру SQL Azure.
Настройка и использование метаданных оптимизированной для памяти базы данных tempdb
Чтобы согласиться на применение этой новой функции, используйте следующий скрипт:
Чтобы это изменение конфигурации вступило в силу, нужно перезапустить службу.
Вы можете проверить, является ли tempdb оптимизированной для памяти, используя следующую команду T-SQL:
Если по какой-то причине не удается запустить сервер после включения оптимизированных для памяти метаданных tempdb , можно обойти эту функцию, запустив экземпляр SQL Server в минимальной конфигурации с помощью параметра запуска -f. После этого вы можете отключить функцию и перезапустить SQL Server в нормальном режиме.
Чтобы защитить сервер от потенциальных состояний нехватки памяти, можно привязать tempdb к пулу ресурсов. В этом случае вместо действий, которые обычно выполняются при привязке пула ресурсов к базе данных, следует использовать команду ALTER SERVER .
Кроме того, даже если метаданные оптимизированной для памяти базы данных tempdb уже включены, чтобы это изменение вступило в силу, требуется перезагрузка.
Увеличение производительности базы данных tempdb в SQL Server
Начиная с версии SQL Server 2016 (13.x);, производительность tempdb дополнительно оптимизирована следующим образом:
- Временные таблицы и табличные переменные кэшируются. Кэширование позволяет операциям удаления и создания временных объектов выполняться очень быстро. Кэширование также снижает вероятность возникновения состязаний, связанных с метаданными и выделением страниц.
- Усовершенствован протокол кратковременных блокировок выделения страниц для снижения количества используемых кратковременных блокировок UP (обновление).
- Снижены затраты ресурсов на ведение журнала tempdb — уменьшено потребление пропускной способности подсистемы ввода-вывода файлом журнала tempdb .
- Программа установки добавляет множество файлов данных tempdb при установке нового экземпляра. Эту задачу можно выполнить с помощью нового элемента управления для ввода в пользовательском интерфейсе в разделе Настройка ядра СУБД и параметра командной строки /SQLTEMPDBFILECOUNT . По умолчанию программа установки добавляет столько файлов данных tempdb , сколько имеется логических процессоров, но их может быть не больше восьми.
- При наличии множества файлов данных tempdb автоматическое увеличение выполняется для всех файлов в одно время и в равном объеме согласно параметрам увеличения. Флаг трассировки 1117 больше не требуется.
- Для всех операций распределения в tempdb используются единообразные экстенты. Флаг трассировки 1118 больше не требуется.
- Для первичной файловой группы свойство AUTOGROW_ALL_FILES включено и не может быть изменено.
Дополнительные сведения об улучшениях производительности в tempdb см. в статье блога TEMPDB — Files and Trace Flags and Updates, Oh My! (TEMPDB — файлы, флаги трассировки и обновления).
Обзор
Системная база данных tempdb — это глобальный ресурс, содержащий следующее:
Временные пользовательские объекты, созданные явно. К ним относятся глобальные или локальные временные таблицы и индексы, временные хранимые процедуры, табличные переменные, возвращаемые функциями с табличными значениями таблицы и курсоры.
Внутренние объекты, создаваемые ядром СУБД. К ним относятся следующие:
- Рабочие таблицы, хранящие промежуточные результаты буферов, курсоры, сортировки и временное хранилище больших объектов (LOB).
- рабочие файлы для операций хэш-соединения или статистических хэш-выражений;
- промежуточные результаты сортировки для таких операций, как создание или перестроение индексов (если указать SORT_IN_TEMPDB ), либо определенных запросов GROUP BY , ORDER BY или UNION .
Хранилища версий. Это коллекции страниц данных со строками данных, которые поддерживают функции управления версиями строк. Существует два типа хранилищ: общее хранилище версий и хранилище версий для построения индекса в подключенном режиме. Хранилища версий содержат следующее:
- версии строк, создаваемые транзакциями изменения данных в базе данных, которая использует READ COMMITTED через транзакции изоляции моментальных снимков и транзакции изоляции управления версиями строк;
- версии строк, создаваемые транзакциями изменения данных для таких функций, как операции с индексами в подключенном режиме, функции MARS (множественный активный результирующий набор) и триггеры AFTER .
Операции в tempdb в минимальном объеме записываются в журнал, что позволяет откатывать транзакции. tempdb создается заново при каждом запуске SQL Server, чтобы система всегда запускалась с чистой копией базы данных. Временные таблицы и хранимые процедуры удаляются автоматически при отключении, и при выключении системы нет активных соединений.
tempdb не требует сохранения каких-либо данных между сеансами SQL Server. Операции резервного копирования и восстановления для tempdb недопустимы.
Мониторинг использования tempdb
Нехватка места на диске для tempdb может привести к значительным сбоям рабочей среды SQL Server, а также помешать работающим приложениям завершить операции. Для отслеживания места на диске, используемого в файлах tempdb , можно применять динамическое административное представление sys.dm_db_file_space_usage:
Для мониторинга действий выделения и освобождения страниц в tempdb на уровне сеансов или задач можно использовать динамические административные представления sys.dm_db_session_space_usage и sys.dm_db_task_space_usage. Эти представления позволяют выявлять большие запросы, временные таблицы или табличные переменные, которые используют много места на диске для tempdb . Кроме того, вы можете использовать несколько счетчиков для мониторинга свободного места в базе данных tempdb и ресурсов, использующих tempdb .
Microsoft SQL Server 2005 Standard Edition Microsoft SQL Server 2005 Developer Edition Microsoft SQL Server 2005 Enterprise Edition Microsoft SQL Server 2005 Workgroup Edition SQL Server 2008 Developer SQL Server 2008 Enterprise SQL Server 2008 R2 Datacenter SQL Server 2008 R2 Developer SQL Server 2008 R2 Enterprise SQL Server 2008 R2 Parallel Data Warehouse SQL Server 2008 R2 Standard SQL Server 2008 R2 Web SQL Server 2008 Standard SQL Server 2008 Web SQL Server 2008 Workgroup SQL Server 2012 Developer SQL Server 2012 Enterprise SQL Server 2012 Standard SQL Server 2012 Web SQL Server 2014 Business Intelligence SQL Server 2014 Business Intelligence SQL Server 2014 Developer SQL Server 2014 Developer SQL Server 2014 Enterprise SQL Server 2014 Enterprise SQL Server 2014 Enterprise Core SQL Server 2014 Enterprise Core SQL Server 2014 Express SQL Server 2014 Express SQL Server 2014 Standard SQL Server 2014 Standard SQL Server 2014 Web SQL Server 2014 Web SQL Server 2016 Developer SQL Server 2016 Enterprise SQL Server 2016 Enterprise Core SQL Server 2016 Standard SQL Server 2017 on Windows (all editions) Еще. Меньше
Сведения о базе данных 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 является именованным, выполните следующую команду:
sqlservr.exe -s имя_экземпляра -c -f Если этот экземпляр SQL Server является экземпляром по умолчанию, выполните следующую команду:
sqlservr -c -f Примечание. Параметры -c и -f приводят к запуску SQL Server в режиме минимальной конфигурации, в котором размер файла данных базы данных tempdb составляет 1 МБ, а размер файла журнала — 0,5 МБ.
Подключитесь к серверу 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 заключается в том, что размер базы данных нельзя сделать меньше, чем размер шаблона базы данных.
tempdb в Базе данных SQL
Отдельные базы данных и базы данных в пуле в службе База данных SQL Azure поддерживают глобальные временные таблицы и глобальные временные хранимые процедуры, которые хранятся в tempdb и имеют область действия на уровне базы данных. Глобальные временные таблицы и глобальные временные хранимые процедуры являются общими для всех сеансов пользователей в рамках одной базы данных. Сеансы пользователей, связанные с другими базами данных, не имеют доступа к глобальным временным таблицам. Дополнительные сведения см. в разделе Глобальные временные таблицы (база данных SQL Azure) в области базы данных.
Для отдельной базы данных или баз данных в пуле в службе "База данных SQL Azure" из всех системных баз данных доступны только база данных master и tempdb . Дополнительные сведения см. в статье Что собой представляет логический сервер в Azure.
Дополнительные сведения о размерах tempdb в Базе данных SQL Azure см. в следующих статьях:
- модель приобретения на основе виртуальных ядер: отдельные базы данных, базы данных в пуле;
- модель приобретения на основе DTU: отдельные базы данных, базы данных в пуле.
Планирование ресурсов для tempdb в SQL Server
Определение требуемого размера tempdb в рабочей среде SQL Server зависит от многих факторов. Как описано выше, эти факторы включают текущую рабочую нагрузку и используемые функции SQL Server. Рекомендуется проанализировать текущую рабочую нагрузку, выполнив следующие задачи в среде тестирования SQL Server:
- Включите автоувеличение для tempdb .
- Запускайте отдельные запросы или файлы трассировки рабочей нагрузки и следите за использованием диска базой данных tempdb .
- Выполняйте операции обслуживания индексов, например перестроение индексов, и следите за использованием диска базой данных tempdb .
- Используйте значения используемого пространства на диске из предыдущих шагов для прогнозирования общей рабочей нагрузки. Скорректируйте полученное значение с учетом предполагаемой параллельной обработки и задайте соответствующий размер tempdb .
Перемещение данных и файлов журналов базы данных tempdb в SQL Server
Сведения о перемещении файлов журналов и данных tempdb см. в статье Перемещение системных баз данных.
Физические свойства tempdb в SQL Server
В следующей таблице приводятся исходные значения конфигурации для файлов данных и журналов tempdb в SQL Server. Значения основаны на значениях по умолчанию для базы данных model . Размеры этих файлов могут немного отличаться в зависимости от выпуска SQL Server.
Количество вторичных файлов данных зависит от числа логических процессоров на компьютере. Как правило, если число логических процессоров меньше или равно восьми, используйте равное ему число файлов данных. Если число логических процессоров больше восьми, используйте восемь файлов данных. Если состязание сохраняется, увеличьте число файлов данных на значение, кратное четырем, пока состязание не снизится до приемлемого уровня, или внесите изменения в рабочую нагрузку или код.
Количество файлов данных по умолчанию основано на общих рекомендациях, приведенных в статье KB 2154845.
Чтобы проверить текущий размер и параметры увеличения для tempdb , выполнить запрос к представлению tempdb.sys.database_files .
Проблема
В процессе работы 1С:Предприятия 8 возможно значительное увеличение размера базы данных TEMPDB .
tempdb в Управляемом экземпляре SQL
Управляемый экземпляр SQL Azure поддерживает временные объекты так же, как и SQL Server, где все глобальные временные таблицы и глобальные временные хранимые процедуры доступны всем пользовательским сеансам в пределах одного управляемого экземпляра. Аналогично доступны все системные базы данных.
Дополнительные сведения о размерах tempdb в Управляемом экземпляре SQL Azure см. в статье Ограничения ресурсов.
Разрешения
Любой пользователь может создавать временные объекты в tempdb . Если не предоставлены дополнительные разрешения, пользователям доступны только принадлежащие им объекты. Можно отозвать разрешение на подключение к tempdb , чтобы запретить пользователю работать с tempdb . Но делать это не рекомендуется, так как tempdb требуется для выполнения некоторых стандартных операций.
Ошибки 2501 и 8909 при выполнении операций сжатия
SQL Server 2005 и более поздние версии
SQL Server 2000
Эти ошибки не означают наличие реальных повреждений в базе данных tempdb. Тем не менее, имейте в виду, что могут быть другие причины ошибок физического повреждения данных, таких как ошибка 8909, включая проблемы с подсистемой ввода-вывода. Поэтому, если ошибка происходит не в процессе выполнения операции сжатия, необходимо дополнительно исследовать проблему.
В SQL Server 2000 и более ранних версиях эти ошибки вызовут сбой операций сжатия. Поэтому для сжатия базы данных tempdb необходимо будет перезапустить SQL Server, чтобы повторно создать базу данных tempdb.
Если в результате перемещений файлов базы данных tempdb Вы доигрались до того, что служба MS SQL Server больше не запускается, то скорее всего у вас проблемы либо с диском, куда указали файл, либо с размерами tempdb, либо с правами на доступ.
Если разобраться по логам скуля с этой проблемой вы не смогли, то надо вернуть назад или в другое место файлы.
Для этого запустите службу в ограниченном режиме (в командной строке)
Если у Вас не экземпляр по умолчанию, а именованный, то строка будет иметь вид
Вызовите через командную строку подключение под учетной записью Windows, имеющей права SYSADM (в моем случае это будет administrator)
SQLCMD -s COMPUTERNAME\administrator
Теперь снова измените путь
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf’)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdblog.ldf’)
GO
У Вас пусть может отличаться. Ну вот, собственно, и все, теперь рестартуйте службу, и все заработает.
Причина
Причиной увеличения размера базы данных TEMPDB, как правило, является невозможность автоматической очистки журнала транзакций и повторного использования свободного пространства в базе данных TEMPDB из-за наличия активных транзакций, использующих объекты этой базы данных. Основные причины, вызывающие длительную блокировку работы этих механизмов базы данных TEMPDB, заключаются в следующем:
- "Большие" транзакции, использующие TEMPDB , выполнение которых занимает большой промежуток времени.
- Сетевые ошибки, из-за которых Microsoft SQL Server не получает уведомление о потере сетевого подключения. Если клиентская рабочая станция зависает, перезагружается, или будет выключена во время исполнения определяемой пользователем транзакции, то Microsoft SQL Server будет считать, что клиент продолжает работу, и выполняющаяся клиентская транзакция будет по-прежнему активна. Время обнаружения подобной ситуации зависит от настроек параметров сетевого протокола, используемого Windows . Например, при использовании протокола TCP/IP это время составляет 2 часа.
Если для завершения активных транзакций не хватает места в базе данных, Microsoft SQL Server автоматически увеличивает размер TEMPDB на величину, заданную в параметрах этой базы данных (по умолчанию – 10% от текущего размера).
Аннотация
В этой статье приведены различные способы, которые можно использовать для сжатия базы данных 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.
Читайте также: