Очистка лога транзакций во время выполнения бооольшой транзакции... / Microsoft SQL Server / Sql.ru

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

Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
пятый2 Member Откуда: Сообщений: 150	Добрый день. Есть запрос, который удаляет огромное количество данных из таблицы (примерно 150 Гб). delete from table where a=1 Но при этом в лог транзакций генерируется куча данных. И лог иногда перестает влазить на диск. удаление длится примерно 3 часа. Бэкап лога прямо во время выполнения этого удаления должен ли очищать лог? Или он не сможет очистить то что генерирует открытая транзакция?
6 авг 18, 18:15 [21631956] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37198	А что должно произойти в случае, если вы забэкапите лог, сервер его почистит, а потом транзакция захочет откатиться?
6 авг 18, 18:20 [21631964] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

пятый2
Member

Откуда:
Сообщений: 150

Гавриленко Сергей Алексеевич
А что должно произойти в случае, если вы забэкапите лог, сервер его почистит, а потом транзакция захочет откатиться?

Я вот тоже думаю что он очищать текущие транзакции не могёт.

Ну и бэкап лога это подтверждает - не очищает.

Хотел убедиться.

А во время перестройки индекса получается такая же ситуация - бэкапь не бэкапь лог: он не очистится?

6 авг 18, 18:22 [21631967] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

komrad
Member

Откуда:
Сообщений: 5593

пятый2
Или он не сможет очистить то что генерирует открытая транзакция?

не сможет

используйте батчи

например

delete top (30000) from [TBL] where a=1

while @@rowcount <> 0 

delete top (30000) from [TBL] where a=1

6 авг 18, 18:24 [21631970] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

пятый2
Member

Откуда:
Сообщений: 150

komrad

пятый2
Или он не сможет очистить то что генерирует открытая транзакция?

не сможет

используйте батчи

например

delete top (30000) from [TBL] where a=1

while @@rowcount <> 0 

delete top (30000) from [TBL] where a=1

Спасибо, попробую так.

Наверное еще на каждом шаге бэкап лога как-нить всуну.

6 авг 18, 18:28 [21631975] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37198	Может, стоит секционировать по полю "a" как-нибудь и транкейтить секции?
6 авг 18, 18:33 [21631980] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

пятый2
Member

Откуда:
Сообщений: 150

Гавриленко Сергей Алексеевич
Может, стоит секционировать по полю "a" как-нибудь и транкейтить секции?

Тоже хороший вариант, благодарю.

6 авг 18, 18:34 [21631981] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

Владимир Затуливетер
Member

Откуда:
Сообщений: 427

10 раз подумайте прежде чем секционировать... некоторые запросы возможно придется менять.

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

while 1=1
begin
    delete top (1000) from [TBL] where a=1
    if @@rowcount = 0 break
    waitfor delay '00:00:01' -- я еще обычно добавляю задержку чтобы не грузить сильно сервер.
end

Для такого запроса обязательно нужен индекс по полю "a", иначе такое удаление только хуже сделает.

create index IDX_TBL_A on dbo.TBL ( a );

6 авг 18, 19:44 [21632061] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

msLex
Member

Откуда:
Сообщений: 8856

Владимир Затуливетер

while 1=1
begin
    delete top (1000) from [TBL] where a=1
    if @@rowcount = 0 break
    waitfor delay '00:00:01' -- я еще обычно добавляю задержку чтобы не грузить сильно сервер.
end

Для такого запроса обязательно нужен индекс по полю "a", иначе такое удаление только хуже сделает.

create index IDX_TBL_A on dbo.TBL ( a );

Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "best practices"

6 авг 18, 19:48 [21632066] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
Remind Member Откуда: UK Сообщений: 523	А огромное кол-во данных это какой процент от всех данных в таблице? А то может проще перенести все что надо в новую табличку, а эту дропнуть?
6 авг 18, 19:49 [21632067] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

TaPaK
Member

Откуда: Kiev
Сообщений: 6801

msLex,

автор
Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "best practices"

это как-то специально надо заставить это сделать?

6 авг 18, 20:07 [21632089] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151

пятый2

Добрый день.

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

delete from table where a=1

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

И лог иногда перестает влазить на диск.

удаление длится примерно 3 часа.

Бэкап лога прямо во время выполнения этого удаления должен ли очищать лог?

Или он не сможет очистить то что генерирует открытая транзакция?

А можно так.
Сделать выгрузку из таблицы table в CSV where a <> 1.
Затем загрузку пакетом SSIS с Fast load в чистую вновь созданную таблицу table_new.
Затем переименование table в table_old и table_new в table внутри одной транзакции с обработкой исключения.
И после этого делать бэкап журнала транзакций.

Если что-то важное удалится, а реально столбец для этой строки был не a=1, а a=2, то найти нужную строку в table_old и загрузить ее одну такую в table будет проще, чем с воплями "где таки свободное дисковое пространство" разворачивать полный бэкап базы рядом и дальше после этого искать в базе-копии эту единственную строку.

То есть я предлагаю Вам посмотреть чуть дальше привычного горизонта планирования. А место внутри БД можно всегда получить через truncate table_old перед полным бэкапом БД, MSSQL поймет, что там были реальные строки, а теперь страницы с пустым местом для новых таблиц - и не будет эту пустоту записывать в бэкап.

6 авг 18, 20:24 [21632118] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
Andy_OLAP Member Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион Сообщений: 3151	Andy_OLAP, Более того, имея копию одной большой таблицы, но одной, в файле CSV - можно ее в любой момент загрузить внутрь этой базы данных в таблицу table_old_copy_before_delete и поискать там. А если файлы CSV сжимать в архив и складывать на дешевую дисковую полку - то при необходимости можно будет в любой момент сделать bulk insert через SSIS пакет в таблицу table_old_copy_before_delete_in_date_20180803 и другой CSV в таблицу table_old_copy_before_delete_in_date_20180806 - и сравнить между собой. Иногда такое сравнение дает исключительно положительный и неожиданный эффект.
6 авг 18, 20:28 [21632122] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

Владимир Затуливетер
Member

Откуда:
Сообщений: 427

msLex
Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "best practices"

Да, да, как буд-то секционирование дается нам "бесплатно".
Я сказал подумать. Если автор только складывает и удаляет, то однозначно секционирование.
А вот если у него куча разных запросов, которым поплохеет после вашего секционирования, да еще и новые значения добавляются в столбец "а" переодически, то кто заплатит за рефекторинг и поддержку софта автора?

6 авг 18, 20:50 [21632141] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

komrad
Member

Откуда:
Сообщений: 5593

пятый2
Наверное еще на каждом шаге бэкап лога как-нить всуну.

ну безусловно, либо через N-ое кол-во циклов

кстати, бекап в NUL работает безотказно и места не занимает, хотя с компрессией не совместим )

6 авг 18, 22:14 [21632222] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37198

Владимир Затуливетер

msLex
Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "best practices"

Если выпиливание 150 Гб данных командой delete (да еще, по вашей версии, и по некластерному индексу) -- это не повод задуматься о секционировании, то я уж даже не знаю, что может быть таким поводом. Но я уже испорчен наличием AlwaysOn -- сия волшебная технология быстро отучает нагружать лог всякой ненужной фигней.

Сообщение было отредактировано: 7 авг 18, 00:17

7 авг 18, 00:13 [21632285] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
Владимир Затуливетер Member Откуда: Сообщений: 427	В каждом решении есть плюсы и минусы, давайте предоставим выбор автору темы. Мы не знаем что у него там, у нас только предположения. А по поводу некластерного индекса, это не важно что мы удаляем по нему. Это бэкграунд таск, задержкой мы можем регулировать нагрузку. Он может удалять данные и 10 часов и неделю. Главное чтобы это автора темы устраивало.
7 авг 18, 06:54 [21632354] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
.Евгений Member Откуда: Сообщений: 667	В схожем случае выпиливал записи курсором (delete...current of). По одной, зато сразу и без накладных расходов.
7 авг 18, 09:57 [21632500] Ответить \| Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

пятый2
Member

Откуда:
Сообщений: 150

Владимир Затуливетер

while 1=1
begin
    delete top (1000) from [TBL] where a=1
    if @@rowcount = 0 break
    waitfor delay '00:00:01' -- я еще обычно добавляю задержку чтобы не грузить сильно сервер.
end

Для такого запроса обязательно нужен индекс по полю "a", иначе такое удаление только хуже сделает.

create index IDX_TBL_A on dbo.TBL ( a );

Индекс есть.

На счет задержки, спасибо.

7 авг 18, 12:23 [21632722] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]

пятый2
Member

Откуда:
Сообщений: 150

Andy_OLAP

Andy_OLAP,

Более того, имея копию одной большой таблицы, но одной, в файле CSV - можно ее в любой момент загрузить внутрь этой базы данных в таблицу table_old_copy_before_delete и поискать там.

А если файлы CSV сжимать в архив и складывать на дешевую дисковую полку - то при необходимости можно будет в любой момент сделать bulk insert через SSIS пакет в таблицу table_old_copy_before_delete_in_date_20180803 и другой CSV в таблицу table_old_copy_before_delete_in_date_20180806 - и сравнить между собой.

Иногда такое сравнение дает исключительно положительный и неожиданный эффект.

Интересный вариант, мини архив получается.

7 авг 18, 12:26 [21632729] Ответить | Цитировать Сообщить модератору

Re: Очистка лога транзакций во время выполнения бооольшой транзакции... [new]
пятый2 Member Откуда: Сообщений: 150	В общем, удаляю в цикле с задержкой, итерациями примерно по 2-3 минуты, мониторя размер лога и делая бэкап.
7 авг 18, 12:34 [21632739] Ответить \| Цитировать Сообщить модератору

Все форумы / Microsoft SQL Server

Ссылка на сообщение

Ссылка (включая название темы)

Ссылка (URL)