Dbcc shrinkdatabase: DBCC SHRINKDATABASE (Transact-SQL) — SQL Server

Содержание

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

Posted On 2018-08-23

Уменьшение размера базы данных Tempdb, способ 1

Этот способ требует перезапуска сервера SQL Server.

Остановите сервер 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%имя_экземпляра%).

Подключитесь к серверу SQL Server с помощью средства Query Analyzer и выполните следующие команды Transact-SQL:

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

Остановите сервер SQL Server путем нажатия в окне командной строки клавиш Ctrl-C, перезапустите SQL Server как службу и проверьте размер файлов Tempdb. mdf и Templog.ldf.

Ограничение этого способа заключается в том, что он работает только с логическими файлами базы данных 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 МБ свободного места после уменьшения размера базы данных.
Подключитесь к серверу SQL Server с помощью средства Query Analyzer и выполните следующие команды Transact-SQL:
```
   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 данной статьи.

Определите желаемый размер основного файла данных (tempdb.mdf), файла журнала (templog. ldf) и дополнительных файлов, добавленных к базе данных tempdb. Убедитесь в том, что используемое файлами место на диске меньше или равно желаемому размеру.

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

http://support.microsoft.com/kb/307487

База данных azure sql

. Почему база данных и файл сжатия dbcc не работают?

Хорошо, понял. Сокращение вашей базы данных является неправильным. Ты ненавидишь это. Но позвольте мне объяснить.

У меня есть рабочая база данных SQL Azure P1 объемом 1 ТБ с ~50 таблицами, где ~5 из них являются контейнерами JSON. Это был первоначальный дизайн, и я быстро осознал его ограничения, поэтому сейчас я нахожусь в процессе переноса хранилища этих JSON на более подходящую учетную запись хранения Azure.

Этот процесс займет время (JSON используются в разных бизнес-процессах, и я переношу по одному), поэтому в настоящее время я удаляю диапазоны строк после успешной миграции. Тем не менее, я не могу обрезать или удалить всю таблицу.

После переноса многих бизнес-процессов у меня осталось 868,64 ГБ выделенного пространства по сравнению с 390,82 ГБ используемого пространства. Конечно, я хотел бы уменьшить размер хранилища до уровня 400 ГБ, чтобы сократить расходы, но когда я пытаюсь сделать это с портала Azure, я получаю следующее сообщение об ошибке:

Размер хранилища вашей базы данных не может быть меньше текущего выделенного размера. Чтобы уменьшить размер базы данных, ей сначала необходимо освободить неиспользуемое пространство, выполнив команду DBCC SHRINKDATABASE () 9. 0013 . Обратите внимание, что эта операция может повлиять на производительность во время ее выполнения и может занять несколько часов.

Ладно, честно. Итак, я приступаю к выполнению команды (разумеется, с правильным именем базы данных), и через пару часов успешного выполнения ситуация точно такая же. Пространство не восстановлено.

После этого я приступил к следующим предварительным действиям:

Возможно, мне придется принудительно реорганизовать + усечение, поэтому я выполнил dbcc shrinkdatabase(, notruncate) , за которым следует dbcc shrinkdatabase(, truncateonly) : нет результатов.
Возможно, мне нужно сжать отдельные файлы, поэтому я выполнил dbcc shrinkfile() : все то же самое.
Возможно, мне нужно сжать файлы до определенного значения, поэтому я выполнил команду `dbcc shrinkfile(, ): снова безуспешно.

Этот запрос

 с
    [Базовые данные] как (
        выбирать
            [DF]. [type_desc] как [Тип],
            [DF].[имя] как [ИмяФайла],
            [DF].[размер] / 131072.0 как [TotalSpaceInGB],
            [UP].[размер] / 131072.0 как [UsedSpaceInGB],
            ([DF].[размер] - [UP].[размер]) / 131072.0 как [FreeSpaceInGB],
            [DF].[max_size] как [MaxSize]
        из [sys].[database_files] как [DF]
            перекрестное применение (
                выберите свойство файла ([DF]. [имя], 'spaceused') как [размер]
            ) как [ВВЕРХ]
    )
выбирать
    [BD].[Тип] как [Тип],
    [BD].[ИмяФайла] как [ИмяФайла],
    формат([BD].[TotalSpaceInGB], N'N2') как [TotalSpaceInGB],
    формат([BD].[UsedSpaceInGB], N'N2') как [UsedSpaceInGB],
    формат([BD].[FreeSpaceInGB], N'N2') как [FreeSpaceInGB],
    чехол [BD].[MaxSize]
        когда 0, то N'Disabled'
        когда -1, то N'Unrestricted'
        еще формат(([BD].[MaxSize] / 131072.0), N'N2')
    закончить как [MaxSizeInGB]
из [BaseData] как [BD]
порядок по [BD].[Type] asc, [BD].[FileName];

всегда возвращает один и тот же результат:

Введите	Имя файла	TotalSpaceInGB	Используется SpaceInGB	FreeSpaceInGB	Макссизеингб
ФАЙЛИСТОК	ХТП	2,03	НУЛЕВОЙ	НУЛЕВОЙ	Без ограничений
ЖУРНАЛ	журнал	1,63	0,60	1,03	250,00
РЯДЫ	данные_0	509,47	231,58	277,89	512. 00
РЯДЫ	dfa_data_3	359,17	159,27	199,91	512.00

Кроме того, этот запрос:

 с
    [Базовые данные] как (
        выбирать
            [TB].[object_id] как [ObjectId],
            max([PT].[строки]) как [RowCount],
            count(различный [IX].[index_id]) как [IndexCount],
            sum([PS].[used_page_count]) / 131072.0 как [UsedSpaceInGB],
            sum([PS].[reserved_page_count]) / 131072.0 как [ReservedSpaceInGB]
        из [sys].[схемы] как [SC]
            внутреннее соединение [sys].[tables] как [TB]
                на [SC].[schema_id] = [TB].[schema_id]
            внутреннее соединение [sys].[indexes] как [IX]
                на [ТБ].[object_id] = [IX].[object_id]
            внутреннее соединение [sys].[partitions] как [PT]
                на [TB].[object_id] = [PT].[object_id]
                    и [IX].[index_id] = [PT].[index_id]
            оставил соединение [sys]. [dm_db_index_usage_stats] как [IS]
                на [ТБ].[object_id] = [IS].[object_id]
                    и [IX].[index_id] = [IS].[index_id]
            левое соединение [sys].[dm_db_partition_stats] как [PS]
                на [PT].[partition_id] = [PS].[partition_id]
                    и [IX].[index_id] = [PS].[index_id]
                    и [TB].[object_id] = [PS].[object_id]
        сгруппировать по [TB].[object_id]
    )
выбрать топ 5
    [BD].[ObjectId] как [ObjectId],
    [BD].[RowCount] как [RowCount],
    [BD].[IndexCount] как [IndexCount],
    формат([BD].[UsedSpaceInGB], N'N2') как [UsedSpaceInGB],
    format([BD].[ReservedSpaceInGB], N'N2') как [ReservedSpaceInGB]
из [BaseData] как [BD]
упорядочить по [BD].[ReservedSpaceInGB] desc;

ясно показывает, что таблицы не занимают больше места, чем необходимо:

ObjectId	Количество строк	ИндексКоунт	Используется SpaceInGB	ЗарезервированоSpaceInGB
108579475	2892280	1	254,34	254,37
1952114095	834306760	1	79,73	79,74
418204640	20233590	1	23,52	23,53
1599396817	6346104	1	6,63	6,74
19395		596471	1	4,75	4,75

Я также сделал следующие выводы:

Я наткнулся на этот пост, объясняющий трюк с использованием файловых групп, но, насколько мне известно, управлять ими в базе данных SQL Azure невозможно.
Проблема может быть связана с тем, что я удалил много больших объектов. Я нашел команду dbcc forceghostcleanup (, 'visit_all_pages') , но не решаюсь ее попробовать.
Чтобы поэкспериментировать с командами dbcc, я создал клон базы данных из резервной копии. Я думаю, что это исключает любую возможную проблему, связанную с активными транзакциями, удерживающими версии строк из хранилища версий ускоренного восстановления базы данных.
В идеале я хотел бы избежать, насколько это возможно (использовать в крайнем случае) процесса копирования данных и удаления исходной таблицы или подобных вещей.

Большинство таблиц в базе данных представляют собой кластеризованные индексы rowstore, за исключением таблицы размером 6,63 ГБ, которая представляет собой кластеризованный индекс columnstore, и семи куч, размер которых не превышает 40 МБ, как выделенных, так и используемых. Все таблицы, подвергающиеся удалению, попадают в первую категорию, а также не имеют некластеризованных индексов.

Я только что попробовал DBCC UPDATEUSAGE , но это ничего не меняет; sp_spaceused возвращает те же значения.

У вас есть идеи?

`производительность - «DBCC SHRINKDATABASE» или не «DBCC SHRINKDATABASE»: вот в чем вопрос`

спросил 12 лет, 4 месяца назад

Изменено 4 года, 8 месяцев назад

Просмотрено 6к раз

Мы освобождаем много места в базе данных SQL Server 2008 R2 — оооочень! достаточно, чтобы заботиться о- (мы удаляем много ненужных данных). но файл базы данных сохраняет свой размер файла. мы хотим вернуть его.

Я много раз слышал, что использование DBCC SHRINKDATABASE снижает производительность ваших БД - Насколько я знаю, потому что "... Операция сжатия не сохраняет состояние фрагментации индексов в базе данных, и вообще увеличивает фрагментацию в определенной степени" [MSDN]

Поэтому я планирую использовать DBCC SHRINKDATABASE , а затем перестроить наши индексы.

`Есть еще одна причина не использовать`

DBCC SHRINKDATABASE для восстановления дискового пространства?

производительность
sql-server-2008
dbcc

1

Из блога Тибора Караси:

Так в чем проблема? Почему мне не следует регулярно сжимать файлы базы данных? Взгляните на приведенный ниже список, и тогда вы сможете определить для себя, хотите ли вы регулярно сжимать файлы базы данных:

Каждая перемещенная страница будет зарегистрирована в журнале транзакций. Допустим, у вас есть база данных с 50 ГБ используемого пространства (страницы данных и индексов), и сжатие перетасует 40 ГБ в начало файла. Файл журнала для этой операции сжатия потребует 40 ГБ, вероятно, автоматически увеличится до этого размера (если у вас уже нет 40 ГБ свободного места в файле журнала). Следующая резервная копия журнала будет иметь размер 40 ГБ плюс «обычные» записи журнала. Этого не происходит, если база данных находится в простом режиме восстановления, возможно, потому что CHECKPOINT будет регулярно сокращать журнал во время сжатия. (Применяется к сжатию файлов данных.)
После сжатия, когда пользователи добавляют строки и т. д. в базу данных, файл должен снова увеличиваться. Увеличение файла базы данных — дорогостоящая операция, требующая времени и снижающая производительность (используется много ресурсов). Кроме того, некоторые модификации будут заблокированы до завершения операции увеличения. (Применяется к сжатию файлов данных и журналов.)
SQL Server 2005 и более поздние версии: Что касается SQL Server 2005, у нас есть «мгновенная инициализация файлов», что означает, что файлы базы данных могут создаваться и также очень быстро расти, поскольку Windows не «обнуляет» данные в файле базы данных. Мгновенная инициализация файлов доступна только для файлов данных, но не для файлов журналов. Кроме того, для инициализации файла экземпляра требуется, чтобы учетная запись службы для службы SQL Server имела привилегию Windows SE_MANAGE_VOLUME_NAME, которую можно назначить с помощью политики безопасности «Выполнение задач обслуживания тома». По умолчанию это предоставляется только администраторам.
Бывают ситуации, когда автоматическое увеличение не «догоняет» требования к использованию пространства. Это приведет к сообщению об ошибке от SQL Server при выполнении модификации, которое будет возвращено клиентскому приложению: ошибка 1105, если данные заполнены, и 9002, если журнал заполнен. (Применяется к сжатию файлов данных и журналов.)
Перемещение страниц данных приведет к фрагментации вашей базы данных. Скажем, вы перестраиваете свои индексы (для чего потребуется свободное место в базе данных), а затем сжимаете базу данных. Сокращение по существу отменит перестроение индекса, оставив вас с фрагментированными индексами. Не верите мне? Это легко проверить на себе.
Что если сделать наоборот, сначала уменьшить, а потом перестроить? Что ж, для перестроения требуется свободное место в базе данных для самого большого индекса, который вы перестраиваете, и, вероятно, у вас есть большая таблица с кластеризованным индексом.