Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
пятый2 Member Откуда: Сообщений: 150 |
Добрый день. Есть запрос, который удаляет огромное количество данных из таблицы (примерно 150 Гб). delete from table where a=1 Но при этом в лог транзакций генерируется куча данных. И лог иногда перестает влазить на диск. удаление длится примерно 3 часа. Бэкап лога прямо во время выполнения этого удаления должен ли очищать лог? Или он не сможет очистить то что генерирует открытая транзакция? |
6 авг 18, 18:15 [21631956] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37198 |
А что должно произойти в случае, если вы забэкапите лог, сервер его почистит, а потом транзакция захочет откатиться? |
6 авг 18, 18:20 [21631964] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
Я вот тоже думаю что он очищать текущие транзакции не могёт. Ну и бэкап лога это подтверждает - не очищает. Хотел убедиться. А во время перестройки индекса получается такая же ситуация - бэкапь не бэкапь лог: он не очистится? |
||
6 авг 18, 18:22 [21631967] Ответить | Цитировать Сообщить модератору |
komrad Member Откуда: Сообщений: 5593 |
не сможет используйте батчи например delete top (30000) from [TBL] where a=1 while @@rowcount <> 0 delete top (30000) from [TBL] where a=1 |
||
6 авг 18, 18:24 [21631970] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
Спасибо, попробую так. Наверное еще на каждом шаге бэкап лога как-нить всуну. |
||||
6 авг 18, 18:28 [21631975] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37198 |
Может, стоит секционировать по полю "a" как-нибудь и транкейтить секции? |
6 авг 18, 18:33 [21631980] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
Тоже хороший вариант, благодарю. |
||
6 авг 18, 18:34 [21631981] Ответить | Цитировать Сообщить модератору |
Владимир Затуливетер 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] Ответить | Цитировать Сообщить модератору |
msLex Member Откуда: Сообщений: 8856 |
Да, да, секционирование это зло, а вот поддерживать доп индекс на таблице и добавить "лукап" в кластерный индекс при удалении это "best practices" |
||
6 авг 18, 19:48 [21632066] Ответить | Цитировать Сообщить модератору |
Remind Member Откуда: UK Сообщений: 523 |
А огромное кол-во данных это какой процент от всех данных в таблице? А то может проще перенести все что надо в новую табличку, а эту дропнуть? |
6 авг 18, 19:49 [21632067] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
msLex,
это как-то специально надо заставить это сделать? |
||
6 авг 18, 20:07 [21632089] Ответить | Цитировать Сообщить модератору |
Andy_OLAP Member Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион Сообщений: 3151 |
А можно так. Сделать выгрузку из таблицы 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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
Владимир Затуливетер Member Откуда: Сообщений: 427 |
Да, да, как буд-то секционирование дается нам "бесплатно". Я сказал подумать. Если автор только складывает и удаляет, то однозначно секционирование. А вот если у него куча разных запросов, которым поплохеет после вашего секционирования, да еще и новые значения добавляются в столбец "а" переодически, то кто заплатит за рефекторинг и поддержку софта автора? |
||
6 авг 18, 20:50 [21632141] Ответить | Цитировать Сообщить модератору |
komrad Member Откуда: Сообщений: 5593 |
ну безусловно, либо через N-ое кол-во циклов кстати, бекап в NUL работает безотказно и места не занимает, хотя с компрессией не совместим ) |
||
6 авг 18, 22:14 [21632222] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37198 |
Сообщение было отредактировано: 7 авг 18, 00:17 |
||||
7 авг 18, 00:13 [21632285] Ответить | Цитировать Сообщить модератору |
Владимир Затуливетер Member Откуда: Сообщений: 427 |
В каждом решении есть плюсы и минусы, давайте предоставим выбор автору темы. Мы не знаем что у него там, у нас только предположения. А по поводу некластерного индекса, это не важно что мы удаляем по нему. Это бэкграунд таск, задержкой мы можем регулировать нагрузку. Он может удалять данные и 10 часов и неделю. Главное чтобы это автора темы устраивало. |
7 авг 18, 06:54 [21632354] Ответить | Цитировать Сообщить модератору |
.Евгений Member Откуда: Сообщений: 667 |
В схожем случае выпиливал записи курсором (delete...current of). По одной, зато сразу и без накладных расходов. |
7 авг 18, 09:57 [21632500] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
Индекс есть. На счет задержки, спасибо. |
||
7 авг 18, 12:23 [21632722] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
Интересный вариант, мини архив получается. |
||
7 авг 18, 12:26 [21632729] Ответить | Цитировать Сообщить модератору |
пятый2 Member Откуда: Сообщений: 150 |
В общем, удаляю в цикле с задержкой, итерациями примерно по 2-3 минуты, мониторя размер лога и делая бэкап. |
7 авг 18, 12:34 [21632739] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |