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

Автоматическое сжатие базы данных — КиберПедия


Навигация:



Главная
Случайная страница
Обратная связь
ТОП
Интересно знать
Избранные



Топ:

Методика измерений сопротивления растеканию тока анодного заземления: Анодный заземлитель (анод) – проводник, погруженный в электролитическую среду (грунт, раствор электролита) и подключенный к положительному…

Выпускная квалификационная работа: Основная часть ВКР, как правило, состоит из двух-трех глав, каждая из которых, в свою очередь…

Генеалогическое древо Султанов Османской империи: Османские правители, вначале, будучи еще бейлербеями Анатолии, женились на дочерях византийских императоров…


Интересное:

Уполаживание и террасирование склонов: Если глубина оврага более 5 м необходимо устройство берм. Варианты использования оврагов для градостроительных целей…

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

Лечение прогрессирующих форм рака: Одним из наиболее важных достижений экспериментальной химиотерапии опухолей, начатой в 60-х и реализованной в 70-х годах, является…



Дисциплины:


Автоматизация Антропология Археология Архитектура Аудит Биология Бухгалтерия Военная наука Генетика География Геология Демография Журналистика Зоология Иностранные языки Информатика Искусство История Кинематография Компьютеризация Кораблестроение Кулинария Культура Лексикология Лингвистика Литература Логика Маркетинг Математика Машиностроение Медицина Менеджмент Металлургия Метрология Механика Музыкология Науковедение Образование Охрана Труда Педагогика Политология Правоотношение Предпринимательство Приборостроение Программирование Производство Промышленность Психология Радиосвязь Религия Риторика Социология Спорт Стандартизация Статистика Строительство Теология Технологии Торговля Транспорт Фармакология Физика Физиология Философия Финансы Химия Хозяйство Черчение Экология Экономика Электроника Энергетика Юриспруденция




⇐ ПредыдущаяСтр 19 из 23Следующая ⇒

Если параметр базы данных AUTO_SHRINK установлен в значение ON, компонент Database Engine автоматически сжимает базы данных, в которых имеется свободное место. Этот параметр настраивается с помощью инструкции ALTER DATABASE. По умолчанию этот параметр имеет значение OFF. Компонент Database Engine периодически проверяет использование дискового пространства в каждой базе данных. Если параметру AUTO_SHRINK в базе данных присвоено значение ON, компонент Database Engine уменьшает размер файлов этой базы данных. Эта операция выполняется в фоновом режиме и не влияет на действия пользователя в базе данных.

Задание режима автоматического сжатия базы данных

ALTER DATABASE (Transact-SQL)

Сжатие базы данных вручную

Базу данных или ее файлы можно сжимать вручную с помощью инструкции DBCC SHRINKDATABASE или DBCC SHRINKFILE. Если инструкция DBCC SHRINKDATABASE или DBCC SHRINKFILE не может зарезервировать все заданное дисковое пространство в файле журнала, должно быть выдано информационное сообщение с указанием того действия, которое следует выполнить для обеспечения возможности освобождения дискового пространства. Дополнительные сведения о сжатии файлов журнала см. в разделе Сжатие журнала транзакций.

Операции DBCC SHRINKDATABASE и DBCC SHRINKFILE могут быть остановлены на любом этапе процесса, при этом вся выполненная работа сохраняется.

С помощью инструкции DBCC SHRINKDATABASE нельзя сжимать всю базу данных до размера, который меньше исходного. Поэтому, если база данных была создана с размером 10 МБ и потом увеличилась до 100 МБ, ее можно сжать только до 10 МБ, даже если все данные удалены из базы данных.

Однако с помощью инструкции DBCC SHRINKFILE можно сжимать отдельные файлы базы данных до размера, который меньше начального. При этом каждый файл следует сжимать по отдельности.

Примечание
Нельзя сжимать базу данных или журнал транзакций при их резервировании. С другой стороны, нельзя создавать резервную копию базы данных или журнала транзакций при их сжатии.

Сжатие базы данных

· DBCC SHRINKDATABASE (Transact-SQL)

· Как сжать базу данных (среда SQL Server Management Studio)

Сжатие файла данных или файла журнала

· DBCC SHRINKFILE (Transact-SQL)

· Как сжать файл (среда SQL Server Management Studio)

Сжатие журнала транзакций

Предусмотрены фиксированные крайние значения, до которых можно сжимать файл журнала транзакций. Размер виртуальных файлов журнала в журнале определяет пределы сжатия. Поэтому файл журнала нельзя сжать до размера, меньшего, чем размер виртуального файла журнала. Кроме того, уменьшать объем файла журнала можно только с шагом, равным размеру виртуального файла журнала. Например, файл журнала транзакций размером 1 ГБ может состоять из пяти виртуальных файлов журнала по 200 МБ каждый. При сжатии файла журнала транзакций неиспользуемые виртуальные файлы журнала удаляются, однако должно остаться как минимум два таких файла. Поскольку каждый виртуальный файл журнала в данном примере занимает 200 МБ, журнал транзакций можно сжать не менее чем до 400 МБ с шагом в 200 МБ. Чтобы иметь возможность сильнее сжать файл журнала транзакций, следует создать небольшой журнал и задать автоматический режим увеличения его размера вместо того, чтобы сразу создавать объемный файл журнала транзакций.

Команды DBCC SHRINKDATABASE или DBCC SHRINKFILE пытаются немедленно уменьшить размер файла журнала транзакций до требуемого (после округления). Перед сжатием следует выполнить резервирование файла журнала, чтобы уменьшить размер логического журнала, а также пометить как неактивные все виртуальные журналы, которые не содержат какой-либо части логического журнала. Дополнительные сведения см. в разделе Сжатие журнала транзакций.

Рекомендации

При планировании сжатия базы данных или файла следует учитывать следующие соображения.

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

· Большинству баз данных для выполнения обычных ежедневных операций требуется некоторый объем свободного места. Если сжатие базы данных производится регулярно, но она снова увеличивается в размерах, это означает, что освобожденное при сжатии место требуется для регулярных операций. В таких случаях повторное сжатие базы данных бессмысленно.

· Операция сжатия базы данных не избавляет от фрагментации индексов, а даже наоборот, обычно еще более увеличивает ее. Например, не следует сжимать базу данных или файл после перестройки индексов. Это еще одна причина отказаться от повторного сжатия базы данных.

· Не следует устанавливать параметр базы данных AUTO_SHRINK в значение ON без достаточных на то оснований.


⇐ Предыдущая14151617181920212223Следующая ⇒

Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций…

Опора деревянной одностоечной и способы укрепление угловых опор: Опоры ВЛ — конструкции, предназначен­ные для поддерживания проводов на необходимой высоте над землей, водой…

Поперечные профили набережных и береговой полосы: На городских территориях берегоукрепление проектируют с учетом технических и экономических требований, но особое значение придают эстетическим…

Кормораздатчик мобильный электрифицированный: схема и процесс работы устройства…



Сжатие базы данных и журнала транзакций в Microsoft SQL Server | Info-Comp.

ru

Многие администраторы Microsoft SQL Server сталкивались с проблемой значительного увеличения физического размера базы данных и файлов журнала транзакций и, конечно же, им хотелось бы каким-то образом уменьшить этот размер, для того чтобы не предпринимать какие-либо действия, связанные с увеличением свободного пространства на жестком диске. Способ уменьшить физический размер базы данных и файлов журнала транзакций в SQL сервере есть – это сжатие.

Содержание

  1. Что такое сжатие в Microsoft SQL Server?
  2. Как сжать базу данных в MS SQL Server?
  3. Сжимаем базу данных с помощью среды Management Studio
  4. Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE
  5. Рекомендации и важные моменты при сжатии базы данных

Что такое сжатие в Microsoft SQL Server?

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

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

Наибольший эффект от сжатия достигается тогда, когда операция сжатия выполняется после операции удаления таблиц из БД или удаления данных из таблиц.

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

Усечение журнала транзакций происходит автоматически:

  • В простой модели восстановления — после достижения контрольной точки, которая может возникнуть, например, после создания BACKUP базы данных, при явном выполнении инструкции CHECKPOINT, или тогда когда размер логического журнала транзакций заполняется на 70 процентов, во всех этих случаях происходит автоматическая очистка неактивной части журнала, т.е. его усечение;
  • В модели полного восстановления или в модели восстановления с неполным протоколированием — после создания резервной копии журнала при условии, что с момента создания последней резервной копии журнала была достигнута контрольная точка.

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

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

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

Как сжать базу данных в MS SQL Server?

Сжать файлы базы данных и журнала транзакций можно и с помощью графического интерфейса Management Studio и с помощью инструкций Transact-SQL: DBCC SHRINKDATABASE и DBCC SHRINKFILE. Также возможно настроить базу данных на автоматическое сжатие путем выставления параметра БД AUTO_SHRINK в значение ON.

Примечание! Сжатие базы данных я буду рассматривать на примере Microsoft SQL Server 2016 Express.

Сжимаем базу данных с помощью среды Management Studio

Запускаем Management Studio и в обозревателе объектов открываем объект «Базы данных». Затем щелкаем правой кнопкой мыши по БД, которую необходимо сжать, далее выбираем «Задачи ->Сжать -> База данных (или Файлы, если, например, нужно сжать только журнал транзакций)». Я для примера выбираю «База данных».


В итоге у Вас откроется окно «Сжатие базы данных», в котором Вы, кстати, можете наблюдать размер базы данных, а также доступное свободное место, которое можно удалить (т.е. сжать). Нажимаем «ОК».

Через некоторое время, в зависимости от размера базы данных, сжатие будет завершено.

Сжимаем базу данных с помощью инструкций SHRINKDATABASE и SHRINKFILE

В MS SQL Server для выполнения сжатия файлов базы данных и журнала транзакций существуют две инструкции SHRINKDATABASE и SHRINKFILE.

  • DBCC SHRINKDATABASE – это команда для сжатия базы данных;
  • DBCC SHRINKFILE – с помощью данной команды можно выполнить сжатие некоторых файлов базы данных (например, только журнала транзакций).

Для того чтобы выполнить сжатие БД (например, TestBase) точно также как мы это сделали чуть ранее в Management Studio, выполните следующую инструкцию.

  
  DBCC SHRINKDATABASE(N'TestBase')


SHRINKDATABASE имеет следующие параметры:

  • database_name или database_id — имя или идентификатор базы данных, которую необходимо сжать. Если указать значение 0, то будет использоваться текущая база данных;
  • target_percent – свободное пространство в процентах, которое должно остаться в базе данных после сжатия;
  • NOTRUNCATE — сжимает данные в файлах с помощью перемещения распределенных страниц из конца файла на место нераспределенных страниц в начале файла. Если указан данный параметр, физический размер файла не изменяется;
  • TRUNCATEONLY — освобождает все свободное пространство в конце файла операционной системе, но не перемещает страницы внутри файла. Файл данных сокращается только до последнего выделенного экстента. Если указан данный параметр, то параметр target_percent не обрабатывается;
  • WITH NO_INFOMSGS — подавляет все информационные сообщения со степенями серьезности от 0 до 10.

Синтаксис SHRINKDATABASE

  
  DBCC SHRINKDATABASE   
  ( database_name | database_id | 0   
       [ , target_percent ]   
       [ , { NOTRUNCATE | TRUNCATEONLY } ]   
  )  
  [ WITH NO_INFOMSGS ]  


Для того чтобы сжать только журнал транзакций можно использовать инструкцию SHRINKFILE, например.

  
  DBCC SHRINKFILE (N'TestBase_log')


В данном случае мы осуществим сжатие файла журнала (TestBase_log – это название файла журнала транзакций), до его начального значения, т.е. до значения по умолчанию. Для того чтобы сжать файл до определенного размера, укажите вторым параметром размер в мегабайтах. Например, следующей инструкцией мы уменьшим размер файла журнала транзакций до 5 мегабайт.

  
  DBCC SHRINKFILE (N'TestBase_log' , 5)


Также необходимо учесть, что если Вы укажете размер меньше того, чем требуется для хранения данных в файле, то файл до этого размера сжат не будет. Например, допустим, если Вы указали 5 мегабайт, а для хранения данных в файле требуется 7 мегабайт, файл будет сжат только до 7 мегабайт.

SHRINKFILE также имеет параметры NOTRUNCATE и TRUNCATEONLY.

Синтаксис SHRINKFILE

  
  DBCC SHRINKFILE   
  (  
      { file_name | file_id }   
      { [ , EMPTYFILE ]   
      | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
      }  
  )  
  [ WITH NO_INFOMSGS ] 


Рекомендации и важные моменты при сжатии базы данных

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

На этом у меня все, надеюсь, статья была Вам полезна, удачи!

База данных 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` возвращает те же значения.

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

sql server - Как быстро сжать все файлы для всех баз данных?

спросил

Изменено
3 года, 7 месяцев назад

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

Как в SQL Server (в данном случае 2008) быстро сжать все файлы, журналы и данные, для всех баз данных на экземпляре? Я мог бы пройти через SSMS, щелкнуть правой кнопкой мыши и выбрать «Задачи» -> «Сжать», но я ищу что-то более быстрое.

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

  • sql-server
  • sql-server-2008
  • сжать

Когда вы выполняете «Задачи -> Сжать» из графического интерфейса, он фактически выдает команду DBCC SHRINKDATABASE за кулисами. Попытайся. Когда появится диалоговое окно, не нажимайте кнопку «ОК». Вместо этого нажмите кнопку «Сценарий». Вы увидите команду в окне запроса. Объедините это с запросом к sys.databases (без учета master и msdb), и вы сможете создать сценарий для сжатия всех баз данных.

Например (взято из комментария jcolebrand):

 SELECT
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, ТОЛЬКО ОБРЕЗАТЬ)'
    + СИМВОЛ(13) + СИМВОЛ(10) + СИМВОЛ(13) + СИМВОЛ(10)
ИЗ
         sys.master_files мф
    ПРИСОЕДИНЯЙТЕСЬ к sys.databases d
        ON mf.database_id = d.database_id
ГДЕ d.database_id > 4;
 

Скопируйте результат этого запроса и запустите его, чтобы сжать все ваши файлы.

7

Как насчет одной строки инструкции sql?

Пожалуйста, прочтите этот очень интересный пост в блоге, прежде чем выполнять следующую инструкцию sql.

 EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'
 

2

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

Гораздо более быстрый способ сжать файл базы данных:

  • Выделить новую файловую группу для базы данных
  • Сделайте эту файловую группу настолько большой, насколько она должна быть (используйте sp_spaceused , чтобы определить, насколько большой)
  • Перестроить все индексы в эту новую файловую группу
  • Удалить старую файловую группу

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

Этот метод также имеет дополнительное преимущество дефрагментации ваших индексов в процессе.

3

Я немного подправил запрос, чтобы уменьшить только ЖУРНАЛ по мере его запроса:

 set nocount on
ВЫБРАТЬ
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10)
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, ТОЛЬКО ОБРЕЗАТЬ)'
    + СИМВОЛ(13) + СИМВОЛ(10) + СИМВОЛ(13) + СИМВОЛ(10)
ИЗ
         sys.master_files мф
    ПРИСОЕДИНЯЙТЕСЬ к sys.databases d
        ON mf.database_id = d.database_id
ГДЕ d.database_id > 4 и mf.type_desc = 'LOG'
 

2

Приведенный ниже код позволяет получить список несистемных баз данных, установить базу данных только для чтения, а затем сжать файл. Я сохранил этот код в нескольких ящиках SQL Server, используя задание агента SQL, где пространство всегда является проблемой. Каждую неделю в субботу/воскресенье он запускается и сокращает все базы данных в течение нескольких часов (в зависимости от размера баз данных).

 объявить @db varchar(255)
объявить c курсор для
выберите имя из sys.databases, где is_read_only=0 и state=0
  и имя не в ('мастер','модель','tempdb','msdb')
открыть с
извлечь c в @db
в то время как @@fetch_status=0
начинать
  exec SP_dboption @db,'trunc. войти в chkpt.','true'
  База данных сжатия DBCC (@db)
  выбрать следующий из c в @db
конец
близко с
освободить c
 

0

Мы можем динамически повторять SHRINKDB и SHRINKFILE для всех баз данных:

, в то время как @DBID<[email protected]
начинать
  -- Используется динамический SQL для всех баз данных.
  Установите @SQL = 'Использовать '[email protected]+ ' '+Char(10)
  Установить @SQL += 'DBCC SHRINKFILE('[email protected]+',5)' +Char(10)
  Установить @SQL += 'DBCC SHRINKDATABASE('[email protected]+')'+Char(10)
  --#6 Увеличить DBid для перебора всех баз данных
  Установить @DBID = @DBID+1
  Выберите @DBName = DBName, @Filename=DBFileName из #DBNames, где [dbid] = @DBID и type_Desc = 'LOG'
  Распечатать (@SQL)
  Исполнитель (@SQL)
конец
 

Подробности в этой статье.

Уменьшить все файлы журналов, кроме master, model, msdb:

 EXEC sp_MSforeachdb '
DECLARE @sqlcommand nvarchar (500)
ЕСЛИ ''?'' НЕ В (''мастер'', ''модель'', ''msdb'')
НАЧИНАТЬ
ИСПОЛЬЗОВАТЬ [?]
ВЫБЕРИТЕ @sqlcommand = ''DBCC SHRINKFILE (N'''''' +
имя
ИЗ [sys].[database_files]
ГДЕ type_desc = ''ЖУРНАЛ''
ВЫБЕРИТЕ @sqlcommand = @sqlcommand + '''''' , 0)''
EXEC sp_executesql @sqlcommand
КОНЕЦ'
 

Этот ответ расширяет приведенный выше ответ, используя курсор для итерации операторов SQL один за другим. Он не такой короткий, как ответ Эмры, но допускает дополнительную логику в цикле while внутри курсора..

 ВЫБОР
'ИСПОЛЬЗОВАТЬ ['
+ базы данных.имя + N']'
+ СИМВОЛ(13)
+ СИМВОЛ(10)
+ 'DBCC SHRINKFILE (N'''
+ masterFiles.имя
+ N''' , 0, ТОЛЬКО ОБРЕЗАТЬ)'
+ СИМВОЛ(13)
+ СИМВОЛ(10)
+ СИМВОЛ(13)
+ CHAR(10) КАК sqlCommand
В
#shrinkCommands
ИЗ
[sys].[master_files] мастер-файлы
INNER JOIN [sys].[databases] базы данных ON masterFiles.

Imacros | Все права защищены © 2021