Отрадно заметить, что хотя бы файлы данных (mdf) избавлены от этой «напасти» — «принудительного зануления» при создании/расширении файла. Ну хорошо, если вы требуете от автора абсолютной точности формулировок, то: начиная с SQL Server 2005 и при соблюдении ряда условий (описание которых вновь выходит за рамки данной статьи) файлы данных могут «наслаждаться» такой «фичей» как немедленная инициализация файлов (instant file initialization). Суть ее, или более корректно сказать, ее «профит» сводится к тому что файлы данных возникают на диске практически мгновенно (скажем 20-ти гиговый MDF-файл будет образован менее чем за секунду). До версии 2005-й и эти файлы подвергались «принудительному занулению» ровно как это происходит сейчас с журналом, но начиная с указанной версии был придуман механизм пропуска этой ресурсоемкой процедуры. К сожалению, ее, «фичу» эту, никак невозможно применить к журналу транзакций в виду особой логики работы последнего. Для него зануление неизбежно до тех пор пока не будет пересмотрено ядро всего сервера, а это, мыслится автору, произойдет не скоро. В качестве «утешительного приза» автор подскажет как вы можете хотя бы посмотреть на факты принудительного зануления журнала и отсутствия таковых у файлов данных.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | USE master go DBCC TRACEON (3004, 3605); GO CREATE DATABASE TstLog ON PRIMARY ( NAME = 'TstLog_Data', FILENAME = 'c:\sqlCMD.ru\TstLog_Data.mdf', SIZE = 5 MB, MAXSIZE = 5 MB ) LOG ON ( NAME = 'TstLog_Log', FILENAME = 'c:\sqlCMD.ru\TstLog_Log.ldf', SIZE = 1 MB, MAXSIZE = 1 MB ) GO ALTER DATABASE TstLog SET RECOVERY SIMPLE GO --clean up USE master go DROP DATABASE TstLog |
После этого заглянув в файл лога сервера (вот как раз сейчас — в SQL Server Logs, а не в журнал транзакций 
) вы, вне всяких сомнений, обнаружите там:
При желании можете расширить последний скрипт что бы вынудить лог-файл к разрастанию и убедиться, что каждый такой процесс будет приводить ко все новым строчкам в SQL Server Logs наподобие выделенных на последней иллюстрации. Однако, обратите внимание, что вы не должны обнаружить там упоминание о «Zeroing» по отношению к файлам данных (вариант, что во времена «царствования» SQL Server 2012 у вас до сих пор эксплуатируется сервер 2000-й автор решительно отказывается воспринимать всерьез)! Если таковые записи по отношению к файлам данных все же обнаруживаются, то это значит некорректную настройку системы и ее неготовность использовать упомянутую ранее «фичу» — instant file initialization. К сожалению рассказ о правильной настройке и приведение системы в состояние «instant initialization ready» лежит совсем уж далеко от сферы интересов данной публикации.
Стало быть, мы однозначно установили, что «прирезание» к лог-файлу (вне зависимости от его природы, будь то ручное или автоматическое) должно рассматриваться как операция из разряда «не бог весть», если не сказать жестче. Тут у многих читателей может родиться пара «гениальных» идей, как нивелировать последствия эффекта «прирезания» к лог-файлу новых кусков. Автор примерно догадывается какие это идеи и спешит если не отговорить вас от них, то, по крайней мере, осветить каждую из них и с «темной стороны» тоже.
Идея 1. «А я буду прирезать по 1MB (или типа). Что такое занулить 1MB!? Фигня!». Последнее утверждение не вызывает никаких возражений — можно только добавить «на постном масле». Современные высокоскоростные HDD даже особо не отвлекутся на столь мизерную операцию. И более того, если такое одномегабайтовое приращение будет происходить раз в месяц, а максимальный размер лог-файла через пять лет эксплуатации базы видится вам в районе мегабайт сорока — идея подлежит немедленному внедрению в промышленность. Единственное только — а сразу 40MB выделить, нет? Все таки хотите сначала 10, и каждый месяц по 1? Ну как скажете, все равно идея «прокатывает» на «ура».
К сожалению, в среднестатистической системе идея начинает «рушиться» после первого месяца эксплуатации. Дело в том, что прирезка 1MB это не только «лишний мег на диске», но и четыре новых VLF. А потом еще 4. И еще. И так пока их не станет тыщи полторы. И вот с этой точки времени вопрос «сколько длится зануление» будет уже даже не второстепенным, ибо «схлопываться» начнет менеджер виртуальных файлов, буквально «зафлуженый» таким числом своих «подопечных». И вот эта последняя проблема просто заслонит собой все остальные.
Идея 2. «А я, напротив, прямо в момент создания базы, когда еще ни одного пользователя нет, выделю весь диск на 16 терабайт только под лог-файл что бы снять все вопросы на ближайшие 5 лет, хитро?» Хитро! Хотя, если подумать — не очень. Во-первых, не все готовы резервировать 99.99% места на диске «под будущее». Во-вторых, предсказать объем транзакций через 5 лет невозможно даже теоретически. Если бизнес пользующийся базой «пойдет в гору» — может и 16TB не хватить. Однако самый главный «затык» — это наше в-третьих: если распределить наши 16TB сразу, в один «заход», то у нас будет 16 VLF. По одному терабайту каждый. Что может и похуже чем 1500 VLF-ов по 250MB каждый. Такие «мастодонты» так же управляются весьма тяжко, но уже не из-за своего числа, а из-за своего размера. А что еще хуже — пока MinLSN будет оставаться в одном из таких терабайтных VLF он не может быть усечен (напомню, что тема усечения лога будет завершающей частью цикла). То есть у нас давным-давно высвободились 900GB, а считается занятым весь терабайт, поскольку все еще «доиспользуются» его завершающие 100GB.
То есть понимаете как все «ловко» устроено? Число VLF должно быть «достаточным» (не слишком мало, но и не слишком много), да еще и размер каждого должен быть «достаточным» (не монструзным, но и не микроскопическим). Все становится еще «веселее», если мы вспомним что у нас нет прямых «рычагов влияния» ни на первую цифру, ни на вторую. Только косвенные. То есть задача перед DBA ставится примерно как «приберись в комнате, только дверь туда закрыта и ключа нет, только швабра». Еще интересный факт: нет и универсального цифрового выражения для понятия «достаточное число VLF». И — да, вы правильно догадались — того же самого нет и для понятия «достаточный размер VLF». Пока речь не зайдет о конкретной базе, с ее размерами, пользователями, транзакционной нагрузкой и прочими «обвесами» любые цифры будут описывать объем «сферического коня», причем в «вакууме». Для одной системы 20 VLF-ов — вполне OK, для другой — катастрофически мало. Для одной системы один VLF на 500MB — это просто за гранью обсуждаемого, для другой — вполне обычно, «а у нас и по гигабайту бывают». Заодно можно осознать и такую вещь: статьи и циклы статей подобного объема пишутся не от хорошей жизни (хотя эта — еще и небольшая, уверяю вас, для такой-то темы!). Просто нет иного выхода кроме как объяснить «а как оно внутри», что бы администратор мог осознано выбрать правильные значения для своей системы. Потому как рекомендация конкретная, вроде «начальный сайз в 1GB, приращение по 200MB» будут в самом лучшем случае для него не оптимальны, а в худшем — это будет для него и не рекомендация вовсе, а чистой воды вредительство.
Ладно, ну если не прямые рекомендации, и даже не косвенные, и даже не просто рекомендации, то хотя бы соображения какие-нибудь, «хинты» там или типа того автор может привести? Как жить в таком «страшном мире» полной неопределенности? Ну пару «хинтов» он для вас приготовил, однако еще раз напоминает — это все «средняя температура по больнице», причем «ну очень» средняя. Пользуйтесь полученными знаниями, пробуйте, создавайте тестовые макеты, думайте, считайте — вот путь к «вашим» цифрам.
Итак — «в среднем, и очень в среднем» об управлении файлом лога вообще и его ростом в частности:
- прямо со старта (т.е. в момент создания базы) выделяйте не просто достаточное количество места под LDF-файл, а с хорошим таким «заделом» а-ля «запас по-русски». Если согласно вашим расчетам (а проводить их вы теперь умеете!) в результате такого выделения у вас получится маленький «табунчик» «VLF-монстриков» (в смысле объема каждого из них), то примените иную тактику: изначально выделите LDF-файлу лишь треть запланированного места, а затем сразу же сделайте два «приращения» по трети каждый. В результате число VLF возрастет, а объем каждого сократится;
- закладывайтесь на то, что приращения вы будете делать в основном «вручную» в «не пиковые» часы работы системы (лучше — в моменты ее полного простоя);
- однако не отказывайтесь от автоприращения совсем: рассматривайте его как «подушку безопасности» на случай если вы «забудете», «проспите», «пропустите», «заболеете» и т.д.
- если у вас есть возможность выделить отдельный физический HDD только под LDF-файл — прекрасно, база вам однозначно «скажет спасибо»;
- даже если вы реализовали предыдущий пункт не отказывайтесь от ограничения размера LDF-файла «сверху» (опция MAXSIZE). Оставьте разумное число MB/GB дабы не оказаться в ситуации «ни места на HDD, ни нового (большего) HDD». Уж тем более не игнорируйте указанную опцию если на диске имеются другие файлы;
- иными словами, для каждого своего LDF-файла указывайте и SIZE, и MAXSIZE, и FILEGROWTH, да еще будьте готовы что последняя опция не сработает никогда — ведь вы же проповедуете проактивный менеджмент вашего сервера и будете все делать заблаговременно, не правда ли?
- используя FILEGROWTH в качестве «подушки безопасности» указывайте этой опции конкретное число MB/GB, не пользуйтесь процентами! Причина очень проста: у вас и без того имеется лишь косвенное влияние на число/размер VLF, процентное приращение возводит эту косвенность в квадрат;
- при ручном прирезании пользуйтесь формулой «прирезаемый кусок»=0.9*«существующий LDF-файл». То есть 90% от имеющегося. Опять же — больше креатива! Что лучше: +90% за один заход или +30% и повторить три раза?
- считайте общее число VLF в вашем LDF-файле (файлах) до ста как «в рамках допустимого». Более ста — возможный (только! никаких гарантий) признак ошибочности выбранной стратегии увеличения размера лог-файла;
- считайте общее число VLF в LDF-файле в районе одной тысячи достаточно надежным признаком близкой катастрофы и поводом сменить вашу стратегию на диаметрально противоположную;
- считайте что «минимально комфортный» размер одного VLF равен одному мегабайту. Иными словами старайтесь не прирезать в рамках одной операции кусок менее 4MB, поскольку только он обеспечит приемлемый минимальный размер одного VLF;
- считайте что «максимально комфортный» размер одного VLF равен 640 мегабайтам. Иными словами старайтесь не прирезать сразу кусок более 10GB, поскольку 10GB=10240MB/16VLF=640MB — будет как раз размер одного VLF;
- помните, что со временем «стиль работы» пользователей с системой может сильно измениться, а поэтому время от времени пересматривать текущую стратегию приращений и вносить в нее коррективы — нормально и даже необходимо.
Наконец — время «хороших новостей». После того, как стратегия приращения лог-файла выработана и утверждена вами, реализовать ее на практике действительно «проще пареной репы», команды T-SQL для этого даже не просты, а элементарны. Вот как можно вручную увеличить размер лог-файл с 1MB до 15MB, а затем до 29MB и плюс еще поменять значение для автоприращения:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | USE master go CREATE DATABASE TstLog ON PRIMARY ( NAME = 'TstLog_Data', FILENAME = 'c:\sqlCMD.ru\TstLog_Data.mdf', SIZE = 5 MB, MAXSIZE = 5 MB ) LOG ON ( NAME = 'TstLog_Log', FILENAME = 'c:\sqlCMD.ru\TstLog_Log.ldf', SIZE = 1 MB, FILEGROWTH = 5MB, MAXSIZE = 100 MB ) GO ALTER DATABASE TstLog SET RECOVERY SIMPLE GO --добавляем к лог-файлу 14MB, финальный размер - 15MB ALTER DATABASE TstLog MODIFY FILE (NAME=TstLog_Log, SIZE=15MB, MAXSIZE = 100 MB) GO --добавляем к лог-файлу еще 14MB, финальный размер - 29MB; автоприращения теперь будут по 12MB ALTER DATABASE TstLog MODIFY FILE (NAME=TstLog_Log, SIZE=29MB, FILEGROWTH=12MB, MAXSIZE = 100 MB) GO --clean up USE master go DROP DATABASE TstLog |
Код настолько ясен, что даже не требует каких-либо пояснений.