Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сильно растет лог  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Всем добрый день.

При изменении типа поля с int на bigint
alter table [dbo].[***] alter column [idObject] bigint  not  null
сильно растет лог.

Таблица 43кк записей 1.4 гига. Лог вырастает на 30 гиг. Откуда???

Проблема в том, что данную операцию надо провести и на таблицах большего размера - до 300кк строк и 100 гиг.

Профайлер показывает, что в этот момент идет операция
UPDATE [***].[dbo].[***] SET [idObject] = [idObject]

Насколько я понимаю, при alter column сервер добавляет колонку bigint с новым именем, апдейтит ее, старую удаляет а новую переименовывает. Но откуда берется 30 гиг лога на таблице в 1.4 гига???

Триггеров нет, индексы убиты, ключи отключены.

Какие еще есть возможности изменить тип поля, не требующие большого объема свободного места? И не будет ли быстрее и менее затратно создать новую таблицу, перегнать в нее данные и переименовать?
16 ноя 11, 15:00    [11606518]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
1. по кусочкам :) т.е. не всю таблицу апдейтить а ее часть...


ps Забегая вперед скажу: модели нет смысла менять - т.к. даже если поставите Simple лог будет также расти, т.к. Вы похоже апдейтите одной транзакцией...
16 ноя 11, 15:10    [11606635]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Shakill
Member

Откуда: мск
Сообщений: 1887
SanyL, ну ALTER COLUMN по частям сделать не получится, автор же изначально про это спрашивает

Anddros, можно создать в той же таблице bigint столбец, и вот уже его заполнять порциями, а потом дропнуть старый стобец и переименовать новый
16 ноя 11, 15:14    [11606672]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
Anddros,

Какая модель восстановления на БД? Сервер протоколирует все операции:
1. Добавление нового столбца
2. Апдейт
3. Удаление старого.

Но если модель восстановления Full, то записи не сбрасываются в логе, а остаются. Если модель восстановления Simple, то можно просто шринконуть лог.
Выполните
with spaceused as(
select
	a.FILEID,
	[FILE_SIZE_MB] = 
		convert(decimal(12,2),round(a.size/128.000,2)),
	[SPACE_USED_MB] =
		convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
	[FREE_SPACE_MB] =
		convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
	NAME = left(a.NAME,128),
	FILENAME = left(a.FILENAME,520),
    FILE_GROUP = fg.name,
	STATE_DESC = df.state_desc,

	[DEFAULT] = fg.is_default 
from
	sys.sysfiles a
left join sys.database_files df on df.file_id = a.fileid
left join sys.filegroups as fg on fg.data_space_id =  df.data_space_id
) 
select 
	FILEID, 
	FILE_SIZE_MB,
	SPACE_USED_MB,
	FREE_SPACE_MB,
	[NAME],
	[FILENAME],
	FILE_GROUP,
	[STATE_DESC],
	[DEFAULT]
 from spaceused
union all
select	
	NULL as FILEID,
	sum(FILE_SIZE_MB)as FILE_SIZE_MB, 
	sum(SPACE_USED_MB)as SPACE_USED_MB,
	sum(FREE_SPACE_MB)as FREE_SPACE_MB,
	NULL as [NAME],
	NULL as [FILENAME],
	NULL as [STATE_DESC],
	NULL as FILE_GROUP,
	NULL as [DEFAULT]
from spaceused

Что вернет результат?

И для просветления советую почитать это и это.
16 ноя 11, 15:16    [11606711]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
gds
Anddros,

Какая модель восстановления на БД? Сервер протоколирует все операции:
1. Добавление нового столбца
2. Апдейт
3. Удаление старого.

Но если модель восстановления Full, то записи не сбрасываются в логе, а остаются. Если модель восстановления Simple, то можно просто шринконуть лог.


а ведь я сразу написал про то что модель его не спасет.... а шринкануть лог можно и при фулл ;)
16 ноя 11, 15:32    [11606945]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Shakill
SanyL, ну ALTER COLUMN по частям сделать не получится, автор же изначально про это спрашивает

Anddros, можно создать в той же таблице bigint столбец, и вот уже его заполнять порциями, а потом дропнуть старый стобец и переименовать новый


это и подразумевалось
16 ноя 11, 15:34    [11606977]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
gds
Member

Откуда: Железнодорожный
Сообщений: 1842
Блог
SanyL
а шринкануть лог можно и при фулл ;)

Это то да, но при фулле ему сперва надо будет бекап логов сделать... ;)
16 ноя 11, 16:12    [11607439]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Anddros
Member

Откуда:
Сообщений: 1077
SanyL
1. по кусочкам :) т.е. не всю таблицу апдейтить а ее часть...

Этого-го то я и хотел избежать... Из-за того, что делать придется разными транзакциями, есть немалые шансы потерять добавленные/измененые данные, а накручивание 'надежного' механизма, позволяющего этого избежать, само отъест немалые ресурсы.

Ну и еще из-за того, что мне все это надо провернуть с примерно 1500 полями в 500 таблицах. Из которых штук 50 весят больше гига, а самая крупная - 120 гиг. Непросто написать апдейт по частям для таблиц, заполненных абсолютно разной информацией. :)

И все-таки откуда 30 гиг на табличке 1.4 гига? Даже если пишется в лог и старое и новое состояние, то даже сохраняя всю таблицу, можно уложиться в 3. А здесь 10 раз больше...

ЗЫ. База в Simple. Забыл упомянуть...
16 ноя 11, 17:37    [11608147]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Glory
Member

Откуда:
Сообщений: 104751
Anddros
И все-таки откуда 30 гиг на табличке 1.4 гига?

Вы увеличили размер поля. Что может привести к расщиплению заполненных страниц.
16 ноя 11, 17:41    [11608183]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
Anddros
И все-таки откуда 30 гиг на табличке 1.4 гига? Даже если пишется в лог и старое и новое состояние, то даже сохраняя всю таблицу, можно уложиться в 3. А здесь 10 раз больше...
Просто так сложилось - старое и новое значение в логе + накладывается расщепление страниц...
Плюс не забудьте про инедксы - может, вы учитываете только размер данных.

Лучьше просто сделать вставку в новую таблицу с переименованием старой...

Операция при этом вообще может быть нелогируемой (зависит от версии).

Хотя писать придётся много, там же наверняка у вас всякие индексы и прочее.
16 ноя 11, 17:48    [11608251]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
FilipneZ
Member

Откуда:
Сообщений: 1
понятно)
16 ноя 11, 18:04    [11608398]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Glory
Anddros
И все-таки откуда 30 гиг на табличке 1.4 гига?

Вы увеличили размер поля. Что может привести к расщиплению заполненных страниц.


alexeyvg
Anddros
И все-таки откуда 30 гиг на табличке 1.4 гига? Даже если пишется в лог и старое и новое состояние, то даже сохраняя всю таблицу, можно уложиться в 3. А здесь 10 раз больше...
Просто так сложилось - старое и новое значение в логе + накладывается расщепление страниц...


Посмотрел. К расщеплению привело. После альтера 3 полей (каждый в своей транзакции, разумеется) размер таблички увеличился вдвое. Теперь 2.9 гига. Все равно 30 гиг набрать как-то непросто... :)
До 30 гиг лог вырастает при альтере каждого поля.

alexeyvg
Плюс не забудьте про инедксы - может, вы учитываете только размер данных.

Индексов нет. Заранее все были убиты.

alexeyvg
Лучьше просто сделать вставку в новую таблицу с переименованием старой...
Как раз хотел попробовать

alexeyvg
Операция при этом вообще может быть нелогируемой (зависит от версии).
Очень интересно... А поподробнее можно? Или в ссылочку какую носом мну ткнуть?

alexeyvg
Хотя писать придётся много, там же наверняка у вас всякие индексы и прочее.
Все отключение/подключение уже написано и работает нормально. Проблема в размере лога при альтере. Ну и во времени тоже. Потому как по моим оценкам в нынешнем виде скрипт будет работать с неделю. Хорошо бы уложиться в сутки...
16 ноя 11, 18:21    [11608551]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Glory
Member

Откуда:
Сообщений: 104751
Anddros
Посмотрел. К расщеплению привело. После альтера 3 полей (каждый в своей транзакции, разумеется) размер таблички увеличился вдвое. Теперь 2.9 гига. Все равно 30 гиг набрать как-то непросто... :)

В лог попадает не конечный размер таблицы, а все измнения
У файла лога какое приращение ?
16 ноя 11, 19:10    [11608771]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Glory
У файла лога какое приращение ?
100 MB.

Но и после расширения лога до 30 ГБ при последующих
alter table ... alter column ...
он заполнялся практически с 0 до 100%

Отслеживал через DBCC SQLPERF (LOGSPACE)


Попробовал вставку в новую таблицу. Лога кушает меньше - 20 ГБ вместо 30. А по времени существенно лучше - 9 минут против 3(3 alter'a)*25 мин = 75 мин.
Но лога все равно критически много... На больших таблицах не прокатит...
16 ноя 11, 19:57    [11608941]     Ответить | Цитировать Сообщить модератору
 Re: Сильно растет лог  [new]
invm
Member

Откуда: Москва
Сообщений: 9824
Anddros
alexeyvg
Операция при этом вообще может быть нелогируемой (зависит от версии).
Очень интересно... А поподробнее можно? Или в ссылочку какую носом мну ткнуть?
http://msdn.microsoft.com/ru-ru/library/ms191244.aspx
Следующие операции, выполняемые с полным протоколированием в модели полного восстановления, осуществляются с минимальным протоколированием в простой модели восстановления и модели восстановления с неполным протоколированием:

  • Операции массового импорта (bcp, BULK INSERT и INSERT... SELECT). Дополнительные сведения о том, когда массовый импорт в таблицу подлежит минимальному протоколированию, см. в разделе Предварительные условия для минимального ведения журнала массового импорта данных.


  • Операции SELECT INTO. Дополнительные сведения см. в разделе Предложение INTO (Transact-SQL).

    ...
  • 16 ноя 11, 19:59    [11608950]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    step_ks
    Member

    Откуда:
    Сообщений: 936
    Anddros
    Всем добрый день.

    При изменении типа поля с int на bigint
    alter table [dbo].[***] alter column [idObject] bigint  not  null
    
    ....
    Профайлер показывает, что в этот момент идет операция
    UPDATE [***].[dbo].[***] SET [idObject] = [idObject]

    Т.е. вы запускаете alter, а профайлер показвыает update? какой EventClass ?
    16 ноя 11, 22:49    [11609422]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Haramambaru
    Member

    Откуда:
    Сообщений: 31
    Вместо создания новой таблицы можно добавить новую колонку в старую с новым типом данных, скопировать данные в эту колонку, а потом удалить старую.
    17 ноя 11, 04:21    [11610109]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Haramambaru
    Member

    Откуда:
    Сообщений: 31
    Я имела ввиду добавить новую колонку в существующую таблицу. Плохо что нельзя редактировать сообщения :(
    17 ноя 11, 04:23    [11610110]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31948
    Anddros
    alexeyvg
    Операция при этом вообще может быть нелогируемой (зависит от версии).
    Очень интересно... А поподробнее можно? Или в ссылочку какую носом мну ткнуть?
    Ссылка:
    https://www.sql.ru/blogs/gladchenko/320
    И в документации это тоже написано: http://msdn.microsoft.com/ru-ru/library/ms191244.aspx

    Например: если вы вставляете данные в пустую таблицу без индексов, база в симпл, и используете при вставке хинт TABLOCK, то будет минимальное логирование.
    17 ноя 11, 08:43    [11610259]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Anddros
    Member

    Откуда:
    Сообщений: 1077
    step_ks
    Anddros
    Всем добрый день.

    При изменении типа поля с int на bigint
    alter table [dbo].[***] alter column [idObject] bigint  not  null
    
    ....
    Профайлер показывает, что в этот момент идет операция
    UPDATE [***].[dbo].[***] SET [idObject] = [idObject]

    Т.е. вы запускаете alter, а профайлер показвыает update? какой EventClass ?

    Угу.
    SQL:BatchStarting alter table [dbo].[***] alter column [idObject] bigint not null
    SP:stmtSrarting UPDATE [***].[dbo].[***] SET [idObject] = [idObject]
    17 ноя 11, 11:13    [11611101]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    Anddros
    SP:stmtSrarting

    Это вообще команда из процедуры какой то
    17 ноя 11, 11:15    [11611122]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Anddros
    Member

    Откуда:
    Сообщений: 1077
    alexeyvg
    Anddros
    пропущено...
    Очень интересно... А поподробнее можно? Или в ссылочку какую носом мну ткнуть?
    Ссылка:
    https://www.sql.ru/blogs/gladchenko/320
    И в документации это тоже написано: http://msdn.microsoft.com/ru-ru/library/ms191244.aspx

    Например: если вы вставляете данные в пустую таблицу без индексов, база в симпл, и используете при вставке хинт TABLOCK, то будет минимальное логирование.
    К сожалению, у мну 2005-й. А полноценный INSERT... SELECT с минимальным протоколированием только с 2008-го. В 2005-м только оно для INSERT ... SELECT * FROM OPENROWSET(BULK...). Проверил: лог кушает точно так же, как и без TABLOCK'a. Все остальные условия и так были соблюдены.

    А вот INSERT INTO лог действительно не нагружает. По времени работает немного (процентов на 10-15) быстрее INSERT... SELECT. Только вот в INSERT INTO свои тараканы. Ща буду осматривать все на предмет наличия вычисляемых столбцов, секционирования и пр...
    17 ноя 11, 11:25    [11611189]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104751
    Anddros
    К сожалению, у мну 2005-й. А полноценный INSERT... SELECT с минимальным протоколированием только с 2008-го. В 2005-м только оно для INSERT ... SELECT * FROM OPENROWSET(BULK...). Проверил: лог кушает точно так же, как и без TABLOCK'a. Все остальные условия и так были соблюдены.

    С чего это вдруг ?
    http://msdn.microsoft.com/ru-ru/library/ms191244%28v=SQL.90%29.aspx
    Следующие операции минимально регистрируются в модели восстановления с неполным протоколированием и полностью регистрируются в полной модели восстановления:
    ...

    Операции SELECT INTO.
    Дополнительные сведения см. в разделе SELECT (Transact-SQL).
    17 ноя 11, 11:30    [11611252]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Anddros
    Member

    Откуда:
    Сообщений: 1077
    Glory
    Anddros
    SP:stmtSrarting

    Это вообще команда из процедуры какой то


    object_id: 971635539
    object_type: 20801 - AQ
    object_name: пусто
    SPID, естественно, тот же самый.

    Объекта с таким id нет ни в текущей ни в системных БД. В том числе нет и в момент выполнения команды.

    Нечто невидимое... :)
    17 ноя 11, 11:38    [11611339]     Ответить | Цитировать Сообщить модератору
     Re: Сильно растет лог  [new]
    Anddros
    Member

    Откуда:
    Сообщений: 1077
    Glory
    Операции SELECT INTO.
    Дополнительные сведения см. в разделе SELECT (Transact-SQL).


    Именно его я и имел в виду, когда говорил о INSERT INTO, его успехах и тараканах. :)) Прошу прощения - голова уже пухнет...
    17 ноя 11, 11:41    [11611373]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить