Самый быстрый шринк на Диком Западе

Публикация № 1031815

Администрирование - Оптимизация БД (HighLoad)

SQLServer shrink шринк база данных

65
Шринк (shrink) базы данных. Наглядное объяснение что это, зачем, когда применять и как это можно ускорить.

Шринк? Что это и зачем?

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

 
 Не путать понятия!

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

Почему шринк не стоит делать регулярно на рабочем окружении? Вот основные причины:

  1. Сама по себе операция сжатия файла данных очень ресурсоемка и на больших базах может выполняться десятки часов, а то и больше. Также во время ее выполнения пользователи могут, и скорее всего будут, ощущать замедление работы системы.
  2. Значительное снижение эффективности работы индексов. Шринк переносит страницы в начало файла, так он может перенести и индекс. Причем часть его страниц может быть в самом начале файла, другая где-то по середине, а третья вообще разбросана где попало. В итоге фрагментация индекса будет под 99.9%, что значительно снижает производительность. Индексы попросту не могут быть использованы должным образом. Спасти может только перестроение или реорганизация (иногда), но это снова может увеличит размер файла данных. Есть отличная статья об этом от Brent Ozar.
  3. Появление дополнительных задержек при увеличении файла данных. После шринка база занимает минимальный размер, но если информационная система жива, то новые данные в нее будут поступать снова и снова. Новые данные = нужно больше места. Каждое увеличение файла данных потребует дополнительных ресурсов. Оптимизировать можно уменьшив количество таких операций за счет большего шага автоувеличения файла в настройках базы данных. О влиянии на производительность можно узнать вооооот здесь.

Постойте! Но статья же о быстром шринке! Если сам по себе он так плох, то неужели статью можно уже закончить?

Конечно, нет! Бывают ситуации, когда шринк для базы целесообразен. Замете, целесообразен, но не обязателен!

Вот несколько кейсов, когда его стоит использовать:

  1. В базе данных произошли серьезные изменения. Например, вы удалили какой-либо исторический регистр из базы 1С, который занимал 100 ГБ. И если эти 100 ГБ важны и их нужно освободить на диске, то от шринка не уйти.
  2. Вы применили сжатие страниц для таблиц и индексов, что уменьшило размер занимаемых данных на 70%! А файл базы данных на диске не изменился! Снова шринк! Но опять же, если место и правда нужно под что-то освободить, ведь рано или поздно данные в базе смогут занять его снова.
  3. Вы готовите тестовую базу и удаляете из нее данные, чтобы она не весила 1ТБ. Без шринка тоже никуда, но вопрос производительности может быть неактуальным. Ведь это тестовая среда.

Вообщем, смысл думаю уже понятен.

Классический путь

Стандартный способ выполнить шринк файлов базы данных - воспользоваться такими командами как "SHRINKDATABASE" или "SHRINKFILE". Вот примеры.

-- Первым параметром указываем имя базы данных. 
-- Вторым параметром указываем сколько процентов свободного пространства
-- данных мы хотим освободить
DBCC SHRINKDATABASE ('DatabaseName', 10);  

или

-- Первым параметром указываем логическое имя файла. 
-- Вторым параметром указываем целевой размер 
-- для сжатия файла в мегабайтах.
DBCC SHRINKFILE ('LogicalFileName', 100)

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

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

Также для каждой операции могут быть указаны дополнительные параметры, о который вы можете узнать по ссылкам:"SHRINKDATABASE" или "SHRINKFILE".

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

Ах да, ни в коем случае не ставьте в настройках базы данных включенной опцию "Auto Shrink".

ALTER DATABASE [bsl] SET AUTO_SHRINK OFF WITH NO_WAIT

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

Быстрее, выше, сильнее

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

Речь идет о переносе данных из одной файловой группы в другую, при котором есть два пути:

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

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

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

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

-- Добавляем файловую группу
ALTER DATABASE [bsl] 
	ADD FILEGROUP [FOR_SHRINK]
GO

-- После добавляем новый файл данных для новой файловой группы
ALTER DATABASE [bsl] 
	ADD FILE ( 
		NAME = N'AfterShrink', 
		FILENAME = N'F:\DBs\MSSQL14.MSSQLSERVER\MSSQL\DATA\AfterShrink.mdf' , 
		SIZE = 8192KB , 
		FILEGROWTH = 65536KB ) 
	TO FILEGROUP [FOR_SHRINK]
GO

Теперь с помощью этих скриптов мы можем переместить все таблицы, индексы и даже BLOB-данные в эту файловую группу вот так.

 EXEC dbo.sp_MoveTablesToFileGroup
    -- Фильтр по имени схемы (LIKE-оператор)
    @SchemaFilter = '%',
    -- Фильтр по имени таблицы (LIKE-оператор)
    @TableFilter  = '%',   
    @DataFileGroup = 'FOR_SHRINK', -- Имя файловой группы назначения (куда переносим)
    -- 1 означает "Перенести все кластерные индексы", то есть таблицы, где есть первичный ключ / кластерный индекс
    @ClusteredIndexes = 1,
    -- 1 означает "Переместить все дополнительные индексы"
    @SecondaryIndexes = 1,
    -- 1 означает "Перенести все кучи" - то есть таблицы без кластерного индекса.
    @Heaps = 1,
    -- 1 - значит только сгенерировать скрипт, ничего выполнять не нужно. 0 - сразу выполнить перемещение
    @ProduceScript = 0

Скрипт не универсальный, по крайней мере в текущей его версии, и не может перенести таблицы, в которых, например, только одно поле с BLOB-типом. Для баз 1С это таблица "DBSchema" с описанием структуры базы данных, которую автоматически в новую файловую группу переместить нельзя. Для этого нужно выполнить немного ручной работы:

BEGIN TRANSACTION
GO

CREATE TABLE dbo.Tmp_DBSchema
	(
	SerializedData varbinary(MAX) NOT NULL
	)  ON FOR_SHRINK
	 TEXTIMAGE_ON FOR_SHRINK
GO

ALTER TABLE dbo.Tmp_DBSchema SET (LOCK_ESCALATION = TABLE)
GO

IF EXISTS(SELECT * FROM dbo.DBSchema)
	 EXEC('INSERT INTO dbo.Tmp_DBSchema (SerializedData)
		SELECT SerializedData FROM dbo.DBSchema WITH (HOLDLOCK TABLOCKX)')
GO

DROP TABLE dbo.DBSchema
GO

EXECUTE sp_rename N'dbo.Tmp_DBSchema', N'DBSchema', 'OBJECT' 
GO

COMMIT

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

-- Сжимаем файл основной файловой группы 'PRIMARY'
DBCC SHRINKFILE('bsl', 0)

В итоге все данные перемещены в новую файловую группу, что можно проверить с помощью этого скрипта.

По ссылке выше в своей статье Paul S. Randal этот способ рекомендовал использовать вместо стандартного сжатия данных. Так почему бы не прислушаться? Если бы исходную файловую группу можно было бы удалить (если это не "PRIMARY"), то можно было бы сделать следующее.

-- Удаляем все данные из файла перенося их в другие файловые группы
-- Подробнее о параметре смотреть здесь:
-- https://docs.microsoft.com/ru-ru/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017
DBCC SHRINKFILE ('AfterShrink', EMPTYFILE);
GO

-- Удаляем пустой файл
ALTER DATABASE [bsl]  REMOVE FILE [AfterShrink]
GO

-- Удаляем файловую группу
ALTER DATABASE [bsl] REMOVE FILEGROUP [FOR_SHRINK]
GO

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

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

База в базу

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

  1. Использовать Bulk-операции, как, например, описано здесь.
  2. Штатный мастер импорта и экспорта данных
  3. Использовать утилиту "sqlpackage.exe", входящую в состав SQL Server Data Tools.
  4. Сгенерировать скрипт с помощью SQL Server Managment Studio
  5. Использовать операцию "INSERT INTO".

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

Вы все еще шринкуете?

На этом все. Думаю, в публикации не открыл особо ничего нового для сообщества, но может хотя бы скрипты пригодятся. Главное помнить - шринку нет места в продакшене! Хватит шринковать! :)

Другие ссылки

65

См. также

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. Hekeus 17.04.19 09:33 Сейчас в теме
Поставил плюс. Вот только мой шринк быстрее и эффектнее: drop database!
succub1_5; YPermitin; +2 Ответить
2. YPermitin 3031 17.04.19 09:34 Сейчас в теме
3. asved.ru 36 17.04.19 09:50 Сейчас в теме
(1) Для внедрений, хранящих БД на механике, существуют устройства, реализующие аппаратный шринк. Вот например.
4. bykrash 17.04.19 09:55 Сейчас в теме
По ссылке описание и скрипт поэтапного сжатия файла бд https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия идут даже после отсутствия свободного пространства.
5. bykrash 17.04.19 10:00 Сейчас в теме
Код и описание скрипта пошагового сжатия файла БД https://www.sqlshack.com/shrinking-your-database-using-dbcc-shrinkfile/. Немного доработал, так как попытки сжатия не прекращаются при отсутствии свободного места.
USE [DBname]
DECLARE @FileName sysname = N'LogicalFileName';
DECLARE @TargetSize INT = (SEL ECT 1 + size*8./1024 FR OM sys.database_files WHERE name = @FileName);
DECLARE @Factor FLOAT = .99;
DECLARE @ActualSize INT = 0;
 
WHILE @TargetSize > @ActualSize
BEGIN
    SET @TargetSize *= @Factor;
    DBCC SHRINKFILE(@FileName, @TargetSize);
	SET @ActualSize = (SELECT 1 + size*8./1024 FR OM sys.database_files  Wh ere name = @FileName) * 0.99; 
    DECLARE @msg VARCHAR(200) = CONCAT('Shrink file completed. Target Size: ', 
         @TargetSize, ' MB. Actual size: ', @ActualSize, 'MB. Timestamp: ', CURRENT_TIMESTAMP);
    RAISERROR(@msg, 1, 1) WITH NOWAIT;
     WAITFOR DELAY '00:00:01';
END;
Показать
artichoke; YPermitin; +2 Ответить
6. YPermitin 3031 17.04.19 10:02 Сейчас в теме
(5) шринкуете на постоянной основе? :)
7. bykrash 17.04.19 10:06 Сейчас в теме
(6) Да, сжатие как этап при переносе с прода на тест.
YPermitin; +1 Ответить
13. user-z99999 18 17.04.19 10:28 Сейчас в теме
(7)
Сжатие - это архивирование т.е. базе будет нужно каждый раз, при обращении к данных, распаковывать данные.
Будет тратится время ...

Либо на тесте - использовать полную копию рабочей базы (не нужно сжимать),
либо - обрезать таблицы за предыдущие года, оставлять последний год (месяц).
15. bykrash 17.04.19 10:45 Сейчас в теме
(13) В данном случае архивирование - это то, что нужно, т.к. процессорных мощностей в избытке, а вот дисковое пространство нет (тестовая среда работает на ssd). Полная копия рабочей базы (одной из) занимает ~600 гб и пока от предыдущих данных избавиться нельзя.
8. triviumfan 10 17.04.19 10:08 Сейчас в теме
SQL 2014
В плане обслуживания присутствует "Сжатие БД".
Никакого "Значительного снижения эффективности работы индексов" не замечено.
Проверим статистику индексов самых популярных таблиц.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
9. YPermitin 3031 17.04.19 10:10 Сейчас в теме
(8) утверждаете, что все что в стать "от лукавого"?
10. triviumfan 10 17.04.19 10:13 Сейчас в теме
(9) У меня нет глубоких знаний и опыта администрирования БД. Просто факт. Но похоже, что "Сатана тут точно замешан".
YPermitin; +1 Ответить
11. YPermitin 3031 17.04.19 10:17 Сейчас в теме
(10) тут на самом деле два варианта:
1. вам повезло и при шринке данные индексов не были перемещены.
2. Вы делали шринк без перемещения данных, который только освобождает место в конце файла. При этом пустое место внутри файла не затрагивается.

Подробнее посмотрите статью Brent Ozar по ссылке в публикации. Она хоть и на английском, но все хорошо показано о влиянии на индексы.
14. triviumfan 10 17.04.19 10:43 Сейчас в теме
(11)
Brent Ozar

Натыкался я на него однажды, ютуб канал ведёт. Непутёвый он.
(12) После. В понедельник выполнялся, 2 дня прошло. Этот план выполняется только по понедельникам.
Прикрепленные файлы:
maksa2005; YPermitin; +2 Ответить
16. YPermitin 3031 17.04.19 10:57 Сейчас в теме
36. Дмитрий74Чел 147 18.04.19 17:48 Сейчас в теме
(11) да у него база 51Мб, плюс в базе оставляет 10% места. И статистику проверил лишь по 3м объектам.
Я читал товарища Брантозавра. И тест выполнил на рабочей базе размером более 200Гб. После этого пришел к админам и смог на цифрах подтвердить - почему я и раньше был против регулярного шринка базы.
12. bykrash 17.04.19 10:26 Сейчас в теме
(10) Статистика собрана после выполнения плана в котором отработало сжатие? Если план ежедневный, на следующий день после сжатия у вас произойдет обсуживание индексов и статитика по идексам будет в норме. Кстати, возможно вы забыли в плане обновление статистики и очистку кэша?
18. nicxxx 227 17.04.19 12:35 Сейчас в теме
(8) Сколько строк в таблицах?
22. triviumfan 10 17.04.19 15:37 Сейчас в теме
(18) достаточно: документов 100к и цен 10кк. Базе более 10 лет.
17. ids79 2594 17.04.19 11:50 Сейчас в теме
Спасибо, хорошая статья.
YPermitin; +1 Ответить
19. YPermitin 3031 17.04.19 14:12 Сейчас в теме
20. nvv1970 17.04.19 14:19 Сейчас в теме
Прочитал по диагонали в надежде уловить, какую задачу вы решаете?
Чем база со свободным местом "хуже", чем без него?

Предполагая, что база наполняется данными, а не пребывает в RO состоянии, что реиндексация способна генерировать большое расширение пространства базы, делаю вывод, что шринк файла данных не нужен чаще, чем "никогда".
А превентивное выделение пространства? А fillfactor? А 24/7 и отсутствие "техокон"? А.......

В чем тогда смысл шринка? Давайте холиварить )))
Лишние (свободные) 50 Гб заполнятся гораздо быстрее, чем DBA потратит время на борьбу за экономию места

PS: а статья да... хорошая ))

PPS_UPD: все нашел строки про "смысл" )))) согласен
YPermitin; +1 Ответить
21. YPermitin 3031 17.04.19 14:22 Сейчас в теме
(20) холивар можно быстро закончить.

Вот удалили Вы из базы файлы размером 300 ГБ, и чтобы место на серверном SSD освободить под некоторую другую задачу. - нужно сделать шринк за минимальное время.

Никогда не говори никогда! =D
23. 3vs 17.04.19 16:57 Сейчас в теме
"Главное помнить - шринку нет места в продакшене! Хватит шринковать! :)"
Да, вот товарищ тоже с этим согласен:
"Почему вы не должны сжимать ваши файлы данных"
habr.com/ru/post/330492/

По сути шринк, получается, та же дефрагментация диска.
Лишний напряг железа - износ механики и нагрева электроники жёстких дисков, что снижает
надёжность.
Один штатовский товарищ считает по тому же поводу, что любой RAID кроме RAID0 - зло
в плане долгожительства жёстких дисков.

Кстати, сегодня была новость, что импортные медики выявили закономерность внезапной смерти человека от его пульса, человек, у которого пульс в покое 55 ударов в минуту, даже если пьёт и курит и т.п. имеет шансов дожить до глубокой старости больше, чем тот, у кого в покое пульс 75 ударов в минуту.
Сердечко, видимо, тоже имеет свой ресурс по количеству сокращений...

Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.
YPermitin; +1 Ответить
24. TODD22 17 17.04.19 17:03 Сейчас в теме
(23)
Да, затратно, но в плане долговечности дисков, возможно выигрыш.

Диски в серверах уже давно расходный материал.
YPermitin; +1 Ответить
25. 3vs 17.04.19 17:04 Сейчас в теме
(24)Это у кого как, к сожалению...
YPermitin; +1 Ответить
26. TODD22 17 17.04.19 17:09 Сейчас в теме
(25)
Это у кого как, к сожалению...

У тех у кого объёмы данных диски до дыр затирают, остальным смысла нет беспокоится.

У меня серверные диски выходили из строя на сервере на котором 4 буха иногда считали ЗП и вели бух учёт примерно два раза в неделю. И база была что то под 500Мб. Однако пара серверных дисков за пару лет вышли из строя.
YPermitin; +1 Ответить
27. 3vs 17.04.19 17:26 Сейчас в теме
"Однако пара серверных дисков за пару лет вышли из строя."
От брака никто на застрахован!
Мы вот тоже наскребли немного денег на два серверных SSD,
так вот один был в плёнке в упаковке, другой без плёнки.
Который был в плёнке, работает третий год.
Который был без плёнки, был неисправен, когда даёшь нагрузку, он вообще пропадал
из системы и биоса, выключаешь сервер, отключаешь питание, снова появляется...
Заменили, правда прошло пара месяцев и на другой тип, так как замены уже не было.
Хотя в фирме, где диски покупали, сказали, что тестировали диск несколько часов, ничего не отваливалось.
По моему настоянию диск, таки отправили на завод, откуда сообщили, что да, брак...
YPermitin; +1 Ответить
28. YPermitin 3031 17.04.19 20:01 Сейчас в теме
(23)
Юрий, а нет более радикального решения избавления от фрагментации - иметь, к примеру,
два идентичных дисковых массива, один рабочий а другой пустой, на который периодически
сливаются данные с первого массива, но так, чтобы при записи на пустой массив происходила уже дефрагментация, после чего в работу запускается бывший пустым массив с дефрагментированными данными, а первый, рабочий массив, очищается и ждёт своей очереди по включению в работу?
Да, затратно, но в плане долговечности дисков, возможно выигрыш.


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

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

Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)
29. 3vs 17.04.19 20:12 Сейчас в теме
"Но возможно и есть какое-то решение по Вашей схеме, надо думать и экспериментировать :)"
Надо экспериментировать! :-)
Я в Вас верю, всё получится! :-)

Давайте беречь жёсткие и твердотельные диски! :-)
В принципе, есть и дополнительный стимул - пока данные на грязном
дисковом массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...
YPermitin; +1 Ответить
30. YPermitin 3031 17.04.19 20:16 Сейчас в теме
(29)
массиве не грохнули, он вам будет копией базы, разумеется без
новых данных, но всё таки...


Ну вот, весь энтузиазм убили :D
31. 3vs 17.04.19 20:20 Сейчас в теме
(30)Не, это побочный эффект! :-)

Можно, даже, для экономии электроэнергии пустой массив отключать!
Хотя, некоторые говорят, что лучше не выключать - установившийся режим лучшее для жёстких дисков, а старт/стоп могут приводить к неисправностям.
Был давно у нас такой случай, сервер работа себе и работал, решили пропылесосить, всё, жёсткий диск больше не включился...
32. genayo 18.04.19 06:22 Сейчас в теме
Что плохого в том, чтобы делать шринк например раз в месяц в технологическое окно? SSD не резиновые так-то :))
33. nicxxx 227 18.04.19 07:32 Сейчас в теме
(32) Написано же, фрагментация 99.9%. Если с highload-ом не сталкивались, то это не очень страшно. А вот когда к диску пойдет 10000 запросов в секунду, а строк в таблицах будет по миллиарду.....
34. genayo 18.04.19 08:59 Сейчас в теме
(33) Ну то есть это не про 1С :))
35. nicxxx 227 18.04.19 09:01 Сейчас в теме
(34) Про 1С, но таких компаний немного. Спроси у брокеров, если есть знакомые, например, БКС или Открытие, как они свои базы 1С обслуживают.
37. YPermitin 3031 18.04.19 20:54 Сейчас в теме
(35) есть еще на просторах родины Highload в 1С =)
38. DonAlPatino 53 23.04.19 16:03 Сейчас в теме
А можно вопросик почти в тему? Что shrink (обрезание) файла для фрагментации индексов очень плохо - это понятно. А как повлияет backup transaction log при full модели восстановления на фрагментацию? Ведь по логике тоже самое происходит... Может кто-нибудь ткнуть носом в хорошую статью по этой теме.
YPermitin; +1 Ответить
39. YPermitin 3031 23.04.19 20:16 Сейчас в теме
(38) нет, шринк лога транзакций не влияет на фрагментацию в файле данных, также не практически не влияет на фрагментацию в самом файле журнала. Но есть нюансы.

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

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a538cb11-634d-4f5b-91d6-b2257d903783/shrinking-the-log-file-and-fragmentation?forum=sqltools

Но шринк лога транзакций не должен быть регулярной операцией. Зачем? Если это полная модель восстановления, то достаточно делать бэкап лога, тогда данные в нем будут перезаписываться, если так можно выразиться.
40. DonAlPatino 53 23.04.19 20:40 Сейчас в теме
(39) Дык я и не говорил про шринк лога транзакций. Я спрашивал про backup transaction log и его влияние на фрагментацию индексов.
Скажем в лоб - делаем rebuld index, а потом backup transaction log. Соответственно все данные из transaction log переносятся в файл с данными. И что происходит с индексами в этот момент?
YPermitin; +1 Ответить
41. YPermitin 3031 23.04.19 23:18 Сейчас в теме
(40) понял.

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

На производительность это может влиять и есть рекомендации по этому поводу.

Тут дальше можно отдельную статью написать :)
Пару лет назад смотрел вот это: https://www.youtube.com/watch?v=WnLupnOoPXw

Там где-то в видео было подробное описание про влияние бэкапирования на лог транзакций и как с этим бороться. Рекомендую посмотреть.
42. DonAlPatino 53 24.04.19 11:36 Сейчас в теме
(41) Провел тест с утра... Выгрузил базу в бэкап, отребилдил индексы скриптом с https://gallery.technet.microsoft.com/scriptcenter/Script-for-rebuilding-all-8d079754, посмотрел степень фрагментации, сделал backup transaction log, посмотрел степень фрагментации ... один в один до backup. В общем похоже backup transaction log на фрагментацию индексов никак не влияет.
Вот что интересно.. в процессе обнаружил, что ребилд скриптом работает значительно лучше чем rebuild index task из Maintance Plan... ибо на обработанной rebuild index task базе снизил фрагментацию раза в два.
43. YPermitin 3031 24.04.19 12:51 Сейчас в теме
(42) мы говорим на разных языках похоже.

Я говорю про фрагменьацию в файле логов.

В файле данных на фрагментацию не влияет, это я еще в прошлом комментарии написал.

Посмотрите видео, там ответы на все ваши вопросы.
44. СергейК 51 24.04.19 23:01 Сейчас в теме
Спасибо за статью.
Натолкнуло на интересную идею:
Для создания тестовой базы минимально возможного размера использовать метод копирования из рабочей в тестовую базу, предварительно в тестовой создав таблицы и применив к ним сжатие, а только потом заливка данными.
Идеально если применительно к своей конфигурации можно было бы исключать некоторые таблицы от копирования, а некоторые ограничить фильтром, например по дате.

Если у кого есть (полу)готовые наработки, поделитесь плз.

p.s. В виду того что тестовая база на другом SQL сервере и соединение между ними 1ГБит, требует проверки скорость такого копирования для базы 400ГБайт...
Оставьте свое сообщение