Альтернативный контроль помеченных и быстрое удаление средствами SQL

0. Валерий Федоров (barelpro) 776 09.01.14 00:06 Сейчас в теме
Эта обработка является логическим продолжением статьи "Свертывание объемной базы средствами SQL" http://infostart.ru/public/249429

Перейти к публикации

Комментарии
1. Сергей Старых (tormozit) 4702 27.01.14 10:37 Сейчас в теме
Зачем делать запрос к таблице ДвиженияССубконто, если ты все равно делаешь запрос к основной таблице регистра бухглатерии? Оптимальнее делать запрос либо только к ДвиженияССубконто либо к двум таблицам: к основной таблице и к таблице Субконто.
2. Валерий Федоров (barelpro) 776 27.01.14 10:51 Сейчас в теме
(1) tormozit,

согласен, но я умышленно разнес на два запроса.

Объясняю:

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

Например, в УПП в регистре бухгалтерии Международный есть реквизит ПервичныйДокумент - составной по всем видам документов. Но любой документ так же может быть в роли субконто этого регистра. Если буду делать все в одном запросе - усложняю задачу по поиску причины тормозов.

PS. Невнимательно прочитал вопрос. Да, запрос к Субконто более оптимальный, чем к ДвиженияССубконто, исправлю, спасибо!
3. Юрий Гончарук (yukon) 72 27.01.14 10:57 Сейчас в теме
И не забывайте, что при массированном удалении объектов база под SQL не уменьшается, а увеличивается за счет разрастания журнала транзакций (*.ldf). За этим надо следить и периодически сжимать, например командой DBCC SHRINKFILE


Вот ведь все хорошо пишите. Но это вот зачем писать-то? На сколько увеличат объем журнала транзакций именно ваши "ручные" транзакции? Да ни насколько.

Периодически сжимать журнал транзакций на рабочей базе нельзя, ну только если вы техномазохист и/или у вас НЖМД в "сервере" аж целых 40Гб.
4. Валерий Федоров (barelpro) 776 27.01.14 11:02 Сейчас в теме
(3) yukon,

вы не поверите, после удаления 50млн записей регистров методом TSQL DELETE база выросла в полтора раза (в моем случае до 150Gb) как раз за счет разрастания журнала транзакций. DELETE пишет себя в журнал транзакций, даже если recovery model = simple.
5. Юрий Гончарук (yukon) 72 27.01.14 11:39 Сейчас в теме
(4)

вы не поверите, после удаления 50млн записей регистров методом TSQL DELETE база выросла в полтора раза


Охотно верю. И как часто вы удаляете 50 млн записей? Неужели ежедневно.

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

в моем случае до 150Gb


Если у вас на сервере один жесткий на 320Gb, то да это ПРОБЛЕМА, согласен.
6. Валерий Федоров (barelpro) 776 27.01.14 17:30 Сейчас в теме
(5) yukon,

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

ps. По своему опыту знаю, что урезание файла журнала транзакций можно выполнять при работающих пользователях. Эта операция быстрая и безболезненная. А вот урезание файла с данными (mdf) может быть долгим, т.к. процесс похож на дефрагментацию диска. Файл данных разрастается, например, после реструктуризации базы - попробую объяснить почему: все таблицы пересоздаются и записываются в конец файла mdf, данные из старых таблиц переносятся в новые, старые удаляются, и появляются дырки в начале файла mdf. Как-то так...
7. Юрий Гончарук (yukon) 72 29.01.14 12:20 Сейчас в теме
По своему опыту знаю, что урезание файла журнала транзакций можно выполнять при работающих пользователях.


Можно, но крайне не нужно.

Эта операция быстрая и безболезненная.


Это не так. Операция далеко не безболезненная.

все таблицы пересоздаются и записываются в конец файла mdf


Это весьма упрощенное и, как следствие, в большинстве случаев, неверное представление. На увеличение размера mdf файла(ов) влияют с десяток факторов. А уж на размещение данных внутри mdf-файла(ов) как бы не сотни.

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

В частности, размер ldf файла в модели восстановление simple SQL сервер выбирает именно такой, чтобы обслуживать базу данных при существующей интенсивности работы. Вместо помощи серверу и увеличения размера ldf файла, хотя бы до размера "чуть больше чем надо", вы предлагаете на постоянной(!) основе вставлять серверу палки в колеса, это же "быстро и безболезненно".
8. Валерий Федоров (barelpro) 776 29.01.14 12:36 Сейчас в теме
(7) yukon,
Юрий, я прямо чувствую, что вы обладаете тайными знаниями, и очень искусно их прячете от общества :)

Дайте пруфлинки, объясните развернуто, чем таким чревато урезание журнала транзакций при подключенных к базе пользователях? Все вам скажут спасибо, если знания будут полезны. Тут атмосфера вполне доверительная.
9. Юрий Гончарук (yukon) 72 29.01.14 16:10 Сейчас в теме
Юрий, я прямо чувствую, что вы обладаете тайными знаниями, и очень искусно их прячете от общества :)

Хорошо хоть не сакральными.

Начнём с Ab ovo: http://msdn.microsoft.com/ru-ru/library/ms189085(v=sql.105).aspx Усечение журнала транзакций

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

Там же схема работы механизма.

http://msdn.microsoft.com/ru-ru/library/ms345414(v=sql.105).aspx Факторы, могущие вызвать задержку усечения журнала

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

Из популярных изложений было отличное на sqlcmd.ru. Правда они чего-то закрылись :( , так, что придется смотреть через archive.org

Первая часть: http://web.archive.org/web/20120416065146/http://www.sqlcmd.ru/trans_log_internals-part01.html Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер. Часть 1/12

Последняя с выводами:http://web.archive.org/web/20120418002619/http://www.sqlcmd.ru/trans_log_internals-part12.html Как перестать называть журнал транзакций SQL Server лог-файлом и прекратить борьбу за его размер. Часть 12/12.

Чего не следует делать с журналом транзакций никогда в жизни ни при каких обстоятельствах.
...
* сжимать (SHRINKFILE) лог. Да, в отличии от предыдущих пунктов нельзя сказать что это — «категорическое нет», но все же «почти всегда нет»;
* сжимать (SHRINKFILE) лог на регулярной основе, по расписанию. А вот это — необсуждаемый, совершенно полный и законченный бред. Даже продуманное и обоснованное однократное сжатие лога следует рассматривать как ситуацию чрезвычайную и как попытку предотвратить надвигающуюся катастрофу. Делая же это по расписанию вы признаете, что не прочь поработать «пожарником» на постоянной основе, вместо того что бы один раз тщательно потушить все «очаги возгорания» имеющиеся во вверенной вашим заботам системе.


Пропажу обнаружил вот прямо сейчас. Надо озаботиться сохранением в нормальном виде.
10. Валерий Федоров (barelpro) 776 29.01.14 17:09 Сейчас в теме
(9) yukon,

вот, уже лучше, осталось только чтобы ссылки открывались :)
11. Юрий Гончарук (yukon) 72 29.01.14 17:15 Сейчас в теме
(10)

По клику не открываются - какой-то набор букв вываливает :( а copy-paste работает. Мистика :)
12. Валерий Федоров (barelpro) 776 29.01.14 18:30 Сейчас в теме
(11) yukon,

прочитал, но так и не нашел ответа на свой вопрос: если у нас объемная 1С-база, с ежедневным полным бэкапом и включенным recovery mode = simple, и мы провели массированное удаление с помощью команды T-SQL DELETE, и получили распухший журнал транзакций, соизмеримый с размером файла данных, и видим, что свободное файловое пространство на исходе, и спинным мозгом чувствуем, что в любой момент наступит коллапс всей базы, что же все-таки КОНКРЕТНО нам мешает в этом случае сделать усечение файла журнала транзакций?
13. Юрий Гончарук (yukon) 72 30.01.14 10:18 Сейчас в теме
(12)

прочитал


0_0 за час? Там только чтения страниц 30 А4. Минимум на весь день, а с практикой так и на все 3-4 дня.

и видим, что свободное файловое пространство на исходе


Это как так? 150 гигов для современных дисков это не очень внушительный объем. Даже для серверных версий дисков. Заложите в бюджет расширение дискового пространства.

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

все-таки КОНКРЕТНО нам мешает в этом случае сделать усечение файла журнала транзакций


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

В нормальном режиме эксплуатации SQL сервер самостоятельно производит усечение журнала по мере необходимости.
15. Валерий Федоров (barelpro) 776 30.01.14 15:13 Сейчас в теме
(13) yukon,

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

PS. Для меня, как для внешнего подрядчика обычно недоступны все IT-ресурсы заказчика. Чаще всего они выделяются по мере необходимости - нарезаются виртуальные машины с определенными параметрами. Поэтому на тестовых базах такое часто случается - недостаток дискового пространства. Эту ситуацию я и описал выше.
14. Александр Зубцов (iov) 363 30.01.14 14:36 Сейчас в теме
(9)(10)(11)
архив zip Сохранил в одном архиве для истории.
16. Юрий Гончарук (yukon) 72 30.01.14 18:30 Сейчас в теме
(14)

Спасибо.

(15)

как правильно настраивать журнал транзакций


Ну вы и задачки ставите. "Правильно" - это к SQL DBA, а "на пальцах" могу попробовать.
17. Павел Заяш (Pavl0) 84 28.02.14 12:38 Сейчас в теме
Маленький баг есть. Получить его сложно. Если в конфигурации количество регистров > 255 то на базе MS SQL упадет при заполнении объектов поиска. У меня такая ситуация на УПП+Appius на документе КорректировкаЗаписейРегистров.
Просто повесил проверку на количество таблиц.
18. Андрей Журавлев (Wrols) 67 29.04.14 19:36 Сейчас в теме
Почему-то в моем случае сразу не получилось запросто использовать...

В базе все документы за сворачиваемый период уже помечены на удаление.

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

В процедуре "АнализОднойИзТаблиц" в строке модуля 739 под комментом "еще раз проверим отсутствие движений" выполняется запрос на наличие движений у объекта с условием "НЕ ТекДокумент.ПометкаУдаления".
При этом результат запроса всегда пустой и удаление не происходит.

К слову, в выражении "ТекстЗапроса_БезДвижений" тоже содержится условие на "НЕ ТекДокумент.ПометкаУдаления".
igor.ofitserov; pit201201; Збянтэжаны Саўка; +3 Ответить
22. Саўка Збянтэжаны (Збянтэжаны Саўка) 236 20.11.14 15:11 Сейчас в теме
глюк какой-то?, не могу изменить адресат поста, ошибся и ответил 20-му
(18) Wrols, (19) timm00,
мой предыдущий пост предназначался вам
19. Timur (timm00) 97 01.06.14 09:23 Сейчас в теме
Это все потому что данный запрос относится только к таблице документов без движений. А автор как-то забыл поставить условие. После правки данного блока вроде все нормально.
Збянтэжаны Саўка; +1 Ответить
21. Саўка Збянтэжаны (Збянтэжаны Саўка) 236 20.11.14 14:18 Сейчас в теме
(19) timm00, (18) Wrols,
насколько я понял то там ошибка не в запросе, а в условии после него:
вместо условия
Если Запрос.Выполнить().Выбрать().Количество() = 0 Тогда
нужно
Если Запрос.Выполнить().Выбрать().Количество() > 0 Тогда

т.е. если есть движения, то нельзя удалять

//еще раз проверим отсутствие движений
Если Строка.ТипМД = "Документ" Тогда

  Запрос.Текст = 
    "ВЫБРАТЬ ПЕРВЫЕ 1 1
    |"
    + НайденныеСтроки[0].ТекстЗапроса_БезДвижений
    + "
    |И ТекДокумент.Ссылка = &Параметр
    |И НЕ ТекДокумент.ПометкаУдаления
    |";
					
  //Если Запрос.Выполнить().Выбрать().Количество() = 0 Тогда
  Если Запрос.Выполнить().Выбрать().Количество() > 0 Тогда
    Строка.МожноУдалять = Ложь;
    Прервать;
  КонецЕсли;

КонецЕсли;
Показать
pit201201; +1 Ответить
34. Сергей (avasl) 60 27.12.16 18:07 Сейчас в теме
(21) правильное исправление такое

добавить перед выполнением запроса строку

Запрос.Текст = СтрЗаменить(Запрос.Текст, "НЕ ТекДокумент.ПометкаУдаления", "ИСТИНА")


Кусок тогда будет выглядеть так

				//еще раз проверим отсутствие движений
				Если Строка.ТипМД = "Документ" Тогда
					
					Запрос.Текст = "ВЫБРАТЬ ПЕРВЫЕ 1 1
					|"
					+ НайденныеСтроки[0].ТекстЗапроса_БезДвижений
					+ "
					|И ТекДокумент.Ссылка = &Параметр
					|И НЕ ТекДокумент.ПометкаУдаления";
					
					//s
					Запрос.Текст = СтрЗаменить(Запрос.Текст, "НЕ ТекДокумент.ПометкаУдаления", "ИСТИНА");
					
					Если Запрос.Выполнить().Выбрать().Количество() = 0 Тогда
						
						Строка.МожноУдалять = Ложь;				
						Продолжить; //s //Прервать;
						
					КонецЕсли;
					
				КонецЕсли;
Показать


Тогда для любого очередного документа к удалению, не важно помеченный он или нет, будет проверяться отсутствие движений. Если есть движение хоть в каком то регистре, где документ является регистратором, то результат запроса будет пустой и соответственно выполнится условие
Запрос.Выполнить().Выбрать().Количество() = 0

и у документ справедливо не будет удален.

Автору бы не мешало поправить обработку
20. berator37 (berator37) 70 31.08.14 08:59 Сейчас в теме
Можно на почту Альтернативный контроль помеченных и быстрое удаление средствами SQL berator37@mail.ru . Заранее спасибо
23. Николай Зевеке (zekrus) 153 20.01.15 11:15 Сейчас в теме
Добрый день!
Пытаюсь в УТ 11.0 почистить справочник "Контрагенты".
{Форма.Форма.Форма(539)}: Поле объекта не обнаружено (ОбщийРеквизит)
МдРез = Метаданные[ИмяПоля];
romku; pit201201; +2 Ответить
26. Дмитрий Ивакин (pit201201) 66 29.07.15 09:06 Сейчас в теме
(23) zekrus,
В своей версии обработки я добавил
СпЗамен.Вставить("ОбщийРеквизит", "ОбщиеРеквизиты");
последней строчкой. Вроде взлетело.
ElektronHM; +1 Ответить
24. Dmitry Bas (b-dm) 168 28.01.15 12:38 Сейчас в теме
А можно ли как то удалить документы за определенный период ?
25. Алексей Голосеев (Aleksey81) 553 05.04.15 19:23 Сейчас в теме
barelpro, вы очень душевно поработали. Восхищает, что обработка (субъективно) раз в 50 быстрее удаляет данные и при этом позволяет другим пользователям полноценно работать с базой (в том числе запись и проведение документов. Спасибо.
Однако есть и просьба устранить две странные ошибки.
1) Заменить процедуру ЗначениеНеЗаполнено на сам знаете что....
2) Возможно я не прав, но.... у похоже в коде процедуры АнализОднойИзТаблиц
Понадобилось заменить
						
Строка.МожноУдалять = Ложь;				
//Алексей
//Возврат; неправильная команда
Продолжить;

Потому как ваша обработка останавливает любую активность, как только находит строку, которую удалять нельзя.
Поправьте меня, если я не прав.
27. Дмитрий Ивакин (pit201201) 66 29.07.15 09:11 Сейчас в теме
Хорошая обработка! Даже не смотря на некоторые, выше отмеченные ошибки.
Есть предложение добавить опцию "отложенное удаление", при котором скрипт не выполняется, а сохраняется в текстовый файл.
28. Дмитрий Ивакин (pit201201) 66 29.07.15 10:14 Сейчас в теме
Очень не хватает функции "Разорвать циклическую ссылку" .
А то есть два документа СчетФактураВыданный (реквизит ДокументОснование) и ОказаниеУслуг(реквизит СчетФактура) оба помечены на удаление и ссылаются друг на друга.
29. Дмитрий Ивакин (pit201201) 66 29.07.15 10:35 Сейчас в теме
Чтобы удалить документы, ссылающиеся друг на друга добавил строки

Если (Найти(ИмяТаблицыВЗапросе,"Документ.")>0) Тогда 
    НоваяСтрока.ТекстЗапроса = НоваяСтрока.ТекстЗапроса + " И НЕ Т.Ссылка.ПометкаУдаления";
КонецЕсли;

в процедуру СоздатьЗапросВСхемеАнализа

Очень хочется услышать мнение автора обработки на этот счет.
30. Евгений Цыбань (Tciban) 30.09.15 10:02 Сейчас в теме
(29) pit201201, А где это добавить, поточнее можно?
31. Dmitry Bas (b-dm) 168 18.10.16 18:03 Сейчас в теме
32. Дмитрий Шпаковский (Godman) 47 26.12.16 16:44 Сейчас в теме
Не работает :((((
При удалении без проверки на ссылки выдало:
{ВнешняяОбработка.УдалениеОбъектовBarelpro.Форма.Форма.Форма(777)}: Значение не является значением объектного типа (НайтиСтроки)
НайденныеСтроки = СХБЗ.НайтиСтроки(Новый Структура("ТипМД, ОбъектМД", Строка.ТипМД, Строка.ОбъектМД));
33. Николай Зевеке (zekrus) 153 26.12.16 20:36 Сейчас в теме
Странно у меня работает.
35. Александр Орлов (user839171) 18.10.17 13:23 Сейчас в теме
На файловой базе не заработала
Проходит анализ объектов, но объекты в результате не помечаются галочкой "Можно удалять" и не удаляются.
Жаль бессмысленно потраченные 5 $m .
Оставьте свое сообщение