SQL Server FILESTREAM и уменьшение размеров хранилища файлов

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

Не так давно на проекте S решили использовать FILESTREAM. Проект старый и изначально файлы хранились внутри базы. Почему такое решение было принято мне трудно сказать т.к. дело было давно и принималось это решение другой командой программистов.  Лично я бы постарался всячески этого не делать т.к рано или поздно файлов станет много и база станет большой и медленной. И вообще контролировать хранение файлов лучше программно, хотя это и труднее.
База проекта S в итоге стала весить около 80 гигов и регулярные бекапы базы стали большой проблемой. Решили перейти на FILESTREAM и перешли.  База на сервере, в части данных, усохла до 5 гигов. Ну и остальные файлы теперь поселились в файловой системе сервера.  Результатом остались довольны.  Локальная база (сильно урезанная для целей разработки) тоже благополучно обновилась миграциями и все заработало.

Через некоторое время в один прекрасный воскресный вечер я решил установить себе языковое  обновление на свою Windows 7 Home Premium т.е. захотел всего-то чтобы моя винда была на английском языке, вот такая вот странная прихоть?! Да и чем же еще заняться в воскресенье вечером? В этой идее была одна проблема:  W7 Home Premium смену языков интерфейса явно не поддерживает, да и не явно видимо тоже, как покажут дальнейшие события.

Создал точку восстановления системы (предчувствовал не ладное) и  установил языковое обновление вручную. Перезагрузился и … ничего не заработало. Черный экран и  гнетущая пустота монитора. Ну и дальше были попытки использовать точку восстановления системы, а она вдруг пропала куда-то, встроенный бекап отказался восстанавливаться и т.п.  Короче винда умерла прожив на этом свете около 5 лет, что кстати много для винды.

Далее меня ждали три весёленьких дня для полного восстановления системы и всех программ и конфигураций. Процесс  очень нудный, но такой же нужный как и нудный. На второй день восстановления  дошла очередь  до подсоединения(attach) баз в новый экземпляр SQL Server.  И тут сюрприз, моя локальная база проекта S не атачится из-за FILESTREAM. Обычно это процесс занимает несколько секунд, а у меня ушел почти целый день, чтобы понять что дело гиблое. Чтение статей в MSDN, Google и Stack Overflow не помогли.
Короче, стал вопрос где себе взять локальную базу нормального размера, чтобы можно было ее скачать и развернуть не занимая пол моего винта.

База была на продакшене  и нужно было взять ее предварительно убрав хранилище. Но в процессе по поводу FILESTREAM выяснилось следующее:  бекап боевой базы, не смотря на то, что файлы/изображения хранятся отдельно все равно надо делать вместе с ними, а вот без файлов, у нашего админа развернуть не получилось. Т.е. бекап будет большой и мне тащить на локальный комп придется этакого монстра. А зачем мне изображения и прочие файлы для целей разработки и отладки?!

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

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

Нужно было другое решение. И оно нашлось. А что если удалить контент из файлов? А сами файлы пусть так и лежат размером в 0 байт.

Для удаления контента из файлов можно написать консольное приложение на C#, а можно и использовать скрипт Windows PoweShell.

Скрипт для версии PowerShell  1.0 (для версии 3.0 было бы не сколько проще, но не у всех заработает)

Удаляет содержимое файлов рекурсивно от родительской директории, игнорируя при этом файлы FILESTREAM.

$files = Get-ChildItem -Path "D:\Temp\ BaseName.ndf" -Recurse -Exclude "*.hdr" | where { $_.GetType().Name -eq "FileInfo" }

ForEach ($file in $files) { Clear-Content $file.FullName }

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

$files = Get-ChildItem -Path "D:\Temp\ BaseName.ndf" -Recurse -Exclude "*.hdr" | where { $_.GetType().Name -eq "FileInfo" }

ForEach ($file in $files) { Write-Host $file.Name }

Для версии PowerShell 3.0 и старше можно использовать параметр -File

$files = Get-ChildItem -Path "D:\Temp\ BaseName.ndf" -Recurse -Exclude "*.hdr" -File

ForEach ($file in $files) { Clear-Content $file.FullName }

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

Спасибо за внимание.  Если вам понравилось или не понравилась данная статья напиши об этом в комментариях.

SQL Server FILESTREAM и уменьшение размеров хранилища файлов: 2 комментария

  1. Понравилась статья. Интересно! Но делая вывод из статьи — лучше делать свое хранилище и управлять им через консольное приложение.

    1. Да, пожалуй так. Возможно это и сложнее на начальном этапе, но гибкость перевесит прочие сложности.

Добавить комментарий для Cергей Отменить ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *