Очередная очистка таблиц средствами MS SQL

Публикация № 613909

Администрирование - Администрирование данных 1С - Чистка базы

Очистка таблиц SQL delete truncate

31
Очередной пост про очистку таблицы средствами MS SQL. И почему стоит очищать большие таблицы порциями.

Дано:

  • СУБД MS SQL Server
  • Регистр сведений ~80 млн записей

Задача:

  • удалить ~70 млн записей регистра сведений
  • удалить по условию

Решение: 

Если бы мне необходимо было удалить все записи, я бы воспользовался следующей командой

USE <ИмяБазы>
truncate table <ИмяТаблицы>

*имя таблицы можно посмотреть с помощью обрабтки //infostart.ru/public/16282/

Но мне надо было удалить записи по условию, казалось бы, что может быть проще, пишем:

USE <ИмяБазы>
delete from <ИмяТаблицы> where <ИмяПоля> = <ЗначениеПоля>

Но, даже в простой (Simple) модели восстановления, все операции изменения данных  пишутся в одной транзакции. Соответственно лог транзакций растет очень сильно, в моем случае мне просто не хватило места на диске.

Оптимальное решение -  удалять записи порциями, напимер по 5000.

USE <ИмяБазы>
WHILE 1=1
BEGIN
	DELETE top (5000) FROM <ИмяТаблицы> 
	where <ИмяПоля>= <ЗначениеПоля>;
	
	if @@ROWCOUNT<1 break;
END

 ROWCOUNT - Возвращает число строк, затронутых при выполнении последней инструкции. Если число строк превышает 2 миллиарда, используйте ROWCOUNT_BIG

Надеюсь, мой пост будет кому-то полезен.

Гуру SQL, снимайте кеды, прежде чем пинаться! :)

31

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. METAL 108 21.04.17 19:12 Сейчас в теме
Спасибо!
Опечатка <ИмяПоял>
2. JesteR 141 21.04.17 22:55 Сейчас в теме
(1) Спасибо, поправил.

По рзеузльаттам илссоевадний одонго анлигсйокго унвиертисета, не иеемт занчнеия, в каокм проякде рсапжоолены бкувы в солве. Галовне, чотбы преавя и пслонедяя бквуы блыи на мсете. осатьлыне бкувы мгоут селдовтаь в плоонм бсепордяке, все-рвано ткест чтаитсея без побрелм. Пичрионй эгото ялвятеся то, что мы не чиаетм кдаужю бкуву по отдльенотси, а все солво цлиеком.
Antonov.AV; artichoke; user598350_grimax; DeD MustDie; METAL; savinsva; julia96_07; demkonst; RegrZ; AlexGroovy; +10 Ответить
3. AlexGroovy 22.04.17 22:23 Сейчас в теме
4. 1cWin 25.04.17 08:33 Сейчас в теме
Не стал бы я надеяться на @@ROWCOUNT
5. JesteR 141 25.04.17 09:28 Сейчас в теме
(4) Алексей, ты поделись с нами почему не стал бы? Может и я не стану после твоих аргументов :)
6. JesteR 141 25.04.17 09:34 Сейчас в теме
(4) У меня изначально, было такое условие цикла
WHILE (select COUNT(*) from <ИмяТаблицы> where <ИмяПоля>= <ЗначениеПоля>)>1


Но я решил, что это не оптимально...
Потом нашел описание системной переменной ROWCOUNT и переписал запрос на тот, что в статье. Все отработало без нареканий.
7. 1cWin 25.04.17 14:36 Сейчас в теме
(6) здесь может быть потенциальная ошибка. statement изменчив. ROWCOUNT лучше сохранить, а потом использовать в сравнении.
8. JesteR 141 25.04.17 15:22 Сейчас в теме
(7) Т.к. после операции удаления нет никаких операторов, посчитал излишним в переменную сохранять.
9. gra4enok 9 01.05.19 21:17 Сейчас в теме
Если нужно удалить много больше, чем половина записей таблицы, можно и через truncate сделать. Insert и Delete примерно одинаково отрабатывают по времени. Поэтому можно сразу очистить всю таблицу и вставить оставшиеся строки. К примеру:

declare @test int

begin transaction Tran_<ИмяТаблицы>
select * into ##tmp_<ИмяТаблицы> from <ИмяТаблицы> where <ИмяПоля>= <ЗначениеПоля>

set @test = @@ROWCOUNT

truncate table  <ИмяТаблицы>

if @test>0
insert into <ИмяТаблицы> select * from ##tmp_<ИмяТаблицы>;

commit transaction Tran_<ИмяТаблицы>
while exists(select name from tempdb..sysobjects where name='##tmp_<ИмяТаблицы>')
begin
	drop table ##tmp_<ИмяТаблицы>
end;
Показать
Оставьте свое сообщение

См. также

Удаление записей регистров и пересчет итогов в условиях нехватки места на диске 0

Статья Системный администратор Программист Нет файла v8 1cv8.cf Россия СУБД Бесплатно (free) Чистка базы

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

17.04.2019    1862    semensemenbi4    8       

Как быстро "удалить все документы и движения" в базе (Альтернатива) 57

Статья Системный администратор Нет файла v8 Бесплатно (free) Чистка базы

Очень часто задают вопрос - как удалить все документы из базы, оставить только номенклатуру и настройки. Есть много различных вариантов (групповая обработка документов и справочников - да, работает, но медленно, у клиента в базе 460 000 документов проведенных - неделю будет удалять). Перенос данных - через ВыгрузкаЗагрузкаДанныхXML82.epf - можно, но тоже долго и требует определенных навыков.

06.03.2018    15911    Tatitutu    21       

Если при рассылке закончилось место 5

Статья Системный администратор Программист Нет файла v8 Россия Windows Бесплатно (free) Чистка базы

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

23.03.2017    7108    hydro2588_2015    19       

Удаление лишних данных 17

Статья Системный администратор Программист Нет файла v8 Бесплатно (free) Чистка базы

Настало то время, когда на SSD винчестерах размером 120 Гб начало заканчиваться место.  В связи с этим, было принято решение проанализиривать возможность уменьшения занимаемого места БД. Без веского обоснования у нас никакие работы не производятся, просто так оптимизацией никто заниматься не даст. 1. Выявление таблиц БД, занимающих много места. 2. Очистка записей регистров накопления. 3. Тестирование.

25.11.2016    12516    HAMMER_59    10       

Версионирование объектов в 1С УПП 1.3. Фикс дублей 10

Статья Системный администратор Программист Нет файла v8 УПП1 Россия Windows Бесплатно (free) Чистка базы Производительность и оптимизация (HighLoad)

В 1С УПП 1.3 есть такой замечательный механизм "версионирование объектов", который позволяет узнать: кто, когда и что изменил в документах и справочниках. Но есть недочеты в этой системе, программа не проверяет, является ли текущая записываемая версия объекта дублем (аналогична предыдущей версии), что в итоге захламляет базу и существенно увеличивает её объем.

1 стартмани

16.11.2015    11599    tehas    4       

БГУ 1.0. Чистим отчётность 11

Статья Системный администратор Нет файла v8 БГУ Государственные, бюджетные структуры Россия БУ Windows Бесплатно (free) Чистка базы

Временами встречается проблема с заполнением бюджетной (бухгалтерской) отчётности в БГУ 1.0. По разным причинам отчётность может заполняться неправильно или не заполняться совсем. В данной статье приведена методика быстрой «чистки» отчётности.

29.09.2015    14103    Sergey123321    6       

Задвоение элементов плана счетов в базе на SQL. 3

Статья Программист Нет файла v8 Windows Бесплатно (free) Чистка базы

Иногда приходится разбираться с "грехами отцов", когда приходишь к клиенту, а до тебя наделали делов. Вот, собственно, проблема: Настраивали когда-то план обмена, все работало штатно. Потом обмен пошел не как надо и в справочниках появились задвоенные предопределенные элементы. В общем, на Инфостарте нашел кучу обработок, которые помогут. Но тут оказалось, что если работать с элементом "ПланСчетов", эти обработки бесполезны.

27.08.2015    7053    vasyalisk88    17       

Организация простейшего внешнего хранилища прикрепленных к документу (элементу справочника, ...) файлов на web-сервере, или не засоряем базу 1С 19

Статья Системный администратор Программист Нет файла Бесплатно (free) Чистка базы

Если Вы ощущаете когнитивный диссонанс, когда сталкиваетесь с многогигабайтной базой данных, забитой разношёрстными файлами хранилища дополнительной информации, и Вас, как и меня, не устраивает сама идея хранения бинарных файлов в базе данных, то эта статья, возможно, окажется полезной.

03.06.2015    12889    gaabora    6       

Фоновое Задание "Удаление помеченных объектов" (не монопольно) 40

Статья Программист Нет файла v8 1cv8.cf Россия Windows Бесплатно (free) Чистка базы

Подробное описание немонопольного удаления помеченных объектов, реализованное как Фоновое Задание.

02.08.2013    19654    shvo65    10       

Как свертывать большую базу 1C 8 в максимально короткие сроки (1С 8.1, 8.2) 62

Статья Системный администратор Программист Нет файла v8 УТ10 Windows Бесплатно (free) Свертка базы Чистка базы Производительность и оптимизация (HighLoad)

Как свертывать большую базу 1C 8 в максимально короткие сроки (1С 8.1, 8.2). Любая база данных со временем разрастается до огромных размеров, что делает его более медлительным и работать в нем порой бывает очень тяжело. По этому периодически базу свертывают, т.е. до определенной даты все документы и движения удаляются и вводятся начальные остатки. Соответственно всегда есть архивная база за прошлые периоды. Пример свертки базы 1С на SQL приведен здесь.

14.06.2013    55917    setrak    21       

Скрипт для удаления всех таблиц из базы MS SQL 4

Статья Системный администратор Программист Нет файла Россия Бесплатно (free) Чистка базы Практика программирования

Иногда хочется оставить настройку базы данных SQL, но удалить оттуда все данные, чтобы они не занимали место. Самый простой способ - запустить скрипт, который удалит

10.11.2011    21543    fixin    5       

Простейший способ очистить базу данных от мусора и исправить ошибки 37

Статья Системный администратор Программист Нет файла openconf v77::ОУ v77::БУ v77::Расчет 1cv7.md Windows Бесплатно (free) Чистка базы Тестирование и исправление

Столкнулся с ситуацией: все проводки (бухгалтерия) удвоены! во всех отчётах, при этом проводки по документам верны. КРАЙ! заготовок на такой случай нет... Скопировал на флешку, а потом сделал вот такие шаманские действия:

15.12.2010    14106    vladimir_makarov    40       

Замена элементов в реквизитах и проводках документов, реквизитах справочников и движениях регистров. (SQL) 15

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v7.7 1cv7.md Россия Бесплатно (free) Чистка базы Поиск данных

Иногда возникает необходимость заменить один элемент справочника на другой по всей конфигурации. При этом нежелательно перепроводить документы. Эта разработка позволит проделать такую операцию на SQL-базах

26.10.2010    14235    292    MadDAD    17       

Удаление помеченных объектов группами 10

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v8 1cv8.cf Россия Бесплатно (free) Чистка базы

Предлагаемая обработка поможет в тех случаях когда в базе накопилось много помеченных на удаление объектов и штатными средствами удалить их не получается.

28.09.2010    12816    283    mrWatson    13       

Очистка БД от документов всех организаций (фирм) кроме указанной 50

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Россия Бесплатно (free) Чистка базы

Надо было скинуть слепок БД в вышестоящую организацию, но руководство справедливо решило, что все что по этой организации пусть видят, а вот остальные движения там на фиг не нужны, ну и накорябал сие, может ещё кому нить пригодится... Пользуйтесь

23.05.2010    29708    612    Farpost    15       

Универсальная обработка aaDeleteUpdate для 1C v8.2 4

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Чистка базы Инструментарий разработчика

В 1С к сожалению в языке запросов отсутствуют Delete и Update. Данная обработка немного решает эту проблему для объектных типов (справочник, документ, ..)

05.05.2010    3603    78    vadimsntn    1       

Универсальная обработка aaDeleteUpdate 6

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Чистка базы Инструментарий разработчика

К сожалению в языке запросов 1С отсутствуют Delete и Update. Данная обработка немного решает эту проблему для объектных типов (справочник, документ, ..) Выкладываю заново - исправил ошибку

