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

Откуда:
Сообщений: 148
Добрый день.


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

delete from table where a=1


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

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

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

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

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

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

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


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

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

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



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

Откуда:
Сообщений: 5244
пятый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

Откуда:
Сообщений: 148
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
Сообщений: 36965
Может, стоит секционировать по полю "a" как-нибудь и транкейтить секции?
6 авг 18, 18:33    [21631980]     Ответить | Цитировать Сообщить модератору
 Re: Очистка лога транзакций во время выполнения бооольшой транзакции...  [new]
пятый2
Member

Откуда:
Сообщений: 148
Гавриленко Сергей Алексеевич
Может, стоит секционировать по полю "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

Откуда:
Сообщений: 8066
Владимир Затуливетер
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 );


Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "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

Откуда:
Сообщений: 5244
пятый2

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


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

кстати, бекап в NUL работает безотказно и места не занимает, хотя с компрессией не совместим )
6 авг 18, 22:14    [21632222]     Ответить | Цитировать Сообщить модератору
 Re: Очистка лога транзакций во время выполнения бооольшой транзакции...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
Владимир Затуливетер
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

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

Откуда:
Сообщений: 148
Владимир Затуливетер
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 );



Индекс есть.

На счет задержки, спасибо.
7 авг 18, 12:23    [21632722]     Ответить | Цитировать Сообщить модератору
 Re: Очистка лога транзакций во время выполнения бооольшой транзакции...  [new]
пятый2
Member

Откуда:
Сообщений: 148
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

Откуда:
Сообщений: 148
В общем, удаляю в цикле с задержкой, итерациями примерно по 2-3 минуты, мониторя размер лога и делая бэкап.
7 авг 18, 12:34    [21632739]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить