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

Откуда:
Сообщений: 521
День добрый!
Существует оперативная база данных Main_DB, полная модель восстановления, размер порядка 60Г.
Вторая база Reporting_DB, простая модель восстановления(можно поменять).
Переодически, раз в несколько месяцев, удаляются все данные из Reporting_DB и строятся заново на основе данных из Main_DB(полная синхронизация).
Так вот, во время синхронизации размер журнала транзакции может вырасти до порядка 200Г, что не есть хорошо, тупо нет места на диске.
Если не трогать процесс синхронизации, то можно каким-то другим способом ограничить размер лога? Возможность отката не интересует в принципе, если произойдет сбой во время синхронизации, то данные в Reporting_DB в любом случае прийдется удалить.
Правильно ли я понимаю ситуацию:
Процесс синхронизации выполняет длинные транзакции, а чекпоинт происходит редко, это приводит к тому, что файл лога растет?

Если да, более частый чекпоинт спасет ситуацию?

Спасибо!
20 апр 15, 15:29    [17540265]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
Если вы переливаете данные в одной транзакции, то никакие чекпоинты вас не спасут.
20 апр 15, 15:31    [17540277]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Гавриленко Сергей Алексеевич,

я понимаю... Хотелось бы верить, что синхронизация не является одной большой транзакцией.
Можно как-то удостовериться в том, что это на самом деле так?
20 апр 15, 15:33    [17540292]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
Glory
Member

Откуда:
Сообщений: 104751
abrashka
Можно как-то удостовериться в том, что это на самом деле так?

Ну так спрашивайте создателя вашей синхронизации
20 апр 15, 15:34    [17540301]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Glory,

Я имею в виду если нет доступа к синхронизации...
20 апр 15, 15:45    [17540360]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37254
abrashka
Glory,

Я имею в виду если нет доступа к синхронизации...
Запись в лог отключить нельзя. Если кто-то придумал делать транзакции по 200 Гб, то они буду записаны в лог.

Сообщение было отредактировано: 20 апр 15, 15:59
20 апр 15, 15:58    [17540488]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8715
Дропайте базу, заполните базу model так, как Вам надо, создание новой базы будет выполнено по образу model.
20 апр 15, 16:00    [17540496]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Гавриленко Сергей Алексеевич,
Спасибо!
Если одна транзакция- то понятно, нужно фиксить синхронизацию.
А если речь идет о многочисленных транзакциях? Стоит ли менять частоту чекпоинта или есть другие варианты?
20 апр 15, 16:08    [17540554]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Синхронизация таки не является одной большой транзакцией, видимо есть одна или несколько, которые раздувают лог.
Каким образом можно "выловить" транзакции, которые так сильно влияют на размер лога?
21 апр 15, 15:34    [17544524]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
o-o
Guest
abrashka,

покажите, какой у вас выставлен прирост лога,
и в результате сколько VLF имеем (DBCC LOGINFO)
не сильно-то похоже на "несколько транзакций".
у вас же простая модель, чекпойнт по заполнении 70% лога срабатывает.
и чего же не вышло "перезаписать" лог прежде, чем 200 Гигов лога набралось?
наверное, хорошая открытая транзакция держала?
21 апр 15, 15:53    [17544617]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
o-o,

Bаза восстановлена из бэкапа, на данный момент(до синхронизации) база без данных:
Картинка с другого сайта.

DBCC LOGINFO выдает:
Картинка с другого сайта.
21 апр 15, 16:46    [17544988]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
o-o
Guest
abrashka,

вы сейчас про что, эта база 11Мб, мы же вроде про 60 Гб и 200 Гб лога?
в любом случае, если это та самая база,
с приростом лога в 10% получить лог в 200 Гиг -- это надо уметь.
у вас, кстати, чтобы до 200 Гб дорасти, он раз 100 будет расширяться,
VLF-ов получите под 1000, да еще это все в одной транзакции...

а если вы знаете, что вам завтра 60 Гиг зальют, чего сразу базе не выставить сейчас начальный размер эти самые 60 Гиг
и журналу приращение ну хотя бы 100 Мб?

короче, от картинки польза только такая, что явно никто не выставил логу 200Гиг как приращение.
а значит, логу усекаться не давали, и раз простая модель, то что еще-то кроме огромной транзакции
или просто давней висящей незакрытой транзакции, или у вас репликация есть?
21 апр 15, 18:16    [17545393]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
o-o,

