Реорганизация индексов базы данных MS SQL "Невозможно реорганизовать индекс"

1. 09.11.22 11:35 Сейчас в теме
Приветствую вас заклинатели железа, направляю вам преинтереснейшую ситуёвину для разбора и помощи вашему слуге.

Проблема: После обновления платформы 8.3.20.1674->8.3.22.1603 сломалось автоматическое резервное копирование баз данных, созданное менеджером в SSMS 2012. При попытке реиндексации баз данных коих у нас не одна, ssms ругается на "Невозможно реорганизовать индекс "_Acc48_1" в таблице "_Acc48", поскольку отключена блокировка на уровне страницы." И так на 30к+ индексов. Во всех базах где произошло какое-то обновление/регламентное задание/бог его знает что ещё. В кратце, для реиндексации нужно чтобы все таблицы баз были с включенным параметром "allow_page_locks" или как описан в клиенте он "Блокировка страниц в памяти".

Что пробовал?:
· Гуглил. Не помогло.
· Доставать dt сувать обратно, индексы логично достаются и суются вместе с базой. Не помогло.
· Поднимал бэкап до обновления платформы в тест базу, смотрел что в индексах с нашим параметром. Там везде "ON". У нас после обновы в 30к "OFF".
· В небольших базах было по 1-2 индекса с флагом офф, проставлял там руками параметр в истину, запускал план бэкапа, реиндексация и сам бэкап проходили на ура. Сделал вывод, что выставив как-то параметр в 30к индексах я таки получу автобэкапирование, но не пойму причины возникновения отключения параметра. Можно утолкать кусок скрипта перед реиндексацией который будет ставить параметр в "ON". Костыль....
· Пробовал делать тестирование и исправление ошибок через конфигуратор. Не помогло.

Люди добрые:
Кто сталкивался с подобным? Подскажите как решали.
Кто сможет написать запрос который в автомате проставит флаг во всех индексах всех таблиц базы, буду очень признателен.
Конструкция
ALT ER   INDEX ALL ON <Table name>
SET (ALLOW_PAGE_LOCKS = ON)
GO
не помогает, так как во втором параметре нужна таблица, как туда указать все таблицы, не знаю, не могу, не умею.
thunder367; +1 Ответить
Найденные решения
8. splxgf 09.11.22 16:35 Сейчас в теме
(1)
как туда указать все таблицы, не знаю

USE имя базы
EXEC sp_MSforeachtable 'ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)' 
GO

Проверьте такой вариант, имя таблицы автоматом подставляется вместо знака вопроса.
yaguarrr; +1 Ответить
Остальные ответы
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
7. redfred 09.11.22 13:17 Сейчас в теме
(8)
(1)
Кто сможет написать запрос который в автомате проставит флаг во всех индексах всех таблиц базы, буду очень признателен.


Накидал на коленке, никаких гарантий, юзать на свой страх и риск

USE [enter_database_name_here]
GO

DECLARE @object_schema VARCHAR(256);
DECLARE @object_name VARCHAR(256);
DECLARE @index_name VARCHAR(256);

DECLARE db_cursor CURSOR FOR 
SEL ECT OBJECT_SCHEMA_NAME(object_id) AS object_schema,
       OBJECT_NAME(object_id)        AS object_name,
       name                          AS index_name
FR OM   sys.indexes
WH ERE  allow_page_locks = 0 AND OBJECT_SCHEMA_NAME(object_id) != 'sys';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @object_schema, @object_name, @index_name;
WHILE @@FETCH_STATUS = 0  
BEGIN  

	   EXEC ('ALT ER   INDEX ' + @index_name + ' ON ' + @object_schema + '.'+ @object_name + ' SET (ALLOW_PAGE_LOCKS = ON)');
       FETCH NEXT FR OM db_cursor INTO @object_schema, @object_name, @index_name;

END;
CLOSE db_cursor;
DEALLOCATE db_cursor;
Показать
yaguarrr; +1 Ответить
8. splxgf 09.11.22 16:35 Сейчас в теме
(1)
как туда указать все таблицы, не знаю