12.04.2010    2883    58    vadimsntn    7       

Удаление элементов справочников с пустыми наименованиями 4

Инструменты и обработки no Внешняя обработка (ert,epf) v7.7 1С7:Бух Windows Бесплатно (free) Чистка базы Обработка справочников

Обработка позволяет удалить (непосредственное удаление или пометка удаления) элементы справочников с пустыми наименованиями.

04.02.2010    10455    96    whtblck    4       

Поиск ссылок на объект для последующего удаления объектов. 271

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v8 1cv8.cf Россия Бесплатно (free) Чистка базы Поиск данных

Иногда бывает нужно найти все ссылки на конкретный документ или любой другой объект системы, чтобы все это удалить...

12.01.2010    48182    2623    YAN    44       

Универсальная обрезка и очистка периодических реквизитов 115

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v77::ОУ v77::БУ 1cv7.md 1С7:БМО 1С7:Бух 1С7:ЗиК 1С7:Комплекс 1С7:ТиС 1С7:ПУБ Windows Бесплатно (free) Свертка базы Чистка базы

Обработка по обрезке и очистке периодики в справочниках. Реализованы множественные фильтры для элементов и владельцев (если таковые имеются), выбор периодических реквизитов. Находит свое применение при обрезке баз (запоминаем значения на дату обрезки и смело удаляем все документы, которые устанавливают периодические реквизиты справочников ). Кроме того обработка позволяет очищать значения периодических реквизитов за период (например до даты обрезки) - тем самым уменьшить размер 1sconst.dbf - значения периодических реквизитов. Простая, но удобная и полезная.

20.12.2009    21090    1295    nika.1007    28       

Прямое удаление документов 15

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v77::ОУ v77::БУ 1cv7.md Россия Бесплатно (free) Чистка базы

Здравствуйте, уважаемые коллеги. Понимаю, что очередной велосипед, и опять без контроля ссылочной целостности, но надо же с чего-то начинать :) Плюсы мне не нужны, но очень нужна критика опытных товарищей по цеху, ибо нештатные средства типа SQL-ных запросов 1С++ только начинаю осваивать. Заранее благодарна. Тем более буду счастлива, если это кому-нибудь пригодится :)

26.10.2009    6009    215    My Precious    20       

Формирование счетов-фактур на аванс, записей книги покупок. (Проверка на ошибки при ручном оформлении и т.д.) 55

Инструменты и обработки no Внешняя обработка (ert,epf) v77::БУ 1С7:Бух БУ НУ Windows НДС Бесплатно (free) Чистка базы Обработка документов Тестирование и исправление

Удобный и проверенный временем инструмент. Обрабатывает различные нестандартные ситуации (возвраты авансов, пропущенные документом "Формирование записей книги покупок" зачеты и т.д.)

21.07.2009    17239    363    LostMicos    29       

Удаление элементов справочников в 7.7 18

Инструменты и обработки Системный администратор Программист Архив с данными v77::ОУ v77::БУ v77::Расчет 1cv7.md Россия Бесплатно (free) Чистка базы Обработка справочников

Обработка может работать с любыми программами на платформе 1С:Предприятие 7.7. Судя по форуму, тема все еще актуальна.

05.06.2009    16509    777    serguson    15       

Чистка дублей в справочнике 7.7 26

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v77::ОУ v77::БУ 1С7:Бух 1С7:ТиС Россия Бесплатно (free) Чистка базы

Еще раз поиск дублей в справочнике. Возможность замены дубля с пометкой на удаление. Анализируется только НАИМЕНОВАНИЕ. Нечеткий поиск. 1С++.

15.04.2009    15972    1069    mihast    25       

Как найти новые документы автора, в т.ч. созданные программно 9

Статья Программист Нет файла v7.7 1cv7.md Россия Бесплатно (free) Чистка базы Практика программирования Работа с интерфейсом

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

11.03.2009    13419    Shaman100M    16       

Полное удаление организации из базы 48

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Чистка базы

Полное удаление Организации и всех зависимых объектов для версии 8.0

27.02.2009    36422    3653    ganelin    26       

Удаление дублирующих элементов номенклатуры по артикулам в 1С: Торговля и склад 7.7 10

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v77::ОУ 1С7:ТиС Россия Бесплатно (free) Чистка базы

Данная обработка поможет удалить дублирующие элементы номенклатуры в справочнике и документах.

11.02.2009    9074    187    mdvinfostart    14       

Обрезание (свертка) ТиС 1

Статья Программист Нет файла v7.7 1С7:ТиС Россия УУ Розничная торговля Бесплатно (free) Чистка базы Практика программирования

В данной статье я опишу универсальную технологию обрезания (сверки) БД.

24.12.2008    9077    kasperok    20       

ЗиК: Исправление задвоенных шаблонов проводок 42

Инструменты и обработки Системный администратор Внешняя обработка (ert,epf) v77::Расчет 1С7:ЗиК БУ Windows Зарплата Управление персоналом (HRM) Бесплатно (free) Чистка базы Поиск данных Тестирование и исправление

Обработка предназначена для поиска одинаковых шаблонов проводок конфигурации Зарплата+Кадры 2.3, поиска и замены ссылок на один из них.

21.10.2008    15362    450    poppy    6       

Очистка периодических реквизитов справочников 45

Инструменты и обработки no Внешняя обработка (ert,epf) v77::ОУ v77::БУ v77::Расчет 1cv7.md Windows Бесплатно (free) Чистка базы Тестирование и исправление Обработка справочников

Данная обработка предназначена для вывода в таблицу более поздних повторяющихся или "пустых" значений периодических реквизитов справочников для просмотра, анализа и последующего удаления «лишних» значений.

15.07.2008    22704    832    UrbSI    9       

Объединение "дублей" сотрудников 68

Инструменты и обработки Бухгалтер Внешняя обработка (ert,epf) v77::Расчет 1С7:ЗиК Россия Управление персоналом (HRM) Бесплатно (free) Чистка базы Обработка справочников

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

20.06.2008    15040    550    AlexMir    6       

Замещение помеченных на удаление записей справочников 23

Инструменты и обработки no Внешняя обработка (ert,epf) v77::ОУ v77::БУ v77::Расчет 1cv7.md Windows Бесплатно (free) Чистка базы Обработка документов Тестирование и исправление

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

14.05.2008    7144    137    illarion    2       

Поиск одинаковых наименований в справочниках 14

Инструменты и обработки no Внешняя обработка (ert,epf) v77::БУ 1С7:Бух Windows Бесплатно (free) Чистка базы Поиск данных Тестирование и исправление

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

19.02.2008    8403    163    alegator    4       

Групповая обработка документов (признаки и непосредственное удаление) 28

Инструменты и обработки no Внешняя обработка (ert,epf) v8 1cv8.cf Бесплатно (free) Сервисные утилиты Чистка базы Обработка документов

Обработка сделана для более наглядного отображения процесса групповой работы с документами, чем штатная. И никак не претендует на оригинальность жанра. Требовалась простота и наглядность по работе с признаками документов и их непосредственным удалением из базы.

16.01.2008    20163    1175    Шёпот теней    18       

Сжатие журнала регистрации 11

Инструменты и обработки Системный администратор Программист Внешняя обработка (ert,epf) v77::БУ 1С7:Бух Россия Бесплатно (free) Чистка базы

Сжатие файла 1cv7.mlg В связи с ограничением размера 2Gb на файл, что-бы на резать его на части

18.12.2007    11514    76    kudenzov    7       

Чистка справочников 9

Инструменты и обработки no Внешняя обработка (ert,epf) v7.7 1cv7.md Windows Бесплатно (free) Чистка базы Поиск данных Тестирование и исправление

Обработка позволяет удалять дублированные элементы на которые уже существуют ссылки.

29.11.2007    7128    56    Kolek    10       

Обработка Документов 21

Инструменты и обработки no Внешняя обработка (ert,epf) v77::ОУ v77::БУ v77::Расчет 1cv7.md Windows Бесплатно (free) Сервисные утилиты Чистка базы Обработка документов

Работа с документами, проведение, снятие проведения, пометка на удаление за период. (Работает в любой конфигурации)

26.11.2007    6825    314    alex-vs    2