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

#sql #sql_server

                    
Есть база данных 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 файла?

    

Ответы

Ответ 1

Да, это нормальное поведение. 

SQL Server немедленно пишет все вносимые вами изменения в transaction log.

Ваш ALTER требует полного пересоздания таблицы - т.к. вы добавляете кластерный индекс.
Соответственно, в лог будут записано удаление старых данных + вставка их заново, с
попутным пересозданием всех индексов. Так что объем лог-файла стоит увеличить минимум
до 2x размера таблицы (лучше 4x). 

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

Размер таблицы + размер индексов можно посмотреть в View / Object Explorer Details
в Management Studio.