Спасибо за помощь.
На картинках данные до синхронизации. По поводу приращения я понял.
Пытаюсь разобраться с синхронизацией, она выполняется в 5 потоков, т.е. на протяжении всего процесса есть 5 открытых транзакции, я мониторил их при помощи следующего скрипта:
 SELECT [s_tst].[session_id]
	,[s_es].[login_name] AS [Login Name]
	,DB_NAME(s_tdt.database_id) AS [Database]
	,[s_tdt].[database_transaction_begin_time] AS [Begin Time]
	,[s_tdt].[database_transaction_log_bytes_used] / POWER(2.0, 30.0) AS LogGb
	,[s_tdt].[database_transaction_log_bytes_reserved] / POWER(2.0, 30.0) AS [LogRsvdGb]
	,[s_est].TEXT AS [Last T-SQL Text]
FROM sys.dm_tran_database_transactions [s_tdt]
JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN sys.[dm_exec_sessions] [s_es] ON [s_es].[session_id] = [s_tst].[session_id]
JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN sys.dm_exec_requests [s_er] ON [s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text([s_ec].[most_recent_sql_handle]) AS [s_est]
WHERE DB_NAME(s_tdt.database_id) = 'CGB4991DWHSTAGING'
ORDER BY 5 DESC
GO


LogGb одного из потоков вырос почти до 40Гб когда выполнялись команды типа:
INSERT INTO CGB4991DWHSTAGING..MyTable(c1,c2,c3...) 
SELECT c1,c2,c3... FROM CGB4991..MyTable

Без особых трансформации, в большенстве своем AS IS.
T.e. размер лога на данном этапе стал почти в два раза больше размера MDF. Это можно как-то предотвратить?
Спасибо!
21 апр 15, 18:50    [17545527]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
abrashka
T.e. размер лога на данном этапе стал почти в два раза больше размера MDF. Это можно как-то предотвратить?
Вам же писали, нужно уменьшить транзакции.
abrashka
на протяжении всего процесса есть 5 открытых транзакции
Вот какая та из этих транзакций (или какие то) очень большая.
abrashka
LogGb одного из потоков вырос почти до 40Гб когда выполнялись команды типа:

Команды выполнялись в одной транзакции?

Почему вы вообще оборачиваете команды в транзакции? Вы же пишете, что "Возможность отката не интересует в принципе, если произойдет сбой во время синхронизации, то данные в Reporting_DB в любом случае прийдется удалить."

Оборачивать в общую транзакцию нужно только те команды, которые совсем мелкие, типа вставок одиночных записей - так будет быстрее.
А средние-крупные оставьте без оборачивающей транзакции.

Ну и далее, вставляйте чекпойнт периодически, если штатного ыполнения не хватит.
21 апр 15, 19:18    [17545611]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
o-o
Guest
alexeyvg,

так вроде он не может "переписать" эту синхронизацию, не его рук дело.
и чекпойнт ему вряд ли чем поможет, если там все в одну транзакцию завернуто
или одна открытая с самого начала чего-то ждет (завершения всего?)
страницы данных чекпойнт на диск сбросит, но лог-то не "усечется",
пока не завершится та супер-транзакция, к-ая все и держит.

если места на диске совсем нет, надо этим воспользоваться.
выставить логу макс.размер, например, 100 Гиг.
супер-синхронизация завалится с ошибкой 9002.
ее надо будет всем показать, начальству, "написателю" синхронизации,
и сказать: по-вашему, это нормально, что у базы в 60 гиг при заполнении лог вырастает до 200 гиг?
если нормально, давайте еще диск под лог-файл.
если ненормально, то разбирайтесь с авторами "синхронизации".
и ничего не придется вылавливать и доказывать, с ошибкой упадет именно виновник торжества,
причем непоправимо, пока не дадут свободное пространство
21 апр 15, 21:51    [17546066]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
o-o,
alexeyvg ,
Огромное спасибо!
Прошу прощения что ввел Вас в заблуждение, на самом деле "создатель" синхронизации планировал, чтоб возможность отката была(хотя я почему-то думал, что нет необходимости).

Менять процесс синхронизации - последнее, что хочет начальство, поэтому попросили разрулить(если возможно) своими силами.
Грубо говоря начальник все никак не может понять почему в пустую базу добавляют 30-40Г "простым" инсерт-селектом, а лог при этом переваливает за 100Г.
Изменение модели восстановления может как-то помочь? Бэкапы лога во время синхронизации?
21 апр 15, 22:57    [17546261]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
invm
Member

Откуда: Москва
Сообщений: 9780
abrashka
Изменение модели восстановления может как-то помочь? Бэкапы лога во время синхронизации?
Складывается впечатление, что вы ответов не читаете и хотите, что бы вам рассказали где найти волшебную кнопку "не увеличивать лог". Так вот, - такой кнопки не существует.

1. Если у вас очищается целевая БД и затем заполняется с нуля, то совершенно непонятно зачем нужна возможность отката "синхронизации". Или очистка и заполнение в одной транзакции? Тогда не удивительно, что журнал так пухнет.
Почему бы, например, не сделать резервную копию перед очисткой? Тогда не нужно будет заморачиваться с откатом "синхронизации".

2. В текущей ситуации можно попробовать заливать данные с минимальным журналированием. Для этого нужно дописать хинт:
INSERT INTO CGB4991DWHSTAGING..MyTable(c1,c2,c3...) with (tablock)
SELECT c1,c2,c3... FROM CGB4991..MyTable
Возможно, придется включить traceflag 610. Подробнее тут.
21 апр 15, 23:27    [17546323]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
o-o
Guest
abrashka,

у вас точно "простая" модель?
какие "бэкапы лога", если simple recovery model?

вот если при заполнении базы в simple идет одновременный бэкап,
то это засада, ваше минимальное логирование превращается в полное.
+ еще если у вас именно insert ..select без tablock, а не select into, то тоже минимально не логируется,
еще наверное не в кучу, а в кластерный вставляете?

вот тут все расписано и есть таблица условий, к-ые нужны для минимального логирования.
наверняка у вас они не соблюдаются
The Data Loading Performance Guide

мне понятно возмущение начальства по поводу роста лога,
но только зачем отгадывать, почему не потребовать код этой "синхронизации"
и не посмотреть, что же там делается?
21 апр 15, 23:33    [17546332]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
invm,

Спасибо за ответ!
Вот и мне козалось, что нет необходимости в откате, но пустая база только в первый раз, а синхронизация будит проходить раз в несколько месяцев, поэтому если вдруг будут проблемы в последующей синхронизации, то имеется желание откатиться.

На счет резервной копии перед очисткой я тоже думал, по мне- так самый оптимальный вариант, но возникает сложность автоматизировать процесс, начальство желает запустить процесс синхронизации одной кнопкой. Но в принципе и это возможно.

Заливать данные с минимальным журналированием- вот это обязательно попробую, спасибо!

Вспомнил, что в целевой базе есть не мало индексов, попробую их отключить на время синхронизации и перестроить в конце.

Спасибо еще раз!
21 апр 15, 23:42    [17546347]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
o-o,

Спасибо, да, именно в кластерный и именно insert ..select без tablock :(

Изучу статью по поводу минимального логирования, никогда с этим не сталкивался, обязательно отчитаюсь.
Спасибо!
21 апр 15, 23:45    [17546350]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
abrashka
на самом деле "создатель" синхронизации планировал, чтоб возможность отката была(хотя я почему-то думал, что нет необходимости).

Менять процесс синхронизации - последнее, что хочет начальство, поэтому попросили разрулить(если возможно) своими силами.
Грубо говоря начальник все никак не может понять почему в пустую базу добавляют 30-40Г "простым" инсерт-селектом, а лог при этом переваливает за 100Г.
Изменение модели восстановления может как-то помочь? Бэкапы лога во время синхронизации?
Если вы хотите иметь возможность откатить, то вам нужно делать всё в общей транзакции.

Про минимальное логирование уже написали - возможно, это поможет.

Лог при вставке может быть больше из за индексов. Ну и от конкретных команд зависит, в лог много чего пишется...
22 апр 15, 00:32    [17546465]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
abrashka
Member

Откуда:
Сообщений: 521
Спасибо всем!
Минимальное логирование, отключение индексов и т.п. не сильно помогли, объяснил начальству, что если есть необходимость в откате, то следует ожидать увеличение лога, если передумают на счет отката, то будем посмотреть.
30 апр 15, 13:05    [17586666]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз предотвратить увеличение файла журнала.  [new]
komrad
Member

Откуда:
Сообщений: 5700
abrashka
Спасибо всем!
Минимальное логирование, отключение индексов и т.п. не сильно помогли, объяснил начальству, что если есть необходимость в откате, то следует ожидать увеличение лога, если передумают на счет отката, то будем посмотреть.


если места совсем нет, но "очень надо", то можно добавить второй лог файл на сетке
1807

при этом надо понимать все плюсы и минусы такого решения (сетка)
30 апр 15, 13:19    [17586774]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить