Работа с базой данных 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.
sqlsunday.com
T-SQL tips and tricks, best practices and query plans from the field.
Shrinking tempdb without restarting SQL Server
Ok, so even if you’re a seasoned veteran T-SQL coder, at some time you will write a query that runs away and supersizes the tempdb database. This, in turn, might fill up your disk and cause other server-related problems for you. At that point, you may find out the hard way that shrinking tempdb isn’t like shrinking any other database.
Here are some tricks that I’ve tried successfully – but bear in mind that your mileage may vary.
Tempdb stores temporary tables as well as a lot of temporary (cached) information used to speed up queries and stored procedures. For the best chances in shrinking tempdb, we’re going to clear these different caches (except for the temp tables, which you should drop manually).
First off, the easy way out
It’s worth mentioning. If you’re not running a production-like environment, your best bet is to restart the SQL Server service. This will return tempdb to its default size, and you won’t have to worry about all the potential pitfalls of this article. But since you’re reading this, chances are you can’t just restart the server. So here goes:
Warning : These operations remove all kinds of caches, which will impact server performance to some degree until they’ve been rebuilt by the SQL Server. Don’t do this stuff unless absolutely neccessary.
DBCC DROPCLEANBUFFERS
Clears the clean buffers. This will flush cached indexes and data pages. You may want to run a CHECKPOINT command first, in order to flush everything to disk.
DBCC FREEPROCCACHE
Clears the procedure cache, which may free up some space in tempdb, although at the expense of your cached execution plans, which will need to be rebuilt the next time. This means that ad-hoc queries and stored procedures will have to recompile the next time you run them. Although this happens automatically, you may notice a significant performance decrease the first few times you run your procedures.
DBCC FREESYSTEMCACHE
This operation is similar to FREEPROCCACHE, except it affects other types of caches.
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache. This has to do with distributed queries (queries between servers), but I’m really not sure how much space they actually take up in tempdb.
.. and finally, DBCC SHRINKFILE
DBCC SHRINKFILE is the same tool used to shrink any database file, in tempdb or other databases. This is the step that actually frees the unallocated space from the database file.
Warning: Make sure you don’t have any open transactions when running DBCC SHRINKFILE. Open transactions may cause the DBCC operation to fail, and possibly corrupt your tempdb!
Don’t set the new size too low! Make a realistic estimate of the largest “normal” size that tempdb will assume during normal day-to-day operation.
That’s it. If everything works the way it should, you should now be able to verify the new size of tempdb.
A word about shrinking database files
Best practice is to try to minimize the use of file or database shrinking as much as possible. Whenever you shrink a database file and it re-grows later on, you are potentially creating fragmentation on your physical storage medium. This is because the sectors that the file used to occupy may now very well be occupied by other information (just a few bytes are enough). When SQL Server wants to grow that database file, the newly added portion of the file will need to be placed elsewhere on the disk, thus creating fragmentation.
The number one mortal sin in this context is “autoshrink“, because it may very well add to the drive fragmentation every time it runs, which could be very frequently.
As a rule of thumb, never ever autoshrink a database. And try to be very restrictive when it comes to shrinking databases or files in general, unless it’s a one-off operation to fix the aftermath of a runaway query.
Как сжать базу данных 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.
Уменьшение размера базы данных 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, включая проблемы с подсистемой ввода-вывода.
SQLGeeksPro
SQL Server DBA – Tips to Make it easier.
Remove or Delete Tempdb data file without restarting SQL Server
by Dev on September 11, 2017
You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.
You can run the below query to remove the data file:
Or you can use GUI:
SQL Server throws the following error:
The error message says that it cannot remove the file because it is not empty.
If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.
For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:
USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO
But what if it throws the below error:
The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.
Execute the below queries one by one and after clearing each, try to empty the file again:
The successful run shows as below: 
Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.
If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:
I hope you have already debunked the Myth around Remote Query Timeout option.