Msg 9002 The transaction log for database 'myDataBase' is full due to 'ACTIVE_TRANSACTION

Есть база данных myDataBase в ней есть таблица myTable в таблице около двух миллионов записей. При попытке добавить столбец к этой таблице
ALTER TABLE dbo.myTable ADD myTable_id int identity(1,1) not null primary key
получаю ошибку:
Msg 9002 The transaction log for database 'myDataBase' is full due to 'ACTIVE_TRANSACTION'.**
Из сообщения понятно что переполнен журнал transaction log
После этого увеличил размер log файла:
ALTER DATABASE myDataBase MODIFY FILE (NAME = myDataBase_log , MAXSIZE = 170MB) GO
и применил команду:
CHECKPOINT DBCC SHRINKFILE ('myDataBase_log')
До выполнения команды: ALTER TABLE dbo.myTable ADD myTable_id int identity(1,1) not null primary key размер log файла 27MB после 150MB и всё равно ошибка Msg 9002. Подскажите как в данной ситуации поступить:
Увеличивать размер log файла? если увеличивать то на сколько? Нормальное ли это поведение log файла?

Ответ

Да, это нормальное поведение.
SQL Server немедленно пишет все вносимые вами изменения в transaction log.
Ваш ALTER требует полного пересоздания таблицы - т.к. вы добавляете кластерный индекс. Соответственно, в лог будут записано удаление старых данных + вставка их заново, с попутным пересозданием всех индексов. Так что объем лог-файла стоит увеличить минимум до 2x размера таблицы (лучше 4x).
Я бы на вашем месте выставил размер лога не исходя из размера базы, а исходя из доступного места на диске (т.е. все свободное место минус минимальный запас). или вообще снял ограничение.
Размер таблицы + размер индексов можно посмотреть в View / Object Explorer Details в Management Studio.