USE имя базы
EXEC sp_MSforeachtable 'ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)' 
GO

Проверьте такой вариант, имя таблицы автоматом подставляется вместо знака вопроса.
yaguarrr; +1 Ответить
2. user1203706 09.11.22 12:29 Сейчас в теме
(1) добавьте эти таблицы в исключения при реорганизации индекса.

а так, ну вот например, скрипт изменения всех табличек в бд
https://social.technet.microsoft.com/Forums/en-US/d6199a0a-c8ed-4095-88b8-b7c8964cda6e/change-index-of-all-tables-in-at-the-databases-on-a-server?forum=transactsql

только, кто-то же в off вам выставил изначально не просто так...оптимизировали поди
3. tekit 09.11.22 12:35 Сейчас в теме
(2) До обновления платформы все индексы были ON, лишь после обновления они чем-то выставились в OFF. Раз это мешает реорганизации, полагаю это не оптимизация, а ошибка всё-таки.

добавьте эти таблицы в исключения при реорганизации индекса.

Там тупо все таблицы нужно добавить в исключения, и это я не преувеличиваю, какой тогда смысл в реорганизации?
4. user1203706 09.11.22 12:36 Сейчас в теме
(3) дык откати платформу взад.
Нет 22 платформы, чтоб проверить, как оно меняет струткуру табличек
10. tekit 10.11.22 09:32 Сейчас в теме
(4)Задом давали, не помогло. Индексы уже изменены
5. user1203706 09.11.22 12:38 Сейчас в теме
Или выкини из обслуживания баз реорганизацию индекса.
Хотя, перевести в OFF, чтоб от взаимоблокировок избавиться...ну такое.
9. booksfill 09.11.22 17:25 Сейчас в теме
(5) Хм... Мало того, что такое решение (OFF) не разрешит реорганизацию индексов, а только перестроение.

Это еще может привести вместо блокировки на уровне страницы к блокировки на уровне всей таблицы.
Короче, я с вами согласен - я бы все включил обратно.

Есть слабая надежда, что просто алгоритм обновления зачем-то (для ускорения? Есть подозрение, что тогда надо бы проверить еще ALLOW_ROW_LOCK, ежели и тут OFF, все понятно - ускорялись, блокируя потаблично) отключил ALLOW_PAGE_Locks и просто забыл включить обратно.
12. tekit 10.11.22 09:37 Сейчас в теме
(9)Переживаю, что включение мне не даст решения проблемы, уже вторую последнюю платформу поставили, а результат тот же, после каких-то манипуляций, в индексах всех флаги постраничной блокировки уходят в офф.

Откатит на более раннюю платформу не вариант, говорят в бухне есть что-то из-за чего на платформе 20, не бельме... А вот на 22 на ура...

Пока не уточнял, что там такое интересное, что прям необходимо бухне в 22 версии...
18. TormDV 10.11.22 11:51 Сейчас в теме
(12) бухне пока еще 17 платформы достаточно)
19. booksfill 10.11.22 13:56 Сейчас в теме
(12) Ну, что касается интересов бухгалтерии, сильно сомневаюсь, что им нужна 22.
Просто сделайте как в 8, только проверьте, возможно надо будет еще включить ALLOW_ROW_LOCKS.

Т.е. добавить еще одну строку:
USE имя базы
EXEC sp_MSforeachtable 'ALT ER     INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)' 
EXEC sp_MSforeachtable 'ALT ER     INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)' 
GO
Lyubogradov.P; VirDim; +2 Ответить
20. tekit 10.11.22 16:43 Сейчас в теме
6. user1203706 09.11.22 12:39 Сейчас в теме
На какие индексы в OFF переведено ? Поди на все таблички регистров, да ?...
11. tekit 10.11.22 09:34 Сейчас в теме
(6)Вообще на всех индексы базы
13. triviumfan 37 10.11.22 09:46 Сейчас в теме
Даже интересно стало. Попробую накатить 8.3.22.1603.
А вообще, как резервное копирование связано с реиндексацией? Я в танке.
ЗЫ: такого релиза нет! Видимо откатили, есть только 8.3.22.1672. Честно сказать, кто сейчас ставит 8.3.22 платформу... только тестовые кролики. Вероятно, это была платформа для тестирования.
15. tekit 10.11.22 10:13 Сейчас в теме
(13) ну вот да... упущение... но что поделать, ребята уже провернули эту манипуляцию...
22. user1831019 10.11.22 17:08 Сейчас в теме
(15) Поперёк батьки и в пекло? У вас проблемы с организационными решениями...
24. tekit 11.11.22 09:16 Сейчас в теме
(22) Я человек новый, сделали данный неосмотрительный шаг меня не спросив)
16. tekit 10.11.22 10:16 Сейчас в теме
(13)
8.3.22.1672

Позавчера обновились до этого релиза, и после обновления ещё пару баз ушли в OFF.
Тобишь прикол сохраняется и в 8.3.22.1672.
23. triviumfan 37 10.11.22 17:12 Сейчас в теме
(16) у меня нет таких проблем на 8.3.22.1672 =\
14. user1203706 10.11.22 09:51 Сейчас в теме
(11) видать, тестовая какая платформа была. Пробуйте (8).
17. tekit 10.11.22 10:16 Сейчас в теме
(14) Да, ща админ проснётся и пойдём пробовать на кошках
21. tekit 10.11.22 17:00 Сейчас в теме
ALLOW_ROW_LOCK все ON
С этим флагом всё ок
25. tekit 11.11.22 09:25 Сейчас в теме
Огромное всем спасибо за проявленный интерес к проблеме и ответы с решениями, причины пока не нашли, тестим, выясняем какое регламентное меняет наш параметр. Запрос (8), помог исключительно, если не найдём корень проблемы сунем эту вещь с условием перед реорганизацией, в плане бэкапов
33. Oleg_Bik 15.11.22 12:07 Сейчас в теме
(25) При первой реструктуризации после обновления платформы, 1С запросом получает список индексов
sel ect a.name, b.name fr om sys.indexes a join sys.tables b on a.object_id = b.object_id join sys.schemas s ON b.schema_id = s.schema_id and s.name = 'dbo' where a.[allow_page_locks] = 1 and a.index_id <> 0
и затем принудительно изменят параметр для каждого.
Можно посмотреть профайлером, при обновлении конфигурации.
26. Derek777 3 11.11.22 12:01 Сейчас в теме
2 релиза изъяли из обращения, с новым полный абзац!!!!! Такая же проблема.... Там в 1С чего курят? Или чего по серьезнее принимают?

Собственно вопрос следующий, что решило проблему, ALLOW_ROW_LOCK все ON или ALLOW_PAGE_LOCKS = ON?
27. user1203706 11.11.22 12:07 Сейчас в теме
(26) ALLOW_PAGE_LOCKS = ON вестимо, остальное 1с-ина не меняла
28. Derek777 3 11.11.22 12:25 Сейчас в теме
Неправильный синтаксис около "INDEX". Если имелась в виду часть табличного указания, то для этого теперь необходимо использовать ключевое слово A WITH и круглые скобки. Правильный синтаксис см. в электронной документации по SQL Server.
50. fenixoid 24.11.22 17:20 Сейчас в теме
(28)

USE "BASENAME"
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)'
GO
29. user1871657 12.11.22 20:37 Сейчас в теме
8.3.22.1672 платформа , запустил обслуживание баз такая же ошибка
Сбой выполнения запроса

-1073548784 ALT ER INDEX [_AccRg17610_1X1] ON [dbo].[_AccRg1761..."
30. acsent 1188 14.11.22 12:46 Сейчас в теме
можно попробовать трейс собрать с фильтром ALLOW_PAGE_LOCKS
31. triviumfan 37 14.11.22 16:39 Сейчас в теме
32. ViDi 15.11.22 10:45 Сейчас в теме
Та же проблема на 8.3.22.1603. Решал скриптом с условием - если ошибка реорганизации, то ALLOW_PAGE_LOCKS = ON и снова реорганизовать. Но, например, в Документообороте, данное решение помогало на 3 дня. Приходилось по новой скрипт запускать. Вчера установил 8.3.22.1704. Естественно, существующие ошибки не ушли. Сегодня пройдусь скриптом. Посмотрим, вылезут ли ошибки реорганизации на новой платформе.
34. tekit 15.11.22 14:33 Сейчас в теме
(32) На днях убедился что при любой реструктуризации базы, в индексах флажок ставится в off
35. user1203706 15.11.22 16:13 Сейчас в теме
(34) праильно.. тока почему они не вертают его обратно потом ?
36. Monstrilo 15.11.22 20:49 Сейчас в теме
Добрый вечер!
Тоже словили эту "ошибку" на планах обслуживания. Не реорганизуется индекс: "...При попытке реиндексации баз данных "Невозможно реорганизовать индекс "_Acc48_1" в таблице "_Acc48", поскольку отключена блокировка на уровне страницы....".
Посмотрел, куча индексов с отключенным параметром блокировки в базах (ALLOW_PAGE_LOCKS). Это косяк платформы или чего? До перехода на 8.3.22.1704 все было в порядке. Может кто-то подсказать, что делать-то?? Будет ли работать не реорганизация, а перестроение индекса?

(34) "На днях убедился что при любой реструктуризации базы, в индексах флажок ставится в off" - что с этим делать? Не подскажете?
41. tekit 16.11.22 09:44 Сейчас в теме
(36) На днях убедился что при любой реструктуризации базы, в индексах флажок ставится в off" - что с этим делать? Не подскажете?

Как уже ранее написали, воспользуйся скриптом с (8), затем даунгрейд до 8.3.19.1723(она минимальна для работы без выбросов из 19 серии), потом измени что-то в базе чтобы запустилась реструктуризация или запусти обновление базы через строку запуска /обновитьбазуданных или что там, нагугли. И затем скриптом нужно будет пробежаться проверить, есть ли индексы с off или нету. Просто они в off уходят когда обнова проходит только. Без обновы базы не добиться изменения индексов. Если 8.3.19.1723 решит проблему, то заходи сюда, пиши
37. user1203706 15.11.22 21:08 Сейчас в теме
(36) очевидно же, что баг в новых версиях платформы. Поменять ручонками через (8) и откатить платформу на постарее, Ну или бежать впереди паровоза и быть бесплатным бетта-тестером товарищей с Селезнёвки.
38. ansh15 15.11.22 21:15 Сейчас в теме
Потом окажется, что это фича...
39. Monstrilo 16.11.22 09:00 Сейчас в теме
(37) Причем здесь впереди паровоза. Поменять на постарее и получить вылеты как у половины РФ. Сначала обновлениями типовых вынудили менять релиз платформы, а теперь такая фича...
40. tekit 16.11.22 09:38 Сейчас в теме
(39)версий платформ ниже 8.3.22 в которых нет вылетов, много. Загляни в users, там 14.11 вышла куча платформ и 8.3.19/20/21/22
Попробуй себе в 8.3.19.1723 ткнуть, она точно будет работать и точно выкидывать не будет, но я не знаю решает ли она проблему с индексами. На моём нынешнем месте рабочем, пока нет доступа к sql чтобы мне этот момент затестить. Если будешь тестить отпишись пожалуйста о результате.
42. user1203706 16.11.22 11:14 Сейчас в теме
(39) притом, что из вас делают бесплатных беттатестеров.
Все типовые торчат еще в хрен знает каком далёком режиме совместимости, на 15 то и то, только в этом году переход был (в котором конкретно РБ изменили, и он, самый тяжелый для перехода больших баз, ибо реструктуризация невъ..ная).
А все остальные плюшки платформы, типовые конфы не используют от слова совсем. Смысла ставить последние релизы нет.
ЗЫ: А падение платформы, без её замены, можно вылечить фаерволом, тупо забанив ip и порты, куда 1с-ина щемится.

Но, то что они в последних платформах отключают блокировку страниц, грозит рассогласованностью данных в дальнейшем, тут надо на партнёрке вопросы задавать - какого хрена ?
43. redfred 16.11.22 11:46 Сейчас в теме
(42)
Но, то что они в последних платформах отключают блокировку страниц, грозит рассогласованностью данных в дальнейшем


Оно не должно так радикально влиять, что прямо рассогласованность данных образуется
44. tekit 16.11.22 21:14 Сейчас в теме
Наконец-то
Прикрепленные файлы:
45. Monstrilo 16.11.22 22:50 Сейчас в теме
46. Monstrilo 16.11.22 22:56 Сейчас в теме
(44) Т.е. надо на 1709 переходить?
47. user1203706 16.11.22 23:19 Сейчас в теме
(46) нет, вам придётся выкинуть реорганизацию индекса с плана обслуживания, 1с-ина теперь решила таким способом бороться с блокировками - сняв блокировку индекса на уровне страниц
48. user1203706 16.11.22 23:20 Сейчас в теме
А переход ничего не даст.
user1446119; +1 Ответить
49. Monstrilo 16.11.22 23:24 Сейчас в теме
(48) Спасибо большое! По блокировкам разобрался, сначала не догнал. В 1709 исправили ошибку "Ошибка преобразования данных XDTO".
51. fenixoid 24.11.22 17:23 Сейчас в теме
Всем привет! Кто как в итоге решил вопрос с реорганизацией? Я так понимаю 3 варианта: откатиться с 22 платформы, вставить скрипт в план обслуживания перед реорганизацией и отказаться от реорганизации?
54. tekit 02.12.22 19:43 Сейчас в теме
(51)отказаться, 1с намерено убрали блокировки, в описании обновления к платформе есть инфа
52. maikl007 87 01.12.22 11:56 Сейчас в теме
DECLARE @Database NVARCHAR(255) 
DECLARE DatabaseCursor CURSOR READ_ONLY FOR 

SEL ECT name FR OM master.sys.databases   
WH ERE name NOT IN ('master','msdb','tempdb','model','distribution')  -- исключаемые базы данных
AND name IN ('acount_01_usn') -- используется для выбора конкретных баз данных (раскомментируйте эту строку)
AND state = 0 -- база данных онлайн
AND is_in_standby = 0 -- база данных не читается только для log shipping
ORDER BY 1

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database 
WHILE @@FETCH_STATUS = 0 
	BEGIN 
		PRINT 'sp_MSforeachtable ''ALT ER   INDEX ALL ON ' + @Database + '.? SET (ALLOW_PAGE_LOCKS = ON)'''
		EXEC('sp_MSforeachtable ''ALT ER   INDEX ALL ON ' + @Database + '.? SET (ALLOW_PAGE_LOCKS = ON)''')
		
		FETCH NEXT FR OM DatabaseCursor INTO @Database
	END  
CLOSE DatabaseCursor 
DEALLOCATE DatabaseCursor
Показать
53. yaguarrr 71 02.12.22 16:16 Сейчас в теме
Кто-то что-то решил кроме скрипта в (8)? * на 8,3,19 не помогает
55. tekit 02.12.22 19:44 Сейчас в теме
56. thunder367 06.12.22 15:49 Сейчас в теме
Это просто праздник какой-то! Все работало и одномоментно сломалось...

Запустили свои шаловливые ручонки в настройки блокировок... Теперь ждать пока дефрагментация не достигнет уровня > 30% и делать сразу перестроение.
57. thunder367 06.12.22 16:45 Сейчас в теме
Может можно на время реорганизации индекса для каждой таблицы менять состояние ALLOW_PAGE_LOCKS а потом возвращать обратно? Кто скрипт может намутить?
Оставьте свое сообщение
Вакансии
Ведущий разработчик 1С
Новосибирск
зарплата от 120 000 руб. до 150 000 руб.
Полный день

Ведущий Консультант/Аналитик 1С
Москва
зарплата от 150 000 руб. до 250 000 руб.
Полный день

Программист, аналитик, эксперт 1С
Санкт-Петербург
По совместительству

Программист 1С
Великие Луки
зарплата от 100 000 руб. до 120 000 руб.
Полный день

Программист 1С
Красноярск
зарплата от 160 000 руб. до 180 000 руб.
Полный день