Sql шринк базы: Сжатие базы данных — SQL Server

Уменьшение файла транзакций. DBCC SHRINKFILE (Transact-SQL) : РБ-Софт

DBCC SHRINKFILE (Transact-SQL)

SQL Server 2012

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

 Синтаксические обозначения в Transact-SQL

Синтаксис

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

Аргументы

• file_name
• Логическое имя файла, предназначенного для сжатия.
• file_id
• Идентификационный номер (идентификатор) файла, предназначенного для сжатия. Для получения идентификатора файла используйте функцию FILE_IDEX или выполните запрос к представлению каталога sys.database_files текущей базы данных.
• target_size
• Размер файла (в мегабайтах), выражаемый целым числом. Если он не указан, то инструкция DBCC SHRINKFILE уменьшает файл до размера файла по умолчанию. Размер по умолчанию представляет собой размер, указанный в момент создания файла. ПримечаниеМожно уменьшить размер пустого файла, заданный по умолчанию, с помощью инструкции DBCC SHRINKFILE target_size. Например, при создании файла с размером 5 МБ и последующем уменьшении размера до 3 МБ, в то время как файл остается пустым, размер файла по умолчанию задается равным 3 МБ. Это правило применимо только к пустым файлам, в которых никогда не содержались данные. Этот параметр не поддерживается для контейнеров файловых групп FILESTREAM.Если аргумент target_size указан, то инструкция DBCC SHRINKFILE пытается сжать файл до заданного размера. Используемые страницы в освобождаемой части файла перемещаются в свободное место сохраняемой части файла.  Например, если размер файла данных составляет 10 МБ, инструкция DBCC SHRINKFILE со значением аргумента target_size, равным 8, перемещает все страницы, используемые в последних 2 МБ файла, на место любых нераспределенных страниц в первых 8 МБ файла.Инструкция DBCC SHRINKFILE не сжимает файл до меньшего размера, чем требуется для хранения данных в файле. Например, если для файла данных, размер которого составляет 10 МБ, необходимо сжатие до 7 МБ, инструкция DBCC SHRINKFILE со значением аргумента target_size, равным 6, сжимает файл до размера в 7 МБ, а не 6 МБ.
• EMPTYFILE
• Выполняет миграцию всех данных из указанного файла в другие файлы в той же файловой группе. Поскольку компонент Компонент Database Engine больше не разрешает размещать данные в пустом файле, этот файл может быть удален инструкцией ALTER DATABASE.Для контейнеров файловых групп FILESTREAM файл нельзя удалить с помощью ALTER DATABASE до тех пор, пока сборщик мусора FILESTREAM не выполнит и не удалит все ненужные файлы контейнеров файловых групп, которые были скопированы в другой контейнер с помощью EMPTYFILE. Дополнительные сведения см. в разделе sp_filestream_force_garbage_collection (Transact-SQL). ПримечаниеСведения об удалении контейнера FILESTREAM см. в соответствующем разделе в Параметры инструкции ALTER DATABASE для файлов и файловых групп (Transact-SQL). 
• NOTRUNCATE
• Перемещает распределенные страницы из конца файла на место нераспределенных страниц в начале файла с параметром target_percent или без него. Свободное место в конце файла операционной системе не возвращается, и физический размер файла не изменяется. Следовательно, если указан аргумент NOTRUNCATE, файл сжимается незначительно.Аргумент NOTRUNCATE применим только к файлам данных. На файлы журнала он не влияет.Этот параметр не поддерживается для контейнеров файловых групп FILESTREAM.
• TRUNCATEONLY
• Освобождает все свободное пространство в конце файла операционной системе, но не перемещает страницы внутри файла.  Файл данных сокращается только до последнего выделенного экстента.Аргумент target_size не обрабатывается, если указан аргумент TRUNCATEONLY.Аргумент TRUNCATEONLY применим только к файлам данных. Этот параметр не поддерживается для контейнеров файловых групп FILESTREAM.
• WITH NO_INFOMSGS
• Подавляет вывод всех информационных сообщений.

Результирующие наборы

В следующей таблице отображены столбцы результирующего набора.

 Замечания

Инструкция DBCC SHRINKFILE применяется к файлам в текущей базе данных. Дополнительные сведения об изменении текущей базы данных см. в разделе USE (Transact-SQL).

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

В случае сбоя операции DBCC SHRINKFILE возникает ошибка.

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

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

Для файла журнала компонент Компонент Database Engine использует аргумент target_size для вычисления целевого размера всего журнала. Поэтому аргумент target_size является количеством свободного пространства в журнале после операции сжатия.Затем по заданному размеру всего журнала рассчитываются заданные размеры каждого файла журнала. Инструкция DBCC SHRINKFILE сразу же пытается сжать каждый физический файл журнала до намеченного размера. Однако если часть логического журнала хранится в виртуальных журналах за пределами заданного размера, то компонент Компонент Database Engine освобождает как можно больше места, а затем формирует информационное сообщение. Сообщение описывает действия, которые необходимо предпринять, чтобы переместить логический журнал из виртуальных журналов в конец файла. После выполнения всех действий инструкция DBCC SHRINKFILE может быть использована для освобождения оставшегося пространства.

Так как файл журнала может быть сжат только до границы виртуального файла журнала, сжатие файла журнала к размеру, меньшему, чем размер виртуального файла журнала, невозможно, даже если он не используется. Размер виртуального файла журнала динамически выбирается компонентом Компонент Database Engine при создании или расширении файлов журнала.

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

Примите во внимание следующие сведения при планировании сжатия файла.

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

Устранение неполадок

Этот раздел описывает методы диагностики и устранения проблем, которые могут произойти при выполнении команды DBCC SHRINKFILE:

Файл не удалось сжать

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

• Выполните следующий запрос.  
  SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;
• Выполните команду DBCC SQLPERF, чтобы освободить пространство, используемое журналом транзакций.

Если свободного пространства недостаточно, операция сжатия не сможет уменьшить размер файла в дальнейшем.

Обычно это файл журнала, который сжимается незначительно. Это характерно для файла журнала, который не был усечен. Можно усечь файл журнала, установив значение SIMPLE для модели восстановления базы данных или создав резервную копию журнала, а затем выполнив операцию DBCC SHRINKFILE снова.

Операция сжатия заблокирована

Операции сжатия могут быть блокированы транзакцией, запущенной с уровнем изоляции, основанным на управлении версиями строк. Например, если при выполнении масштабной операции удаления с уровнем изоляции, основанном на управлении версиями строк, выполнить инструкцию DBCC SHRINK DATABASE, то, прежде чем приступить к сжатию файлов, она будет ожидать завершения операции удаления. В этом случае операции DBCC SHRINKFILE и DBCC SHRINKDATABASE выводят информационное сообщение (5202 для SHRINKDATABASE и 5203 для SHRINKFILE) в журнал ошибок SQL Server каждые 5 минут в течение первого часа, а затем по одному сообщению каждый час.  Например, если журнал ошибок содержит следующее сообщение об ошибке, произойдет следующая ошибка.

DBCC SHRINKFILE for file ID 1 is waiting for the snapshot 
transaction with timestamp 15 and other snapshot transactions linked to 
timestamp 15 or with timestamps older than 109 to finish.

Это означает, что операция сжатия блокируется транзакциями моментального снимка, которые имеют отметки времени старше, чем метка 109, представляющая последнюю транзакцию, завершающую операцию сжатия. Это также показывает, что столбцыtransaction_sequence_num или first_snapshot_sequence_num в динамическом представлении управления sys.dm_tran_active_snapshot_database_transactions содержат значение 15. Если столбцы transaction_sequence_num или first_snapshot_sequence_num в представлении содержат меньшее число, чем последняя транзакция, выполненная операцией сжатия (109), то операция сжатия будет ждать завершения этих транзакций.

Разрешить эту проблему можно одним из следующих способов.

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

sql-docs.ru-ru/shrink-database-task-maintenance-plan.md at live · MicrosoftDocs/sql-docs.ru-ru · GitHub

[!INCLUDE SQL Server]
Диалоговое окно Задача «Сжатие базы данных» используется для создания задачи, которая пытается уменьшить размер выбранных баз данных. Перечисленные ниже параметры используются для определения количества неиспользуемого пространства, которое должно остаться в базе данных после сжатия (чем больше процент, тем меньше сжимается база данных). Это значение определяется долей фактических данных в базе данных. Например: 100-мегабайтная база данных, содержащая 60 МБ данных и 40 МБ свободного пространства с заданным значением свободного пространства, равным 50 процентам, будет содержать 60 МБ данных и 30 МБ свободного пространства (поскольку 50 процентов от 60 МБ равно 30 МБ). Удаляется только лишнее пространство в базе данных. Допустимые значения: от 0 до 100.

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

[!WARNING]
Данные, перемещаемые в процессе сжатия файла, могут быть разбросаны по любым доступным местам в файле. Это вызывает фрагментацию индекса и может увеличить время выполнения запросов, выполняющих поиск в диапазоне индекса. Чтобы устранить фрагментацию, предусмотрите возможность перестроения индексов файла после сжатия.

Эта задача выполняет инструкцию DBCC SHRINKDATABASE.

Параметры

Соединение
Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.

Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.

Базы данных
Укажите базы данных, для которых должна выполняться эта задача.

• Все базы данных

  Позволяет сформировать план обслуживания, который запускает задачи обслуживания для всех баз данных [!INCLUDEmsCoName] [!INCLUDEssNoVersion], кроме tempdb.

• Все системные базы данных

  Будет сформирован план обслуживания, запускающий задачи обслуживания для каждой системной базы данных [!INCLUDEssNoVersion] , за исключением базы данных tempdb. Для баз данных, созданных пользователями, задачи обслуживания выполняться не будут.

• Все пользовательские базы данных

  Создается план обслуживания, по которому задачи обслуживания выполняются для всех баз данных, созданных пользователем. Для системных баз данных [!INCLUDEssNoVersion] задачи обслуживания выполняться не будут.

• Следующие базы данных

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

  [!NOTE]
  Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.

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

Объем свободного места после сжатия
Прекратить сжатие по достижении заданного размера свободного пространства в базе данных.

Просмотр T-SQL
Просмотрите инструкции [!INCLUDEtsql] , выполняемые для данной задачи по отношению к серверу, на основе выбранных параметров.

[!NOTE]
Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

Диалоговое окно «Создание соединения»

Имя соединения
Введите имя нового соединения.

Выберите или введите имя сервера
Выберите сервер для подключения при выполнении этой задачи.

Обновить
Обновите список доступных серверов.

Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.

Использовать встроенную систему безопасности Windows NT
Подключиться к экземпляру компонента [!INCLUDEssNoVersion] [!INCLUDEssDE] c проверкой подлинности [!INCLUDEmsCoName] Windows.

Использовать указанные имя пользователя и пароль
Подключиться к экземпляру компонента [!INCLUDEssNoVersion] [!INCLUDEssDE] с использованием проверки подлинности [!INCLUDEssNoVersion]. Этот параметр недоступен.

User name
Укажите имя входа, используемое при проверке подлинности [!INCLUDEssNoVersion] . Этот параметр недоступен.

Пароль
Укажите используемый при проверке подлинности пароль. Этот параметр недоступен.

См. также:

DBCC SHRINKDATABASE (Transact-SQL)

Заметки сисадмина

Отвечаете ли вы за единственный ПК с Windows 10, или за тысячи, трудности с управлением обновлениями у вас одни и те же. Ваша цель – быстро устанавливать обновления, связанные с безопасностью, по-умному работать с обновлениями компонентов, и предотвращать падение продуктивности из-за неожиданных перезагрузок

Есть ли у вашего предприятия всеобъемлющий план работы с обновлениями Windows 10? Есть соблазн считать эти скачивания периодическими помехами, от которых нужно избавляться сразу, как только они появляются. Однако реактивный подход к обновлениям – это рецепт разочарований и снижения продуктивности.

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

В статье указана вся информация, необходимая для понимания того, как Microsoft отправляет обновления на устройства под управлением Windows 10, а также детали по поводу инструментов и техник, которые можно использовать для умного управления этими обновлениями на устройствах под управлением Windows 10 версий Pro, Enterprise или Education. (Windows 10 Home поддерживает лишь самые базовые возможности управления обновлениями и непригодна для использования в бизнес-среде).

Но перед тем, как перейти к любому из этих инструментов, вам понадобится план.

Что написано в ваших правилах обновления?

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

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

Правила должны описывать несколько различных типов обновлений. Наиболее понятный тип – ежемесячные кумулятивные обновления безопасности и надёжности, которые выходят во второй вторник каждого месяца («вторник патчей»). В этом релизе обычно присутствует Windows Malicious Software Removal Tool, а также могут быть и любые из следующих типов обновлений:

• Обновления безопасности для . NET Framework
• Обновления безопасности для Adobe Flash Player
• Обновления стека обслуживания (которые нужно устанавливать с самого начала).

Установку любого из этих обновлений можно отложить на срок до 30 дней.

В зависимости от производителя ПК, драйверы оборудования и прошивки тоже могут распространяться по каналу Windows Update. Можно отказаться от этого или же управляться с ними по тем же схемам, что и с другими обновлениями.

Наконец, через Windows Update распространяются и обновления компонентов [feature updates]. Эти крупные пакеты обновляют Windows 10 до последней версии, и выходят каждые шесть месяцев для всех редакций Windows 10, кроме долгосрочного канала обслуживания Long Term Servicing Channel (LTSC). Отложить установку обновлений компонентов можно при помощи Windows Update for Business на срок до 365 дней; для редакций Enterprise и Education возможна дальнейшая отсрочка установки на срок до 30 месяцев.

Учитывая всё это, можно начинать составлять правила обновлений, куда должны входить следующие элементы для каждого из обслуживаемых ПК:

• Срок установки ежемесячных обновлений. По умолчанию в Windows 10 ежемесячные обновления скачиваются и устанавливаются в течение 24 часов после их выхода во «вторник патчей». Можно откладывать скачивание этих обновлений для некоторых или всех ПК в компании, чтобы у вас было время проверить их на совместимость; эта задержка также позволяет вам избежать проблем в случае, когда Microsoft обнаруживает проблему с обновлением после выхода, как это уже много раз случалось с Windows 10.
• Срок установки полугодовых обновлений компонентов. При настройках по умолчанию обновления компонентов скачиваются и устанавливаются тогда, когда Microsoft считает, что они готовы. На устройстве, которое Microsoft посчитали подходящим для обновления, обновления компонентов могут появиться через несколько дней после выхода. На других устройствах обновления компонентов могут появиться через несколько месяцев, или их вообще могут заблокировать из-за проблем с совместимостью. Можно установить задержку для некоторых или для всех ПК в организации, чтобы получить время на проверку нового релиза. Начиная с версии 1903, пользователям ПК предложат обновления компонентов, однако команды на скачивание и установку их будут давать только сами пользователи.
• Когда разрешать ПК перезапускаться для завершения установки обновлений: большая часть обновлений требует перезапуска для завершения установки. Этот перезапуск происходит вне промежутка «периода активности» с 8 до 17 часов; эту настройку можно поменять по желанию, продлив длительность интервала до 18 часов. Инструменты управления позволяют назначить определённое время для скачивания и установки обновлений.
• Как уведомлять пользователей о наличии обновлений и перезапуске: во избежание неприятных сюрпризов, Windows 10 уведомляет пользователей о наличии обновлений. Управление этими уведомлениями в настройках Windows 10 ограничено. Гораздо больше настроек доступно в «групповых политиках».
• Иногда Microsoft выпускает критически важные обновления безопасности вне обычного графика «вторников патчей». Обычно это нужно для исправления недочётов в безопасности, которыми злонамеренно пользуются третьи лица. Ускорять ли применение таких обновлений или ждать следующего окна в графике?
• Что делать с неудачными обновлениями: если обновлению не удалось встать правильно, или оно вызывает проблемы, что вы будете делать в этом случае?

Определив эти элементы, пора выбрать инструменты для работы с обновлениями.

Ручное управление обновлениями

На совсем малых предприятиях, включая магазины с единственным работником, довольно легко осуществить ручную настройку обновлений Windows. Параметры > Обновление и безопасность > Центр обновления Windows. Там можно подправить две группы настроек.

Сначала выберите «Изменить период активности» и подправьте настройки, чтобы они соответствовали вашим рабочим привычкам. Если вы обычно работаете по вечерам, можно избежать простоя, настроив эти значения с 18 до полуночи, в результате чего запланированные перезапуски будут происходить по утрам.

Затем выберите «Дополнительные параметры» и настройку «Выберите, когда устанавливать обновления», прописав её в соответствии с вашими правилами:

• Выберите, на сколько дней задерживать установку обновлений компонентов. Максимальное значение – 365.
• Выберите, на сколько дней задерживать установку обновлений качества, включая кумулятивные обновления безопасности, выходящие по «вторникам патчей». Максимальное значение – 30 дней.

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

До версии Windows 10 1903 там была ещё настройка выбора канала – полугодового, или же целевого полугодового. Её убрали в версии 1903, а в более старых версиях она просто не работает.

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

Управление обновлениями через Групповые политики

Все упомянутые ручные настройки можно применять и через групповые политики, а в полном списке политик, связанных с обновлениями Windows 10, настроек куда как больше, чем тех, что доступны в обычных ручных настройках.

Их можно применять к отдельным ПК при помощи редактора локальной групповой политики Gpedit.msc, или при помощи скриптов. Но чаще всего их используют в домене Windows с Active Directory, где можно управлять комбинациями политик на группах ПК.

Значительное количество политик используется исключительно в Windows 10. Наиболее важные из них связаны с «Обновлениями Windows для бизнеса», расположенными в Конфигурация компьютера > Административные шаблоны > Компоненты Windows > Центр обновления Windows > Центр обновления Windows для бизнеса.

• Выберите, когда получать предварительные сборки – канал и задержки для обновлений компонентов.
• Выберите, когда получать обновления качества – задержки ежемесячных кумулятивных обновлений и других обновлений, связанных с безопасностью.
• Управляйте предварительными сборками: когда пользователь может подключить машину к программе Windows Insider и определите кольцо инсайдеров.

Дополнительная группа политик находится в Конфигурация компьютера > Административные шаблоны > Компоненты Windows > Центр обновления Windows, где можно:

• Удалить доступ к функции приостановки обновлений, что не даст пользователям мешать установке, задерживая её на 35 дней.
• Удалить доступ ко всем настройкам обновлений.
• Разрешить автоматическое скачивание обновлений на соединениях с учётом трафика.
• Не скачивать вместе с обновлениями драйвера.

Следующие установки есть только в Windows 10, и они относятся к перезапускам и уведомлениям:

• Отключить автоматическую перезагрузку для обновлений во время периода активности.
• Указать диапазон периода активности для автоматического перезапуска.
• Указать крайний срок для автоматического перезапуска с целью установки обновлений (от 2 до 14 дней).
• Настроить уведомления с напоминанием об автоматическом перезапуске: увеличить время, за которое пользователя предупреждают об этом (от 15 до 240 минут).
• Отключить уведомления об автоматическом перезапуске с целью установки обновлений.
• Настроить уведомление об автоматическом перезапуске так, чтобы оно не исчезало автоматически через 25 сек.
• Не разрешать политикам задержки получения обновлений инициировать сканирование в Центре обновления Windows: эта политика запрещает ПК проверять обновления, если назначена задержка.
• Разрешить пользователям управлять временем перезапуска и откладывать уведомления.
• Настроить уведомления об обновлениях (появление уведомлений, от 4 до 24 часов), и предупреждений о неминуемом перезапуске (от 15 до 60 минут).
• Обновление политики электропитания для перезапуска корзины (настройка для образовательных систем, позволяющая обновляться даже при питании от батареи).
• Выводить настройки уведомлений об обновлениях: позволяет запретить уведомления об обновлениях.

Следующие политики есть как в Windows 10, так и в некоторых более старых версиях Windows:

• Настройка автоматического обновления: эта группа настроек позволяет выбрать еженедельный, раз в две недели или ежемесячный график обновлений, включая день неделе и время для автоматического скачивания и установки обновлений.
• Указать размещение службы обновлений Microsoft в интрасети: настроить сервер Windows Server Update Services (WSUS) в домене.
• Разрешить клиенту присоединяться к целевой группе: администраторы могут использовать группы безопасности Active Directory для определения колец развёртывания WSUS.
• Не подключаться к расположениям Центра обновления Windows в интернете: запретить ПК, работающим с местным сервером обновления, связываться с внешними серверами обновлений.
• Разрешить управлению электропитанием центра обновления Windows выводить систему из спящего режима для установки запланированных обновлений.
• Всегда автоматически перезапускать систему в запланированное время.
• Не выполнять автоматическую перезагрузку, если в системе работают пользователи.

Инструменты работы в крупных организациях (Enterprise)

Крупные организации с сетевой инфраструктурой Windows могут обойти сервера обновления Microsoft и развёртывать обновления с местного сервера. Это требует повышенного внимания со стороны корпоративного IT-отдела, но добавляет компании гибкости. Два самых популярных варианта – это Windows Server Update Services (WSUS) и System Center Configuration Manager (SCCM).

Сервер WSUS устроен проще. Он работает в роли Windows Server и обеспечивает централизованное хранение обновлений Windows в организации. Используя групповые политики, администратор направляет ПК с Windows 10 на сервер WSUS, служащий единственным источником файлов для всей организации. С его консоли администратора можно одобрять обновления, выбирать, когда их ставить на отдельные ПК или группы ПК. ПК можно вручную привязывать к разным группам, или можно использовать выбор целей на стороне клиента для развёртывания обновлений на основе существующих групп безопасности Active Directory.

Поскольку кумулятивные обновления Windows 10 растут всё сильнее с каждым новым выпуском, они могут занимать значительную часть пропускной способности каналов связи. Сервера WSUS экономят трафик, используя Express Installation Files – это требует больше свободного места на севере, но значительно уменьшает размер файлов обновления, отправляемых на клиентские ПК.

На серверах версий WSUS 4.0 и далее можно также управлять обновлениями компонентов Windows 10.

Второй вариант, System Center Configuration Manager использует богатый по возможностям Configuration Manager for Windows совместно с WSUS для развёртывания обновлений качества и обновлений компонентов. Панель управления позволяет администраторам сети отслеживать использование Windows 10 во всей сети и создавать планы обслуживания на основе групп, включающие информацию по всем ПК, приближающимся к завершению своего цикла поддержки.

Если в организации уже установлен Configuration Manager для работы с более ранними версиями Windows, то добавить в него поддержку Windows 10 будет достаточно просто.

План обслуживания базы 1С в СУБД MS SQL

Сегодня рассмотрим один из вариантов обслуживания баз 1С в СУБД MS SQL.

Содержание:


1. Немного теории по планам обслуживания
2. Постановка задачи по созданию планов обслуживания
3. Создание плана обслуживания (Полная копия)
4. Создание плана обслуживания (Разностная копия)
5. Создание плана обслуживания (Резервная копия журналов транзакций)
6. Мониторинг планов обслуживания

1. Немного теории по планам обслуживания

Может многие со мой не согласятся, но для меня главной целью использования Планов обслуживания в MS SQL является создание резервных копий. Местные ITишники либо еще не делают резервные копии, либо уже делают, после печальных последствий отсутствия резервных копий. Да, не спорю, Планы обслуживания также нужны для оптимизации БД и выгрузки журналов транзакций, в последнем случаи, если не выполнять выгрузку журналов транзакций, у вас может вырасти база данных и занять все пространство на диске, 1С встанет колом и пользователи не смогут работать с базой, а вам придется выполнять шринк (Shrink) базы, это наверно самое страшное для ITишники после поломки базы и отсутствии резервных копий. Но об шринке (Shrink) поговорим в другой раз.

MS SQL Server поддерживает три модели восстановления:
1) Simple (Простая) — хранится только необходимый для жизни остаток журнала транзакций.
2) Full (Полная) — хранится весь журнал транзакций с момента последнего резервного копирования журнала транзакций.
3) Bulk logged (С неполным протоколированием) — часть операций записываются в очень компактном формате. В остальном идентична Full.

Модель восстановления базы можно посмотреть, в свойствах базы данных, на вкладке Параметры. Там же ее можно поменять. На практике я использую Full (Полная).

MS SQL поддерживает три типа формирования резервных копий:
1) Full (Полная копия)
2) Differential (Дифференциальная копия, Разностная копия)
3) Log (Резервная копия журналов транзакций)
Не путайте понятия: полная модель восстановления и полная резервная копия — разные вещи.

Рассмотрим подробно три типа формирования резервных копий.
1) Полная резервная копия
Позволяет восстановить состояние базы данных на некоторый момент времени. Состоит из копии файлов данных и журнала транзакций на момент завершения формирования резервной копии.

2) Разностная резервная копия
Хранит данных, изменившиеся с момента последней Полной резервной копии. При восстановлении нужно сначала восстановить Полную резервную копию в режиме NORECOVERY, потом можно применить любую из последующих Разностных копий. За счет этого можно значительно снизить объём дискового пространства для хранения резервной копии. Обратите внимание: без предыдущей Полной резервной копии Разностная копия бесполезна. Каждая последующая Разностная копия будет хранить все данные, входящие в предыдущую Разностную резервную копию, сделанную после предыдущей Полной копии. Поэтому каждая следующая Разностная копия больше предыдущих, пока снова не сделать Полную копию. Соответственно для восстановления на какой-то момент времени достаточно последней Полной резервной копии и последней Разностной копии. Промежуточные копии для восстановления не нужны.

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

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

2. Постановка задачи по созданию планов обслуживания

В организации N работают по шестидневке с 8:00 до 17:00. Обед с 12:00 до 13:00.
Имеется в MS SQL база данных с именем Moodle.
Что нужно сделать:
1) Проверить модель восстановления базы данных, должна быть Полная.
2) Создать план обслуживания, который будет создавать Полную резервную копию базы данных каждое воскресение в 17:00. Очищать хранилище от устаревших резервных копий старше 15 дней.
3) Создать план обслуживания, который будет создавать Разностную копию базы данных каждый день в 21:00 кроме воскресения.
4) Создать план обслуживания, который будет создавать Резервную копию журналов транзакций два раза в день, в 12:00 и в 17:00, кроме воскресения.

3. Создание плана обслуживания (Полная копия)

Запускаем SQL Server Management Studio, в Обозревателе объектов проходим по ветке Управление — Планы обслуживания.

Правой кнопкой по пункту Планы обслуживания и в контекстном меню выбираем Создать план обслуживания… Указываем имя, к примеру: Moodle. В открывшемся конструкторе будем создавать вложенные планы обслуживания. щелкните два раза по строке ВложенныйПлан _1

Задайте Имя, Описание и обязательно настройте Расписание выполнения вложенного плана обслуживания: еженедельно в воскресение 17:00:00

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

Ниже на рисунке представлен результат настройки, который должен у нас получится, но все по порядку.
Размещаем задачу «Проверка целостности базы данных», двойным щелчком мыши открываем диалог настройки задачи, в первую очередь в свойстве Базы данных отмечаем нужную базу, а остальное настраиваем как показано на рисунке. При желании можно посмотреть T-SQL код полученной задачи.

Размещаем следующую задачу «Перестроение индекса» она у нас будет выполнятся только после успешно выполненной предыдущей задачи. Настраиваем как показано на рисунке, не забываем указать конкретную базу данных.

 Для связи двух задач щелкните по первой задаче «Проверка целостности базы данных» у этой задачи появится стрелка, щелкните по ней и не отпуская соедините со второй задачей «Перестроение индекса». Для изменения значения условия выполнение следующей задачи, щелкните два раза по линии и в открывшемся диалоговом окне выполните необходимые настройки.

Размещаем задачу «Обновление статистики» которая будет выполнятся после завершения предыдущей. Настраиваем эту задачу как на рисунке, не забываем выбрать базу данных.

Размещаем задачу «Выполнение инструкции T-SQL» с кодом: 

DECLARE @intDBID INTEGER SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = ‘Moodle‘) 

DBCC FLUSHPROCINDB (@intDBID)

Инструкция DBCC FREEPROCCACHE используется для аккуратной очистки кэша планов. Освобождение кэша планов приводит, например, к тому, что хранимая процедура повторно компилируется, а не используется из кэша.

При настройке для своей базы не забываем изменить имя БД Moodle.

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

Размещаем следующую задачу «Очистка журнала» она у нас будет выполнятся очистку журналов. Настраиваем как показано на рисунке.

Размещаем следующую задачу «Очистка после обслуживания» она у нас будет выполнятся удаление старых файлов резервных копий, так как свойстве Расширение файла указана маска *.*, то удаляются будут все файлы, и полной резервной копии, и разностной, и журнала транзакций. Настраиваем как показано на рисунке.

Обратите внимание, две последние задачи выполняются после выполнения задачи «Резервное копирование базы данных» и самое главное, задачу «Очистка после обслуживания» нужно выполнять только после успешно выполненной задачи «Резервное копирование базы данных». Что бы не получилось, что у вас уже который раз не создаются резервные копии, а вы задачей «Очистка после обслуживания» удаляете последние актуальные копии.

4. Создание плана обслуживания (Разностная копия)

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

Заполним поля свойств и настроим расписание как показано на рисунке.

Размещаем две задачи «Проверка целостности базы данных» и «Резервное копирование базы данных», обратите внимание последняя задача выполняется только после успешного завершения предыдущей. Иначе какой смысл делать резервную копию если она не корректна.

На рисунке представлена настройка задачи «Проверка целостности базы данных».

На рисунках представлены настройки задачи «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Разностное. И не забудьте указать конкретную базу данных.

5. Создание плана обслуживания (Резервная копия журналов транзакций)

Добавим два вложенных плана обслуживания, один настроим на 12:00 второй на 17:00.

На рисунке представлен результат плана обслуживания на 12:00, на 17:00 отличатся ничем не будет, только временем выполнения.

Разместим одну задачу «Резервное копирование базы данных». Обратите внимание на Тип резервной копии, должен стаять Журнал тарнзакций. И не забудьте указать конкретную базу данных.

6. Мониторинг планов обслуживания

После создания всех Планов обслуживания они появятся в ветке Агент SQL Server.

Откройте Мониторинг активности заданий, в этом мониторинге можно увидеть какие задачи, когда выполнялись, когда следующее выполнение и успешно ли они выполнялись. 

Для запуска определенного плана, достаточно в контекстном меню выбрать пункт Запустить задание на шаге…

P/S

Сегодня рассмотрели минимальные азы создания планов обслуживания в MS SQL по созданию трех типов резервных копий баз данных: Full (Полная копия), Differential (Дифференциальная копия, Разностная копия) и Log (Резервная копия журналов транзакций).

shrink – список вопросов по тегу – страница №1

Есть ли способ узнать, как продвигается DBCC SHRINKFILE заявление? Вот как я это запустил dbcc shrinkfile (‘main_data’, 250000) Я использую вышеуказанный оператор как на SQL. ..

Я не могу понять, как уменьшить размер файла ldf базы данных. Администратор базы данных говорит, что я должен использовать backup log dbname with truncate_only И хотя похоже…

Допустим, у меня есть база данных SQL Server, файлы данных которой были созданы с начальным размером 100 ГБ, но она содержит только 10 ГБ данных. Тогда размер резервной копии…

Я нашел список версий и их номера выпуска Solaris http://download.oracle.com/docs/cd/E19253-01/819-5461/appendixa-1/index.html Я знаю, что вы можете увеличить пул, заменив…

У меня жесткий диск емкостью 5 ТБ, и я хочу уменьшить его до 2 ТБ. Я использую Google Compute Engine PaaS. Как я могу это сделать? Можете ли вы предложить какие-либо…

Я использую VMplayer в Windows 7 и 2008 R2. Внутри гостевой ОС в инструментах VMware есть параметр «Подготовить к сжатию» на вкладке «Сжатие». В главном меню VMplayer есть…

У меня есть файл базы данных размером 150 ГБ, но используется только 75 ГБ — это потому, что я переместил все индексы (остальные 75 ГБ) в новый файл данных. Я хотел бы…

Для проекта я использую SQL Server 2008 R2. В одной таблице есть столбец файлового потока. Я провел несколько нагрузочных тестов, и теперь в базе данных использовано ~ 20 ГБ. У…

Есть ли способ приблизительно определить, сколько времени займет сжатие базы данных SQL Server? Существуют ли инструменты, позволяющие сделать предположения? У нас очень…

Размер моего файла базы данных SQL Server 2008 (.mdf) составляет почти 24 МБ, но размер файла журнала вырос до 15 ГБ. Если я хочу уменьшить базу данных, какие важные моменты…

Усечение файлов журналов транзакций MS SQL — Блог

Постановка проблемы

По разным причинам (например произведенной ранее реиндексацией rebuild index) может случиться, что операция Shrink Database или Shrink file не освобождают дисковое пространство занимаемое файлами журналов транзацкий (transaction log) даже после операций резервного копирования с включением усечение журнала транзакций (truncate log) в полной модели восстановления (recovery model full).

Усечение возможно только после достижения контрольной точки. в описанной постановке задачи требовалось сократить всеми средствами и силами занимаемый базами данных размер на файловой системе, с минимизацией рисков потери/порчи данных и простоях системы. 

Необходимые действия

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

 — выполнитьрезервную копию журнала транзакций transaction log

 — перевести базу данных в readonly (снизить риски)

 Далее необходимо переключить модель восстановления базы данных в Simple (внимание, на этом шаге выполняется критическое изменение).

 После перевода, уже не актуальный файл журналов транзакций можно «зарезать» с помощью DBCC SHRINKFILE, например, до 1 МБ.

  DBCC SHRINKFILE (databasename, 1)

  Далее возвращаем обратно модель восстановления базы данных (Full), переводим базу в Read/Write. 

 Комманды для выполнения описанных действий

USE databasename ;  
ALTER DATABASE databasename SET READ_ONLY
BACKUP LOG databasename to DISK = 'Backup storage\databasename.trn';
ALTER DATABASE databasename SET RECOVERY Simple ;
DBCC SHRINKFILE (databasename_log, 1)
ALTER DATABASE databasename SET RECOVERY FULL ;
ALTER DATABASE databasename SET READ_WRITE

Вместо эпилога

  В конце заметки хочу отметить, что необходимость операции Shrink database является спорной [1][2][3][4][5][6][7]…, а предложенный в заметке путь со сменой модели восстановления для некоторых дикостью. Я не агитирую за «Красную Армию», а привожу возможное решение конкретной поставленной задачи сокращения «несокращаемых» maintenance операциями файлов журналов транзакций.

Почему растет LOG в MS SQL ?

Почему растет LOG в MS SQL ?

Друзья, почти ежедневно сталкиваюсь с тем, что на курсе: Администратор 1С, при опросе студентов, на предмет «Как Вы организовали бэкап в MS SQL?». Очень редко кто пишет: «Да, помимо «полного» я делаю и бэкап журналов транзакций».

К сожалению, редко кто делает бэкап ЖТР (

И тем самым открывает прямой путь к таким проблемам как:

«Распух лог в MS SQL», «Сильно увеличился LDF», «Разрастается log, что делать?», «Журнал занял все свободное место на диске», и многое, многое другое.

В этой статье я не буду рассыпать терминами и сложными понятиями гуру специалиста DBA, нет!

Так как вижу реальную картину, реальную проблематику вопроса, на более чем 5000 тыс студентов (Что проходили у нас курс: Администратор 1С). И реальность она несколько в другом!

Большинство не делает бэкапов журналов транзакций, так как не понимает зависимостей (связей), между их созданием и размерами самого журнала (*ldf).

Собственно цель данной статьи, максимально понятно, на простом языке, объяснить  и закрыть раз и навсегда проблему растущего лога в MS SQL!

Приступим…

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

Файл *LDF он же и есть наш журнал транзакций!

Что там хранится?

Каждая база данных SQL Server имеет журнал транзакций, в котором фиксируются все транзакции и производимые ими в базе изменения.

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

Если говорить еще проще, благодаря бэкапу ЖТР есть возможность восстановить базу фактически на любой момент времени (вплоть до нужной секунды)!

При этом следует понимать, что никаких по факту данных из 1С в прямом смысле этого слова в журнале нет!

Все данные пишутся в файл *mdf, а вот фиксация этих действий пишется в *ldf, по каждому действию (транзакции), что происходит у Вас в 1С. Все что делают пользователи в 1С, фиксируется в журнале транзакций, только сам факт (фиксация) произошедших событий в базе, а не сами данные.

Собственно отсюда и название «Журнал транзакций». Конечно на практике все сложнее, но в упрощенном для понимания виде все именно так.

Почему растет лог файл в MS SQL (*ldf) ?

Конечно, если учитывать что каждое действие сделанное пользователем в 1С фиксируется в журнале транзакций, то он просто не может не расти! И здесь также стоит отметить, что не только действие пользователя влияет на рост журнала, но и различные регламентные задачи (фоновые различные процессы), особенно сильно заметен рост, когда происходит в базе 1С реструктуризация. Собственно при обновлении конфигурации также можем замечать “взрывной” рост журнала транзакций.

К слову мы только что ответили на частый вопрос: «Вот у меня лог не разрастался» в базе «А», а в базе «Б» растет очень быстро».

Конечно если с базой «Б» пользователи работают интенсивно или различные фоновые, регламентные задачи (их много), безусловно, он будет расти быстрее, такова физика работы «MS SQL»!

MS SQL всегда (в “полной” модели восстановления) будет пытаться зафиксировать, фактически все действия в базе. И журнал будет расти до тех пор, пока мы не сделаем его бэкап, этим мы и «усекаем» его!

«Простая» и «полная» модель восстановления

Да, «полная» модель восстановления подразумевает, что в журнал будем писать «По максимуму» все возможное.  Все что сможет записать MS SQL, он туда запишет. Исключения конечно есть. К примеру, когда свободное место закончилось на диске или есть ограничения на сам лог (если установили). Есть и другие причины, но мы сейчас не об этом.

Нам важно понимать одно: «Полная» модель = «Полный» лог! А значит, есть возможность не терять данные, при необходимости восстановится на любой момент времени (фактически до секунды), а выполнив бэкап еще и «заключительного фрагмента журнала» и вообще ничего не потерять!

На сайте Microsoft можно найти информацию о том, что единственная  «рабочая» модель, предназначенная для реальной работы, есть только  «Полная» модель восстановления! Так как в работе недопустимо терять данные, а это гарантированно произойдет в «Простой» модели восстановления, если случится “сбой”.

«Простая» модель восстановления может использоваться для тестирования, разработки, для временного переключения (Обязательно! С предварительным бэкапом базы и лога). К примеру, только на время реструктуризации ее можно включить, а потом обратно вернуть в «Полную» модель. Есть и другие случаи, когда мы только временно переключаем режим с «Полной в Простой» Но работаем всегда в “полной” модели восстановления!

В “простой” модели мы никак не можем восстановиться (в случаи чего) на интересующий нас момент времени. Только на тот момент, когда сделан либо «полный» бэкап, либо «полный» + «разностный»!

Вывод:

Только в «полной» модели мы должны работать! Она не зря «по умолчанию» в MS SQL!

«Активная» и «Неактивная» часть журнала

Сперва дадим ответ на вопрос:  «Что происходит в момент создания бэкапа ЖТР ?»

Чтоб разобраться в этом вопросе, нам нужно понимать, что журнал транзакций может быть «условно разделен» на две части: «Активная» и «Неактивная» часть журнала.

«Активная» – содержит изменения, которые были сделаны в базе, но еще не зафиксированы на диске.

«Неактивная» – изменения уже зафиксированы на диске, следовательно, можно усекать неактивную часть журнала (делать бэкап), вплоть до его активной части!

Неактивная часть журнала транзакций содержит информацию о завершенных транзакциях и не используется сервером SQL Server в процессе восстановления данных. Вместо увеличения размера файла журнала транзакций SQL Server будет повторно использовать освободившееся пространство.

И вот в момент, когда мы создаем бэкап журналов транзакций, мы тем самым «усекаем» его «неактивную» часть (точнее это делает сам MS SQL), вплоть до начала его активной части!

При этом вначале всегда происходит его бэкап, а только после уже «усечение», как на рисунке выше.

Бэкап журналов нужно делать довольно часто (раз на 30-60 мин), особенно если с базой активно работают пользователи, он может вырасти довольно быстро, и конечно без автоматизации этого процесса не обойтись!

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

Вывод:

В «полной» модели восстановления бэкап журналов транзакций НЕОБХОДИМ! Если Вы не хотите в один прекрасный день обнаружить, что свободное место на диске, где он находится, уже закончилось!

Если ЛОГ уже вырос ?

Конечно, если Вы раньше не делали бэкапов журналов, лог соответственно вырос, то здесь одним бэкапом журналов транзакций не обойтись!

И вот почему:

Если Вы хотите больше узнать о технической стороне 1С, тогда регистрируйтесь на первый бесплатный модуль курса: Администратор 1С >>>

Успехов Вам, Коллега!

С уважением, Богдан.

База данных

— как лучше всего использовать сжатый SQL Server?

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

Если ваш файл данных занимает много места, это не означает, что это пространство пусто .

Вы должны использовать sp_spaceused , чтобы определить, есть ли неиспользуемое пространство в файле данных.

Если есть неиспользуемое пространство , это будет , уже использованное «для вставки и обновления некоторых новых данных», и если нет , выполнение shrink ничего не изменит: shrink не удаляет ваши данные , все, что он делает, это перемещает данные в начале файла, чтобы освободить место в конце, чтобы вернуть их в OS .

Сжатие файла данных может быть полезно, когда у вас был файл данных размером 2 ТБ, а 1 ТБ данных был удален, и вы не планируете вставлять еще один ТБ данных в следующие 10 лет.

Вы можете представить свой файл данных в виде коробки размером 1м x 1м x 1м. Если у вас есть только половина коробки с игрушками, даже если вы не используете термоусадочную пленку , вы можете положить в эту коробку другие игрушки (сделайте вставьте /, обновите ). То, что делает shrink , собирает все игрушки в одном углу, а затем разрезает вашу коробку так, чтобы она составляла 50 см x 50 см x 50 см. Таким образом, ваша комната (ОС) теперь имеет больше свободного места, потому что ваша коробка для игрушек занимает только половину пространства, которое занимало до сжатия.

… И если ваша коробка уже была заполнена, вы не сможете добавить больше игрушек, даже если попытаетесь сделать термоусадку .

, если журнал заполнен, сжатие журнала не должно быть проблемой, верно?

Shrinkig log — это еще один процесс, внутри файла журнала ничего нельзя переместить, в этом смысле, конечно, shrink не может причинить большого вреда, как и в случае с файлом данных : он не требует ресурсов сервера, он требует не вызывать фрагментации и т. д.Но если это удастся или нет, это зависит от причины вашего «журнал переполнен».

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

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

Как сжимать и сжимать данные в SQL Server

База данных SQL Server является основой вашего решения ERP, поддерживая приложения, которые обеспечивают ваш бизнес надежными данными и хранилищами. Эти базы данных заполнены критически важными данными для вашей корпорации — незаменимой историей бизнеса, аналитическими данными и всеми данными, необходимыми вашему бизнесу для поддержания общего состояния вашей компании. Их организация и бесперебойная работа являются неотъемлемой частью всех повседневных функций вашей организации и требуют тщательного управления базами данных.

Для администраторов баз данных это означает обширный список дел, который включает в себя все, от удаления старых ненужных данных до регулярных обновлений и всего, что между ними. Но есть ли правильный выбор в вашем контрольном списке управления базой данных? Существует множество отличных тактик, которые помогут поддерживать гибкость и эффективность баз данных. Правильный выбор размера вашей базы данных и поддержание ее управляемости и гибкости базы данных SQL Server жизненно важны для эффективности и роста вашего бизнеса.

Выбирайте правильный размер

Выбор правильного размера базы данных — это процесс планирования, который играет важную роль в предварительном проектировании базы данных.Базы данных глубоки, и их нужно делать как можно более узкими за счет разумного дизайна. Например, вы можете перейти из Excel в базу данных MS Access, потому что ваши электронные таблицы настолько глубоки и широки, что вы не можете видеть лес за деревьями.

Текстовое поле базы данных Access может содержать до 255 символов. Итак, если вы разрабатываете таблицу с полем из 255 символов, которое никогда не будет содержать более 20 символов, ваша база данных в конечном итоге станет излишне толстой и медленной.

MS Access также имеет несколько полезных архивных запросов, которые собирают все эти старые данные, помещают их в отдельные таблицы и отправляют в хранилище, чтобы не мешать повседневным операциям. В MS Access также есть команда сжатия и восстановления, которая, помимо восстановления данных, избавляет от накопившегося мусора, снижающего общую производительность. Дизайн и стратегия вашей базы данных играют большую роль в эффективности ваших приложений и бизнес-операций в целом.

Переход на SQL Server

Точно так же, когда вы перешли на MS SQL Server, это произошло потому, что вы хотели хранить больше данных и держать все отдельно от движка, который ими управляет.Microsoft Access — отличное приложение, но оно (и другие облегченные базы данных) достигает конечной точки, когда его объекты — формы, коммутаторы, запросы, макросы и т. Д. — начинают переполнять ваши данные и конкурировать за место для хранения. Итак, что может сделать ваша организация, когда вы дойдете до этого момента?

Варианты уменьшения размера базы данных SQL Server

Почему вы хотите уменьшить размер базы данных SQL Server? Короткий ответ таков: с годами ваша база данных начала нести мертвый груз пустых страниц данных, а также неиспользуемых или архаичных данных.

Как и Microsoft Access, SQL Server имеет несколько способов удаления устаревших данных и их архивирования. Например, запрос Make-Table найдет старые записи и создаст новую таблицу. Запрос на удаление, в свою очередь, очистит исходную таблицу, удалив заархивированные записи.

У вас есть две дополнительные возможности уменьшить размер базы данных SQL Server: сжать базу данных или сжать ее.

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

Сжатие файлов данных SQL в некоторой степени освобождает пространство, как при дефрагментации диска.Он перемещает неиспользуемые 8-килобайтные страницы данных из конца файла в пустое пространство ближе к его началу. Идея состоит в том, чтобы освободить достаточно места в конце файла, чтобы его можно было «освободить» и вернуть в систему.

Не попадитесь в «ловушку» усадки.

Вот что сказал один программист SQL, Пол Рэндал, в откровенном сообщении в блоге «Почему не следует сжимать файлы данных»:

«Сокращение файла данных никогда не должно быть частью регулярного обслуживания, и вам НИКОГДА, НИКОГДА не следует включать автоматическое сжатие… Не попадитесь в ловушку плана обслуживания, который перестраивает все индексы, а затем пытается освободить необходимое пространство для восстановления индексов путем сжатия… »

Рэндал называет автоматическое сжатие «игрой с нулевой суммой» без реального увеличения производительности.Microsoft также советует никогда не устанавливать для параметра базы данных AUTO_SHRINK значение ON, если у вас нет «особых требований».

Уменьшить базу данных SQL Server, когда:

• вы удалили большой объем данных из очень большой базы данных.
• база данных вряд ли вырастет.
• , вам необходимо очистить файл перед его удалением.

У сжатия базы данных есть ограничения и ограничения. :

Уменьшение базы данных:

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

Если он не сломан, не чините его.

Главный вывод о многократном сокращении базы данных SQL Server заключается в том, что большинству баз данных требуется свободное пространство для их готовности к повседневным операциям.Совет Microsoft Technet выглядит следующим образом:

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

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

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

Обратной стороной является необходимость дополнительных ресурсов ЦП на сервере базы данных для сжатия и распаковки данных по мере их вызова. Сжатие не влияет на некоторые типы данных, например текст и дату. Однако другие типы данных могут быть значительно сжаты.См. Диаграмму в MSDN «Реализация сжатия строк» ​​для типов данных SQL Server 2016.

Точно так же не все таблицы и индексы базы данных SQL Server можно сжать, и не все выпуски SQL Server поддерживают сжатие данных. Активация сжатия данных также лучше всего работает с мастером сжатия данных Microsoft Server Management Studio. В этой статье на sqlmag.com есть иллюстрированное пошаговое руководство по применению сжатия данных к базе данных SQL Server.

Независимо от того, используете ли вы MS Access или перешли на мощную базу данных SQL Server, ваш план обслуживания базы данных должен включать киберэквивалент контроля веса.Если ваша база данных становится тяжелой, наши дружелюбные специалисты знают, как провести капитальный ремонт или обновление. Мы сделали их тысячи! Дай нам знать что тебе нужно. Мы можем либо посадить на диету ваши ценные бизнес-данные, либо помочь вам их увеличить.

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

Примечание редактора. Этот пост был первоначально опубликован в марте 2016 года и был обновлен для обеспечения актуальности, точности и полноты.

Сжимать базу данных кусками — tsql · GitHub

Тибор Караси SQL Server

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

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

Благодарности
Я хотел бы поблагодарить Марка Эллисона и Кален Делани, которые предоставили ценные предложения и материалы для этой статьи.

Дополнительная информация
Вы можете сжать файл базы данных с помощью DBCC SHRINKDATABASE (которая предназначена для всех файлов базы данных) или DBCC SHRINKFILE (которая предназначена для конкретного файла базы данных).Я предпочитаю SHRINKFILE. Я не буду вдаваться в подробности команд здесь; они задокументированы в электронной документации по SQL Server. Давайте сначала определим, что на самом деле происходит, когда вы сжимаете файл базы данных:

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

Управление файлом журнала транзакций
Этот раздел служит лишь кратким введением в тему управления файлом журнала транзакций.См. Справку ниже.
Каждое изменение, выполненное в базе данных, отражается в файле журнала транзакций. Излишне говорить, что эти записи журнала должны быть удалены (или фактически: перезаписаны) рано или поздно — иначе мы получим огромный файл журнала или полный файл журнала.
Один из способов — настроить базу данных на простую модель восстановления. Это означает, что SQL Server автоматически помечает неактивные записи журнала как «ОК для перезаписи» (он же «обрезать журнал» или, как я люблю это называть: «очистить журнал»).
Другой вариант — иметь базу данных в режиме полного восстановления или модели восстановления bulk_logged и выполнять регулярное резервное копирование журнала транзакций (BACKUP LOG). Журнал транзакций очищается при резервном копировании журнала. Обратите внимание, что журнал * не * очищается для других типов резервных копий (например, BACKUP DATABASE).
Подробнее см. В электронной документации по SQL Server http://msdn.microsoft.com/en-us/library/ms345583.aspx. Обязательно прочтите все подразделы. Стоит потратить около 30 минут на то, чтобы понять, как управлять журналом транзакций.Поверьте мне в этом.

Сжатие файла журнала транзакций
SQL Server не может переместить записи журнала из конца файла журнала в начало файла журнала. Это означает, что SQL Server может уменьшить размер файла только в том случае, если он пуст в конце файла. Самая последняя запись журнала устанавливает предел того, насколько можно сжать журнал транзакций. Файл журнала транзакций сокращается до единиц виртуальных файлов журнала (VLF). Вы можете увидеть макет VLF, используя недокументированную команду DBCC LOGINFO, которая возвращает одну строку для каждого файла виртуального журнала для базы данных:

DBCC LOGINFO ('myDatabase')



Интересный столбец — «Статус».0 означает, что VLF не используется, а 2 означает, что он используется. В моем примере у меня есть 2 в конце файла (результат чтения сверху вниз), и это означает, что в настоящее время файл нельзя сжать. Как с этим справиться, зависит от вашей модели восстановления. Вы должны соответствующим образом настроить и заменить параметры и базу данных для приведенного ниже кода. Если вы не уверены, проверьте команду в документации по продукту:

• Простая модель восстановления
  USE dbname
CHECKPOINT
- Первый параметр ниже - fileno для файла журнала, часто 2.Проверьте с помощью sys.database_files
- Второй желаемый размер в МБ.
DBCC SHRINKFILE (2, 500)
DBCC SQLPERF (LOGSPACE) - Необязательно
DBCC LOGINFO - Необязательно
  Теперь повторите приведенные выше команды столько раз, сколько необходимо!
• Модель восстановления с полным или массовым журналом
  ИСПОЛЬЗОВАТЬ dbname
BACKUP LOG dbname TO DISK = 'C: \ x \ dbname.trn'
- Первый параметр ниже - fileno для файла журнала, часто 2. Проверьте с помощью sys .database_files
- Второй желаемый размер в МБ.
DBCC SHRINKFILE (2, 500)
DBCC SQLPERF (LOGSPACE) - Необязательно
DBCC LOGINFO - Необязательно
  Теперь повторите приведенные выше команды столько раз, сколько необходимо!

В конечном итоге вы очищаете журнал (CHECKPOINT или BACKUP LOG) и DBCC SHRINKFILE несколько раз , чтобы SQL Server мог переместить заголовок журнала в начало файла, а также чтобы конец файла стал неиспользованный. Изучите структуру файла журнала, используя DBCC LOGINFO между ними.

Если у вас есть множество VLF (много строк, возвращаемых из DBCC LOGINFO), вероятно, у вас изначально был небольшой размер файла для журнала, а затем было много мелких автозавершений.Наличие большого количества VLF снижает производительность, особенно при восстановлении базы данных и пакетных модификациях. Посмотрите этот и этот замечательный пост в блоге Линчи Ши. В этом случае рассмотрите возможность уменьшения файла журнала до очень маленького размера, а затем увеличьте размер файла до удобного большего размера.

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

• Каждая перемещенная страница будет регистрироваться в журнале транзакций.
  Допустим, у вас есть база данных с 50 ГБ используемого пространства (страницы данных и индексов), и сжатие будет перемещено на 40 ГБ к началу файла. Файлу журнала для этой операции сжатия потребуется 40 ГБ, вероятно, он автоматически увеличится до этого размера (если у вас еще нет 40 ГБ свободного места в файле журнала). Следующая резервная копия журнала будет иметь размер 40 ГБ плюс «обычные» записи журнала. Этого не происходит, если база данных находится в простом режиме восстановления, возможно, потому что CHECKPOINT будет регулярно сокращать журнал во время сжатия.
  (Применяется к сжатию файлов данных.)
• После сжатия, когда пользователи добавляют строки и т. Д. В базу данных, файл должен снова увеличиваться.
  Увеличение размера файла базы данных — дорогостоящая операция, она требует времени и снижает производительность (чрезмерное использование ресурсов). Кроме того, некоторые модификации будут заблокированы до завершения операции роста.
  (Применяется к сжатию файлов данных и журналов.)

  SQL Server 2005 и более поздних версий:
  Начиная с SQL Server 2005, у нас есть «мгновенная инициализация файлов», что означает, что файлы базы данных могут быть созданы и также очень быстро увеличиваться с Windows не обнуляет данные в файле базы данных.Мгновенная инициализация файла доступна только для файлов данных, но не для файлов журнала. Кроме того, для мгновенной инициализации файла необходимо, чтобы учетная запись службы для службы SQL Server имела привилегию Windows SE_MANAGE_VOLUME_NAME, которую можно назначить с помощью политики безопасности «Выполнить задачи обслуживания тома». По умолчанию это предоставляется только администраторам.

• Бывают ситуации, когда автоматическое увеличение не «догоняет» требования к использованию места.
  Это приведет к появлению сообщения об ошибке от SQL Server при выполнении модификации, возвращаемого клиентскому приложению: ошибка 1105, если данные заполнены, и 9002, если журнал заполнен.
  (Применяется к сжатию файлов данных и журналов.)
• Перемещение страниц данных приведет к фрагментации вашей базы данных.
  Предположим, вы перестраиваете индексы (для чего требуется свободное место в базе данных), а затем уменьшаете базу данных. Сжатие приведет к отмене перестроения индекса, в результате чего индексы останутся фрагментированными. Не верите мне? Это легко проверить на себе.
  Что делать, если вы сделаете наоборот, сначала уменьшите, а затем восстановите? Что ж, для rebuld требуется свободное место в базе данных для самого большого индекса, который вы перестраиваете, и, вероятно, у вас есть большая таблица с кластеризованным индексом.У моего друга было 4 ГБ используемого пространства db, где почти все пространство было одной таблицей 4 ГБ. Он сделал сжатие, а затем перестроил, при этом перекомпоновка немедленно «увеличила» размер базы данных до 8 ГБ.
  (Применяется к сжатию файлов данных.)
• Сильное сжатие и увеличение файлов базы данных приведет к фрагментации файловой системы, что еще больше снизит производительность.
  (Применяется к сжатию файлов данных и журналов.)
• Неоднократное сжатие и последующее увеличение файлов журнала транзакций обычно приводит к появлению большого количества файлов виртуального журнала, что может привести к длительному запуску базы данных.Это может проявляться в виде длительного времени запуска базы данных, длительного времени восстановления, задержек при репликации транзакций и т. Д. Дополнительную информацию можно найти в этом сообщении в блоге.

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

Код Transact-SQL
Ниже вы найдете код T-SQL, который я использовал, чтобы доказать, что сжатие базы данных приведет к тому, что количество записей журнала транзакций будет равно количеству перемещенных данных.(Сценарий использует имя файла ‘NUL’ в качестве места назначения резервной копии. Это будет «записывать» данные резервной копии в «битовое ведро». Т.е. данные нигде не сохраняются. Это очень практично для тестирования, но есть риск заявить о очевидном , не используйте ли , а не , это место назначения резервного копирования для вашей производственной работы!)

--Сценарий, показывающий, что сжатие создает много записей журнала.
SET NOCOUNT ON
USE master
IF DB_ID ('shrink_test') IS NOT NULL DROP DATABASE shrink_test
GO

CREATE DATABASE shrink_test
ON PRIMARY
(NAME = shrink_test_data ‘, FILENAME = NAME = shrink_test_data’c: \ shrink_data.mdf ‘
, SIZE = 5MB, MAXSIZE = 200MB, FILEGROWTH = 10%)
LOG ON
(NAME = shrink_test_log, FILENAME = N’c: \ shrink_test_log.ldf’
, SIZE = 3MB, MAXSIZE = 200MB, FILEGROW %)
GO

ALTER DATABASE shrink_test SET RECOVERY FULL

— Убедитесь, что база данных не находится в режиме автоматического усечения журнала
BACKUP DATABASE shrink_test TO DISK = ‘NUL’

USE shrink_test
CREATE TABLE t
(
c1 INT IDENTITY CONSTRAINT PK_shrink_test PRIMARY KEY NONCLUSTERED
, c2 CHAR (3000) DEFAULT ‘hello’
)

DECLARE @i INT
SET @i = 1
WHILE @i <= 40000
DECLARE IN REFERT IN 9065
IF @ i% 500 = 0 — Обрезать журнал для каждой 500-й строки
BACKUP LOG shrink_test TO DISK = ‘NUL’
SET @i = @i + 1
END
SELECT COUNT (c1) AS «Количество строк перед удалением должно быть 40000 «
FROM t
GO

—Удалите несколько строк, в цикле, чтобы журнал не увеличивался!
DECLARE @i INT
SET @i = 1
WHILE @i <= 20000
BEGIN
DELETE FROM t WHERE c1 = @i
IF @ i% 500 = 0 — обрезать журнал на каждой 500-й строке
BACKUP LOG shrink_test TO DISK = ‘NUL’
SET @i = @i + 1
END
SELECT COUNT (c1) AS «Количество строк после удаления, должно быть 20000»
FROM t

— Очистить журнал
BACKUP LOG shrink_test TO DISK = ‘NUL’

— Файл базы данных должен быть немного больше (160 МБ на моем компьютере), файл журнала меньше (3 МБ)
Имя SELECT, размер * 8192 / (1024 * 1024) Размер AS INMB FROM sysfiles
GO

— Это сжатие теперь может привести к появлению большого количества записей журнала, поскольку будет перемещено 20000 строк!
CHECKPOINT
DBCC SHRINKFILE (shrink_test_data, 40)

—Файл базы данных теперь должен быть маленьким, а файл журнала — большим
SELECT name, size * 8192 / (1024 * 1024) AS sizeInMB FROM shrink_test..sysfiles

— Мой результат: файл данных уменьшился до 80 МБ, а файл журнала увеличился с 3 МБ до 146 МБ !!!

USE master
GO
DROP DATABASE shrink_test

Остановитесь и подумайте, прежде чем уменьшать

Мне нравится Хэллоуин, поэтому я каждый год за пару месяцев до этого ловлю себя мысленно. Мой разум обращается к ужасам, и я должен предупредить осторожных путешественников в стране DBA о зле, которое я вижу слишком часто, — об уменьшении базы данных.

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

Плохой совет от экспертов

Плохой совет в Интернете. С каждым годом становится все труднее отделить шум и плохие советы от ценной и хорошей информации. Мне особенно неприятно, когда я вижу, как респектабельные администраторы баз данных регулярно сокращают базы данных. Это напоминает мне о том, как в 1800-х и начале 1900-х врачи фактически прописывали и рекомендовали курение как лекарство от… подождите… астмы.

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

Кошмар сжатия базы данных не является признаком безумия администраторов баз данных. Оказывается, причина…

Файлы данных

Сжатие файлов данных (mdf, ndf) — плохая идея, и ее следует зарезервировать на случай чрезвычайных ситуаций. Добраться до точки, когда сжатие файлов базы данных является важным аспектом в активно используемой OLTP-системе, является проблемой само по себе. До этого никогда не должно доходить. Это ваш красный флаг, что кто-то упал с планированием емкости или есть ошибка, позволяющая вводить все эти данные.

Администраторы баз данных обычно отслеживают размеры и темпы роста базы данных, чтобы они могли принимать правильные решения в отношении планирования емкости.

Файлы журнала транзакций

Сжатие файла журнала (только 1, поскольку это последовательная запись) следует зарезервировать до тщательного рассмотрения. Лучше выяснить, почему журнал транзакций такой большой, и получить необходимый объем дискового пространства для операций с базой данных. Иногда длительная транзакция может вызвать аномальный рост.В таких случаях можно , потенциально имеет смысл сжать журнал транзакций до соответствующего уровня (не настолько маленького, насколько мы можем) . В противном случае, если для типичных операций с базой данных требуется больше места, необходимо выделить дополнительное дисковое пространство для этого.

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

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

Да, это относится и к tempdb тоже

Tempdb иногда становится целью сокращения. Пожалуйста, не делай этого. Это действительно бессмысленное занятие. Если tempdb действительно раздувается сверх разумного предела, перестройте tempdb. Уменьшение размера базы данных tempdb может привести к сбою или зависанию транзакций, а также к снижению производительности, которая вам не понравится.

Autoshink

У Microsoft есть база знаний по этому поводу, которую стоит прочитать: Соображения по параметрам «автоматическое увеличение» и «автоматическое сжатие» в SQL Server

Трудно поверить, что автоматическое сжатие по-прежнему доступно для настройки базы данных и построения планов обслуживания. Это так же плохо, как и настройка питания Windows Server по умолчанию. Ваш ноутбук едва ли может хорошо работать при сбалансированной настройке питания, не говоря уже о хардкорной СУБД!

Вздох — все еще есть. Если Пол Рэндал не смог избавиться от автоусадки, то как мы надеемся, что это когда-нибудь произойдет?

Если вы не верите в это и хотите поспорить, прочтите, пожалуйста, эти статьи экспертов, которые это доказывают.Убедитесь сами.

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

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

1. Очистные работы
   1. В вашу базу данных попадают некорректные данные, и вам необходимо удалить их.Это редкая ситуация.
   2. Это также , а не , что должно происходить на регулярной основе. Если вы часто сжимаете базу данных для очистки, то вы должны знать, что это плохая практика, и ее следует прекратить. Вы не лечите болезнь, а скорее подпитываете симптомы, которые хотите вылечить.
2. Архивирование данных
   1. Если у вас есть политика архивирования, при которой вы периодически удаляете тонны данных из базы данных для архивации.
   2. Эти данные лучше никогда не возвращаться в базу данных.Это для архивирования… а не только для перемещения данных.
3. Абсолютное шоу, останавливающее кошмар.
   1. Я консультировал клиентов, которые настаивали на регулярном сокращении базы данных. Они утверждают, что полный диск делает базу данных недоступной и что мы должны сжать файлы базы данных, чтобы освободить место.
   2. Хотя и правда, что полный диск — это очень плохо, это неправильное средство. Если вам абсолютно необходимо сжать базу данных, то прямо сейчас должен быть элемент действия для планирования мощности! Нельзя допустить, чтобы это происходило постоянно.
   3. В конце концов — вы — это , следите за своими дисками, верно?

Надеюсь, вы прислушались к этому предупреждению и не поддались искушению сжаться.

Если вам нужно посмеяться, ознакомьтесь с моими SQL-мемами!

Нравится:

Нравится Загрузка …

Уменьшение SSISDB — улучшение и повтор

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

Поэтому я был удивлен, когда на нашем резервном диске закончилось место. База данных SSIS вырастет за несколько дней с ~ 7 ГБ до 70 ГБ. Беглый осмотр показал, что в нем хранится 60 миллионов событий, что было неожиданным и проблематичным.Небольшой поиск в Google привел к идее сократить время хранения. Есть много примеров того, как это сделать. К сожалению, тот, который мне больше всего понравился, имел все скрипты на скриншотах, что делает невозможным копирование и вставку. Этот пост исправляет это, объясняет причину выполнения различных шагов и указывает на возможные подводные камни и их предотвращение.

Перед тем, как начать

Не пытайтесь ничего без резервной копии. Если что-то пойдет не так, резервное копирование может быть единственным, что стоит между вами и часами работы, чтобы вернуть SSIS к работе.
Затем ограничьте автоматический рост базы данных SSIS и файлов журнала. При сжатии будет создано много записей в журнале, и если вы попытаетесь сразу уменьшить его слишком сильно, вы снова заполните свой диск, и задание выдаст исключение. Установите максимальный размер файлов журнала, близкий к максимальному, который вы можете поместить на диск. Потом сжимаешь, но до тех пор помогает каждый гигабайт.

Последний шаг подготовки — отключить все задачи, использующие SSIS. Когда работа SSIS не блокируется постоянно, проще сжать базу данных.Вы можете деактивировать задания в обзоре раздела заданий в агенте SQL Server:

Снимите отметки со всех заданий SSIS и нажмите OK. Чтобы включить их позже, вы просто проверяете их и еще раз и нажимаете ОК, чтобы сохранить эти изменения.

Начало усадки

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

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

ИСПОЛЬЗУЙТЕ [master]
ИДТИ

ВЫБЕРИТЕ имя, recovery_model_desc
ИЗ sys.databases
ГДЕ имя = ‘SSISDB’
ИДТИ

ИЗМЕНИТЬ БАЗУ ДАННЫХ [SSISDB] УСТАНОВИТЬ ВОССТАНОВЛЕНИЕ ПРОСТО С NO_WAIT
GO

Теперь проверьте, какие значения установлены для свойств каталога:

ИСПОЛЬЗОВАТЬ SSISDB
ИДТИ

выберите * из [каталога] .catalog_properties

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

Следующим шагом является уменьшение времени удерживания, которое контролируется переменной Retention_window . Эти значения указывают, сколько дней хранятся данные. По умолчанию данные хранятся в течение года (365 дней). Если вы хотите сократить его до 7 дней, у вас может возникнуть соблазн сделать это за один прием.Он может работать, но, скорее всего, он будет работать часами, а затем выйдет из строя. Чтобы снизить риск потратить часы без какого-либо прогресса, старайтесь делать шаги поменьше. Уменьшите его с первой попытки до 200 дней. Если это сработает, уменьшите его на 50 дней. Если это не работает, вы можете сократить время хранения на 1–5 дней за каждый шаг.

обновить SSISDB. [каталог]. [catalog_properties]
установить property_value = 200
где property_name = ‘Retention_window’

Теперь, когда время удерживания уменьшилось, пришло время начать работу по очистке:

EXEC [внутренний]. [Cleanup_server_retention_window]

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

Насколько велика ваша база данных?

Глядя на размер различных файлов базы данных, вы получите только половину картины. Это просто размер, который занимает диск. В базе данных сохраняется свободное пространство, выделенное для повышения общей производительности. Когда вы удаляете большое количество записей, это больше не нужное пространство не возвращается в операционную систему. В SQL Server есть встроенные отчеты, чтобы узнать, какая часть размера файла действительно используется. Вы можете найти эти отчеты в Management Studio SQL Server в контекстном меню вашей базы данных:

Отчет выглядит примерно так, как на следующем рисунке, где свободное место отмечено зеленым цветом:

Уменьшение базы данных

Теперь мы, наконец, подошли к тому этапу, когда мы можем вернуть дисковое пространство операционной системе.SQL Server Management Studio предлагает простой способ сделать это.

Вернуться в режим первоначального восстановления

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

ALTER DATABASE [SSISDB] SET RECOVERY FULL WITH NO_WAIT
GO

Затем сделайте резервную копию теперь сжатой базы данных.Если что-то еще пойдет не так, вам не нужно переделывать всю работу по сокращению времени удерживания.

Не забудьте включить работу SSIS.

Далее

Учитывая неизбежную проблему с ограниченным дисковым пространством, пришло время выяснить, почему SSISDB начал так быстро расти. Возможно, произошел сбой одного или нескольких заданий SSIS. В этом случае каждое выполнение задания приводит к появлению нескольких сообщений с описанием ошибки. Выполнение этих заданий каждые пять минут в течение нескольких дней принесет много сообщений.В этом случае устраните проблему с заданием SSIS, и SSISDB перестанет занимать так много места.

Нравится:

Нравится Загрузка …

Связанные

Уменьшение файлов данных SQL Server — передовой опыт и почему это отстой — Блог Кендры Литтл и бесплатное обучение

В последнее время я получил несколько вопросов об уменьшении файлов данных SQL Server. Как лучше всего научиться бегать из термоусадки? И почему в некоторых случаях он может выйти из строя?

Традиционно, каждый раз, когда вы спрашиваете администратора базы данных, как сделать усадку меньше, они начинают разглагольствовать о том, что усадка — это плохо, и вам просто не следует этого делать.Иногда это звучит немного сердито.

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

Уменьшать файлы данных — отстой, и у вас действительно не так много способов сделать это отстойным

Вот с чем вы столкнетесь, когда дело доходит до усадки:

• Усадка может вызвать блокировку во время работы. Вот сообщение, которое я написал некоторое время назад с демонстрационным скриптом для воспроизведения блокировки.
• Процесс сжатия может перестать работать, и вы не сможете объяснить, почему.
  • В одном случае DBCC SHRINKFILE останавливалась, потому что зашла в тупик и объявлялась жертвой тупика. Однако это сообщение не отображалось в окне сообщений для сеанса, в котором запущен SHRINKFILE! Веселье? (Не совсем.)
• Сжатие фрагментов ваших индексов — если индексы большие и вы запускаете на них команду перестроения, вы в любом случае можете просто восстановить файлы данных.
• Если сжатие должно перемещаться по LOB-данным, это очень медленно. Пол Рэндал объясняет, почему здесь.
  • Обратите внимание, что вы можете нажать на это, даже если данные, которые вы удалили , не содержат данных LOB. Если ближе к концу файла есть LOB-страницы, SQL выберет эти страницы и переместит их в начало файла.

Это не ваша вина, что весь этот багаж в усадьбе. Вы просто хотите иметь базу данных меньшего размера. Но именно поэтому вы обычно стараетесь по возможности избегать сжатия.

Большинство старших администраторов баз данных заранее сохраняют свободное место в базе данных и регулярно архивируют данные

Планирование сокращается с каждым днем.

Рост и обработка данных никогда не должны зависеть от дискового пространства. Данные важнее места на диске!

• Регулярно очищайте данные, которые действительно больше не нужны.
  • Если у вас может быть ночной процесс, который удаляет старые данные небольшими партиями, а не раз в квартал, это обычно лучше для производительности. Это зависит от того, когда используется ваша база данных и от того, может ли процесс удаления данных блокировать пользователей.
  • Иногда компании желательно быстро удалить данные, которые больше не нужны по юридическим причинам.Однако это сильно зависит от отрасли.
• Регулярно измеряйте рост данных и начинайте оценивать, какой объем данных, вероятно, вырастет в будущем
• Оставьте свободное место в базе данных, чтобы обеспечить рост данных без увеличения размера файлов.
  • Это означает упреждающий запрос большего дискового пространства и упреждающее увеличение файлов. Вы можете делать это ежеквартально или один или два раза в год — в зависимости от того, что вам больше подходит, но установите напоминания и не пропускайте это.

Диаграмма роста данных и заблаговременное упреждающее управление пространством выводят вас из реактивного «режима сжатия».

Кроме того, ваше руководство будет хорошо выглядеть, если вы будете в курсе всего этого!

Иногда вам нужно сжать файлы данных

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

В тех случаях:

• Рассмотрите обходные пути. Иногда проще импортировать оставшиеся данные в новую базу данных во время простоя, в зависимости от того, сколько вы удаляете и сколько осталось.
  • Это также может позволить вам тщательно спланировать хорошую файловую группу и расположение файлов в новой базе данных.
• Определите малое время использования, вы можете запустить термоусадочную пленку.
• Используйте DBCC SHRINKFILE и установите конкретный целевой размер для сжатого файла.
• Следите за своими заданиями по резервному копированию и убедитесь, что они выполняются успешно и не занимают больше времени, чем обычно.
• Запланируйте, чтобы в файлах данных оставалось пустое место, чтобы обеспечить их рост в течение следующего года, а также разрешить выполнение перестроек индекса, если это необходимо, после завершения сжатия.Нет необходимости чрезмерно усаживаться, а затем снова расти.
• Следите за блокировкой, если у вас нет окна отключения
• Если вам нужно знать, сколько времени займет сжатие, восстановите копию базы данных в другом месте и протестируйте заранее, чтобы получить представление, но это все равно будет приблизительной оценкой.
  • Узнать, сколько LOB-данных придется переместить, извне сложно. Блокировка тоже имеет значение, как и скорость оборудования и диска, поэтому оценить ее очень сложно.
• Убедитесь, что вы регулярно запускаете DBCC CHECKDB. Потому что вы должны это делать! (Нет, сжатие не должно приводить к коррупции, но и небольшая осторожность тоже неплохая).

И, может быть, найдешь на Netflix шоу, которое можно будет посмотреть, пока работает ваш психоаналитик. То, что нужно всего лишь нескольким клеткам мозга, чтобы посмотреть, и что вы не прочь бы приостановить, если все станет напуганным.

.