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

1. tekit 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
не помогает, так как во втором параметре нужна таблица, как туда указать все таблицы, не знаю, не могу, не умею.
user1628996; SoDm; eklekt; ALmighty; mamonth; user986816; agentesecreto; mafnitofon; onetone; arkitekt; KirillZ44; thunder367; +12 Ответить
Найденные решения
8. splxgf 09.11.22 16:35 Сейчас в теме
(1)
как туда указать все таблицы, не знаю

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

Проверьте такой вариант, имя таблицы автоматом подставляется вместо знака вопроса.
корум; The Ant; Lacoste4life; aqualife; fuser; onetone; MaZaHacKa_13; arkitekt; Sley; maikl007; yaguarrr; +11 Ответить
Остальные ответы
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
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;
Показать
alexex; Indgo; yaguarrr; +3 Ответить
8. splxgf 09.11.22 16:35 Сейчас в теме
(1)
как туда указать все таблицы, не знаю

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

Проверьте такой вариант, имя таблицы автоматом подставляется вместо знака вопроса.
корум; The Ant; Lacoste4life; aqualife; fuser; onetone; MaZaHacKa_13; arkitekt; Sley; maikl007; yaguarrr; +11 Ответить
2. user1203706 14 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 14 09.11.22 12:36 Сейчас в теме
(3) дык откати платформу взад.
Нет 22 платформы, чтоб проверить, как оно меняет струткуру табличек
10. tekit 10.11.22 09:32 Сейчас в теме
(4)Задом давали, не помогло. Индексы уже изменены
5. user1203706 14 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
katavy; Lyubogradov.P; VirDim; +3 Ответить
20. tekit 10.11.22 16:43 Сейчас в теме
6. user1203706 14 09.11.22 12:39 Сейчас в теме
На какие индексы в OFF переведено ? Поди на все таблички регистров, да ?...
11. tekit 10.11.22 09:34 Сейчас в теме
(6)Вообще на всех индексы базы
13. triviumfan 96 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 96 10.11.22 17:12 Сейчас в теме
(16) у меня нет таких проблем на 8.3.22.1672 =\
14. user1203706 14 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 4 11.11.22 12:01 Сейчас в теме
2 релиза изъяли из обращения, с новым полный абзац!!!!! Такая же проблема.... Там в 1С чего курят? Или чего по серьезнее принимают?

Собственно вопрос следующий, что решило проблему, ALLOW_ROW_LOCK все ON или ALLOW_PAGE_LOCKS = ON?
27. user1203706 14 11.11.22 12:07 Сейчас в теме
(26) ALLOW_PAGE_LOCKS = ON вестимо, остальное 1с-ина не меняла
28. Derek777 4 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
Lacoste4life; +1 Ответить
29. user1871657 12.11.22 20:37 Сейчас в теме
8.3.22.1672 платформа , запустил обслуживание баз такая же ошибка
Сбой выполнения запроса

-1073548784 ALT ER INDEX [_AccRg17610_1X1] ON [dbo].[_AccRg1761..."
30. acsent 1201 14.11.22 12:46 Сейчас в теме
можно попробовать трейс собрать с фильтром ALLOW_PAGE_LOCKS
31. triviumfan 96 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 14 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 14 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 14 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 14 16.11.22 23:19 Сейчас в теме
(46) нет, вам придётся выкинуть реорганизацию индекса с плана обслуживания, 1с-ина теперь решила таким способом бороться с блокировками - сняв блокировку индекса на уровне страниц
user1300601; +1 Ответить
48. user1203706 14 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 92 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
Показать
yuryshestakov; +1 Ответить
53. yaguarrr 72 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 а потом возвращать обратно? Кто скрипт может намутить?
60. minarenko 07.12.22 14:04 Сейчас в теме
(57) на итс так и пишут,
Важно! Начиная с версии платформы 8.3.22 необходимо выполнять дефрагментацию индексов по следующему алгоритму:

До дефрагментации индекса необходимо включить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);
Выполнить дефрагментацию.
Обратно выключить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);


https://its.1c.ru/db/metod8dev/content/5837/hdoc
user1628996; alexex; TormDV; +3 Ответить
61. TormDV 07.12.22 15:34 Сейчас в теме
(60) Создали себе проблему, потом героически ее превозмогают.
А насколько быстро это переключается? если базы в сотни гигабайт - терабайты..
yaguarrr; +1 Ответить
58. thunder367 07.12.22 11:50 Сейчас в теме
Короче сделал сам. Я пользуюсь скриптом Tavalik для работы с индексами в ежедневном плане обслуживания.
Внес изменения, чтобы не вылетала реорганизация индекса с описанной в этом топике ошибкой.

Реорганизация индекса таблицы производится теперь только при установленном для нее ALLOW_PAGE_LOCKS. Если параметр ALLOW_PAGE_LOCKS не установлен, то реорганизация индекса таблицы не производится. Дожидаемся, пока уровень дефрагментации этого индекса достигнет 30% и проводим перестроение индекса.

ИМХО, это решение - "меньшее из зол", т.к. устанавливать параметр ALLOW_PAGE_LOCKS на все индексы таблиц противоречит внесенным 1С изменениям, может повлиять на блокировки и стабильность работы всей 1С.
А проводить ежедневное тотальное перестроение индекса потребует повышенных временных затрат и увеличение нагрузки.

-------------------------------------------
-- Выполняет реорганизацию либо дефрагменатцию индексов указанной базы данных
-- Алгоритм пработы:
--  1. Собираются информация обо всех фрагментированных индексах (степерь фрагментации более 5%)
--  2. Если фрагментация менее или равна 30% тогда выполняется дефрагментация, иначе реиндексация индекса
-- Автор: Онянов Виталий (Tavalik.ru)
-- Версия от 20.05.2017

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- База данных для анализа
USE <имя базы>

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ 
DECLARE @object_id int; -- ID объекта
DECLARE @index_id int; -- ID индекса
DECLARE @partition_number bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы 
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @fragmentation_in_percent float; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @allow_page_locks bit; -- состояние параметра allow_page_locks в индексе таблицы
-------------------------------------------
-- ТЕЛО СКРИПТА

-- Отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
SET NOCOUNT ON;

-- Удалим временные таблицы, если вдруг они есть
IF OBJECT_ID('tempdb.dbo.#work_to_do') IS NOT NULL DR OP   TABLE #work_to_do

-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые:
--  являются индексами (index_id > 0)
--  фрагментация которых более 5% 
--  количество страниц в индексе более 128 
SEL ECT
	sys.dm_db_index_physical_stats.object_id,
	sys.dm_db_index_physical_stats.index_id,
	sys.dm_db_index_physical_stats.partition_number,
	sys.dm_db_index_physical_stats.avg_fragmentation_in_percent as fragmentation_in_percent,
	sys.indexes.allow_page_locks
INTO #work_to_do
FR OM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED'), sys.indexes
WHERE sys.dm_db_index_physical_stats.index_id > 0 
	AND sys.dm_db_index_physical_stats.avg_fragmentation_in_percent > 5.0
	AND sys.dm_db_index_physical_stats.page_count > 128;

-- Объявление Открытие курсора курсора для чтения секций
DECLARE partitions CURSOR FOR SEL ECT * FR OM #work_to_do;
OPEN partitions;

-- Цикл по секциям
FETCH NEXT FR OM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks;
WHILE @@FETCH_STATUS = 0
	BEGIN  

-- Собираем имена объектов по ID  
	SEL ECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
		FR OM sys.objects AS o
		JOIN sys.schemas as s ON s.schema_id = o.schema_id
		WH ERE o.object_id = @object_id;

	SEL ECT @indexname = QUOTENAME(name)
		FR OM sys.indexes
		WH ERE  object_id = @object_id AND index_id = @index_id;

	SELECT @partition_number = count (*)
		FR OM sys.partitions
		WH ERE object_id = @object_id AND index_id = @index_id;

-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
		IF @fragmentation_in_percent < 30.0 and @allow_page_locks = 'true'
			SET @command = N'ALT ER   INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
		IF @fragmentation_in_percent >= 30.0
			SET @command = N'ALT ER   INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
		IF @partition_number > 1 and @command is not null
			SET @command = @command + N' PARTITION=' + CAST(@partition_number AS nvarchar(10));

-- Выполняем команду    
		IF @command is not null
			PRINT N'Executed: ' + @command;
			EXEC (@command);
			SET @command = null;

	PRINT N'Index: object_id=' + STR(@object_id) + ', index_id=' + STR(@index_id) + ', fragmentation_in_percent=' + STR(@fragmentation_in_percent);

-- Следующий элемент цикла
	FETCH NEXT FR OM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent, @allow_page_locks;

	END;

-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;

-- Удаление временной таблицы
DR OP   TABLE #work_to_do;

GO
Показать
mc2; HIVvich; user1628996; marlbor; CunningMan; KirillZ44; yaguarrr; maikl007; +8 Ответить
59. redfred 07.12.22 12:49 Сейчас в теме
(58)
А проводить ежедневное тотальное перестроение индекса потребует повышенных временных затрат и увеличение нагрузки.


В общем-то и ежедневная реорганизация - тоже переливание из пустого в порожнее, по большому счёту. Обновления статистики, как правило, вполне достаточно
yaguarrr; +1 Ответить
62. ViDi 12.12.22 02:00 Сейчас в теме
Получил ответ от v8:

В ветке 8.3.22 изменились настройки индексов в Microsoft SQL Server.
Гранулярность блокировки индекса теперь может быть установлена только на уровне строк.
https://dl03.1c.ru/content/Platform/8_3_22_1709/1cv8upd_8_3_22_1709.htm#e3b9347f-72e1-11ec-aa69-0050569f678a
Цитата:
Система «1С:Предприятие» устанавливает гранулярность блокировок на уровне строк, если используется Microsoft SQL Server версии 2008 и последующие версии. Исключается возможность эскалации блокировок. Возможно увеличение потребления оперативной память Microsoft SQL Server.

Рекомендации учёта изменений в регламентных операциях доступны в статье "Регламентные операции на уровне СУБД для MS SQL Server" ( https://its.1c.ru/db/metod8dev#content:5837:hdoc ) в разделе "Дефрагментация индексов".
Цитата:
1. До дефрагментации индекса необходимо включить страничные блокировки. Пример команды:
ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);
2. Выполнить дефрагментацию.
3. Обратно выключить страничные блокировки. Пример команды:
ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);
agentesecreto; +1 Ответить
63. ViDi 12.12.22 02:02 Сейчас в теме
В общем, перед реорганизацией ставьте

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

после реорганизации

USE [ИмяБазы]
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO
user1628996; user800970; agentesecreto; vetgol; KwayGon; bocharovki; Igor_K_; parker_j; +8 Ответить
66. Djo82 11 13.12.22 14:12 Сейчас в теме
(63)встаю на базу, создаю запрос, останавливается с ошибкой.

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

Неправильный синтаксис около "INDEX". Если имелась в виду часть табличного указания, то для этого теперь необходимо использовать ключевое слово A WITH и круглые скобки. Правильный синтаксис см. в электронной документации по SQL Server.
Сообщение 156, уровень 15, состояние 1, строка 1
Неправильный синтаксис около ключевого слова "SET".

Что не так делаю, подскажите?
121. Barok 01.07.23 11:38 Сейчас в теме
(66)
такая же проблема. Нашли решение?
UPD. ALTER слитно :)
75. roma_ov 22.12.22 11:12 Сейчас в теме
(63)
chtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO
+3 – Ответить

подскажите пожалуйста.
это обязательно после реорганизации? или это для того чтобы меньше памяти уходило на базу?
133. medexe 20.07.23 11:52 Сейчас в теме
(63) подскажите пожалуйста, а несколько баз указывать через запятую после USE ?
64. user1884007 13.12.22 08:34 Сейчас в теме
Добрый день.

После обновления платформы с версии 8.3.20.1674 до версии 8.3.20.2184, план обслуживания sql баз 1с - реорганизация индексов стала выдавать ошибку.

Задание состоит из 2 частей:

Реорганизация индекса
Обновление статистики

Ошибки при выполнении задачи Реорганизация индексов.
Номер ошибки: -1073548784

Сведения об:
Сбой выполнения запроса "ALT ER INDEX [_AccumRg4879_1] ON [dbo].[_AccumRg487..." со следующей ошибкой: "Не удалось найти объект "dbo._AccumRg4879", так как он не существует, или отсутствуют разрешения.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.

Сбой выполнения запроса "ALT ER INDEX [_AccRg465_1] ON [dbo].[_AccRg465] REO..." со следующей ошибкой: "Невозможно реорганизовать индекс "_AccRg465_1" в таблице "_AccRg465", поскольку отключена блокировка на уровне страницы.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.

Сбой выполнения запроса "ALT ER INDEX [_AccumRgT7681_1] ON [dbo].[_AccumRgT7..." со следующей ошибкой: "Невозможно реорганизовать индекс "_AccumRgT7681_1" в таблице "_AccumRgT7681", поскольку отключена блокировка на уровне страницы.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.


Везде где обновлялась платформа. Такая проблема.
Подскажите пожалуйста как решается эта проблема или как теперь нужно настраивать план обслуживания 1с баз.
65. roma_ov 13.12.22 08:36 Сейчас в теме
Добрый день.

После обновления платформы с версии 8.3.20.1674 до версии 8.3.20.2184, план обслуживания sql баз 1с - реорганизация индексов стала выдавать ошибку.

Задание состоит из 2 частей:

Реорганизация индекса
Обновление статистики

Ошибки при выполнении задачи Реорганизация индексов.
Номер ошибки: -1073548784

Сведения об:
Сбой выполнения запроса "ALT ER INDEX [_AccumRg4879_1] ON [dbo].[_AccumRg487..." со следующей ошибкой: "Не удалось найти объект "dbo._AccumRg4879", так как он не существует, или отсутствуют разрешения.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.

Сбой выполнения запроса "ALT ER INDEX [_AccRg465_1] ON [dbo].[_AccRg465] REO..." со следующей ошибкой: "Невозможно реорганизовать индекс "_AccRg465_1" в таблице "_AccRg465", поскольку отключена блокировка на уровне страницы.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.

Сбой выполнения запроса "ALT ER INDEX [_AccumRgT7681_1] ON [dbo].[_AccumRgT7..." со следующей ошибкой: "Невозможно реорганизовать индекс "_AccumRgT7681_1" в таблице "_AccumRgT7681", поскольку отключена блокировка на уровне страницы.". Возможные причины сбоя: проблемы с этим запросом, свойство "ResultSet" установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.


Везде где обновлялась платформа. Такая проблема.
Подскажите пожалуйста как решается эта проблема или как теперь нужно настраивать план обслуживания 1с баз.
67. Djo82 11 13.12.22 14:20 Сейчас в теме
(63)встаю на базу, создаю запрос, останавливается с ошибкой.

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

Неправильный синтаксис около "INDEX". Если имелась в виду часть табличного указания, то для этого теперь необходимо использовать ключевое слово A WITH и круглые скобки. Правильный синтаксис см. в электронной документации по SQL Server.
Сообщение 156, уровень 15, состояние 1, строка 1
Неправильный синтаксис около ключевого слова "SET".

Что не так делаю, подскажите?
70. ViDi 13.12.22 17:19 Сейчас в теме
(67)Возможно, что ALTER в запросе написано не слитно
mamonth; onetone; MaZaHacKa_13; +3 Ответить
68. user1559729 13.12.22 14:44 Сейчас в теме
(67) Убрать пробел в "ALT ER"?
user1887666; mamonth; onetone; MaZaHacKa_13; bocharovki; ViDi; Djo82; parker_j; +8 Ответить
69. Djo82 11 13.12.22 14:59 Сейчас в теме
71. user1876524 18.12.22 10:38 Сейчас в теме
(58) Можете доработать скрипт уважаемого Виталия 'Tavalik' для работы с 8.3.22?
https://github.com/Tavalik/SQL_TScripts/blob/master/Обслуживание _индексов_нескольких_БД.sql
thunder367; +1 Ответить
72. zoytsa 19.12.22 07:03 Сейчас в теме
В итоге, есть объяснение логики изменения схемы работы с индексами?
Система «1С:Предприятие» устанавливает гранулярность блокировок на уровне строк, если используется Microsoft SQL Server версии 2008 и последующие версии. Исключается возможность эскалации блокировок. Возможно увеличение потребления оперативной память Microsoft SQL Server.

Для чего это?
73. redfred 19.12.22 10:59 Сейчас в теме
(72)
Для чего это?
Ну, вроде ж написано - чтоб блокировок меньше было. Меньше блокировок - меньше тормозов у пользователей
74. sssss_aaaaa_2011 19.12.22 11:21 Сейчас в теме
(72)Эскалация блокировок ведет к меньшему расходу памяти на эти блокировки, но увеличивает количество блокированных записей, в том числе и тех, в блокировании которых нет необходимости. То есть точность наложения блокировок снижается. Блокируется больше - больше тормозов.
76. roma_ov 22.12.22 11:19 Сейчас в теме
пожалуйста разверните мне, я новичок в этом.
первый этап это отключение блокировок? но при этом могут появиться жалобы на тормоза в базе и просто больше памяти будет задействовано.
но я так и не понял второй этап, возвращение блокировок! он обязательный?

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

после реорганизации

USE [ИмяБазы]
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO
77. den_vrn 13 28.12.22 14:44 Сейчас в теме
Господа я тысячикратно извиняюсь!!! А точно для всех таблиц после реорганизации надо выключать страничные блокировки?????????
SoDm; roma_ov; yaguarrr; +3 Ответить
79. yaguarrr 72 28.12.22 15:03 Сейчас в теме
(77)..., я по всем таблицам базы пробежался, ввел блокировку страниц и так оставил. Работает 1с штатно после этого+регламентные сиквела также
80. yaguarrr 72 28.12.22 15:05 Сейчас в теме
(77) на другой аналогичной базе убрал реорганизацию совсем и участил обновление статистик, также все работает в пределах нормы. Может 1с-ники и правы когда блокировки страниц убрали
81. den_vrn 13 28.12.22 15:12 Сейчас в теме
78. den_vrn 13 28.12.22 14:58 Сейчас в теме
а и нахрена каждый раз дергать блокировки строк??? 😵 (ALLOW_ROW_LOCKS= ON)
SoDm; srg123; thunder367; +3 Ответить
82. maikl007 92 30.12.22 05:32 Сейчас в теме
Пример "Дефрагментация и перестроение индексов" из программы Quick Maintenance & Backup, все отлично работает спасибо тех. поддержке.
Прикрепленные файлы:
IndexRebuild.txt
83. thunder367 03.01.23 10:42 Сейчас в теме
(82) У вас какая версия SQL сервера?

Не работает скрипт.
84. srg123 03.01.23 21:55 Сейчас в теме
(82) "Дефрагментация и перестроение индексов" из этой программы не работает (MS SQL2016). Работает только 2-й вариант скрипта от Ola Hallengren ( http://qmbsql.ru/ ).
85. maikl007 92 04.01.23 06:06 Сейчас в теме
Да действительно не работал скрипт, поправил (MS SQL2014):

Вы можете скопировать задачу «Дефрагментация и перестроение индексов» и заменить там текст скрипта, на тот что во вложении.

Затем в сценариях, выберите созданную вами задачу по дефрагментации и перестроении индексов.
Прикрепленные файлы:
IndexRebuild.txt
srg123; thunder367; +2 Ответить
86. srg123 04.01.23 13:01 Сейчас в теме
(85) Все отлично. Спасибо!
87. lends 04.01.23 16:19 Сейчас в теме
Всем привет.
Я так и не понял - в плане обслуживания оставлять реорганизацию индексов с "костылями" :

USE [Name]
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

USE [Name]
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO

Или нет ?
Забить и делать только обновление статистики .....

Кстати операция Восстановить индекс -тоже не будет работать без "костылей" теперь ?
88. technetos 04.01.23 19:32 Сейчас в теме
у меня когнитивный диссонанс. Пишут в описании выключить блокировки, а в скрипте пишут ON. Так надо?
Прикрепленные файлы:
89. technetos 04.01.23 19:36 Сейчас в теме
включить-выключить страничные.... а строки ON - ON. так и надо?
94. user1863362 10.01.23 12:39 Сейчас в теме
(89) Страница - это page. Строка - это row. Читай скрипт внимательно.
90. lends 05.01.23 00:00 Сейчас в теме
Я поражаюсь 1С - че то сделают, но как нормально поправить в скрипте SQL - не пишут.
Они там вообще офигели ?

"Важно! Начиная с версии платформы 8.3.22 необходимо выполнять дефрагментацию индексов по следующему алгоритму:

До дефрагментации индекса необходимо включить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);
Выполнить дефрагментацию.
Обратно выключить страничные блокировки. Пример команды: ALT ER INDEX index_name ON table_name SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON);"

Мне что каждую таблицу включать/выключать в скрипте ?
Нельзя было общее решение хотя бы для базы описать ?
Заставляют пользоваться написанным кем то кодом ......

1. Плохо понимаю в SQL это правильно написано ?:

USE [Name]
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

USE [Name]
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'
EXEC sp_MSforeachtable 'ALT ER INDEX ALL ON ? SET (ALLOW_ROW_LOCKS= ON)'
GO

Почему по две строки ...?

2. А Это нормально еженедельно как советуют в 1С - сключать/выключить все таблицы ...? Индексы и так реорганизуются долго еще и этот костыль теперь .....
91. yaguarrr 72 06.01.23 13:38 Сейчас в теме
(90) Гдето на просторах встречал статью, что у самой 1С, чуть ли не петабайтная база на сиквеле в которой вообще регламентные не делают, и все работает.....
93. user1863362 10.01.23 12:25 Сейчас в теме
(90)
Я поражаюсь 1С

(90)
Они там вообще офигели ?

(90)
Плохо понимаю в SQL


Вот всё очень правильно 1С делает - поднимает уровень требуемых знаний для обслуживания баз. Отсекаются необразованные, общий уровень сообщества повышается.
mamonth; Sarlee; starik-2005; yaguarrr; +4 Ответить
92. user1876524 09.01.23 21:10 Сейчас в теме
напишите уже кто-нибудь годный скрипт дефрагментации индексов для всех БД:
чтобы в начале блокировки включал (ALLOW_PAGE_LOCKS = ON) - потом дефрагментировал (например скриптом Tavalik) - под конец блокировки выключал (ALLOW_PAGE_LOCKS = OFF)
это же будет полезно всему сообществу и снимет все вопросы.
95. user1863362 10.01.23 12:47 Сейчас в теме
(92)
и снимет все вопросы

- А у меня на SQL 2000 не запускается!
- Как запустить скрипт без конфигуратора?
- Я скачал, запустил у меня все повисло я вас засужу!
- Вы офигели неработоспособное выкладывать?
...

Хе-хе. Сообщество.
96. bardaknn 10.01.23 13:06 Сейчас в теме
Вот, на тестовой базе отработало. Но у меня пока нет 22 релиза.
Проверьте кто может.

Да, обрабатывает все пользовательские базы подходящие под маску @namelike
use master
SET NOCOUNT ON;
DECLARE @namelike varchar(100) = 'Введите_маску_базы_символ%' -- Отбор по имени базы
DECLARE @database_id varchar(100) -- ID баз данных
DECLARE @database_name varchar(100) -- Имена баз данных
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо реиндексации
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmessage as NVARCHAR(4000) = '' -- текст сообщения
-- Отберем базы для выполнения операций
DECLARE DBcursor CURSOR FOR 
(
	SEL ECT 
		database_id as database_id,
		name as database_name
	FR OM sys.databases d
	WHERE 
		d.name <> 'tempdb'
		AND d.name <> 'master'
		AND d.name <> 'model'
		AND d.name <> 'msdb'
		AND d.state_desc = 'ONLINE' -- база должна быть в сети 
		AND d.name like @namelike -- база должна содержать указанное слово   
)
OPEN DBcursor
FETCH NEXT FR OM DBcursor INTO @database_id,@database_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT N'----------------------------------------------------------'
	PRINT N'USE [' + @database_name + N']'
	SET @command = 
	N'USE [' + @database_name + N']
	DECLARE @object_id int; -- ID объекта
	DECLARE @index_id int; -- ID индекса
	DECLARE @partition_number bigint; -- количество секций если индекс секционирован
	DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
	DECLARE @objectname nvarchar(130); -- имя таблицы 
	DECLARE @indexname nvarchar(130); -- имя индекса
	DECLARE @partitionnum bigint; -- номер секции
	DECLARE @fragmentation_in_percent float; -- процент фрагментации индекса
	DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
	-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
	-- Отбор только тех объектов которые:
	--	 являются индексами (index_id > 0)
	--   фрагментация которых более 5% 
	--   количество страниц в индексе более 50 
	SEL ECT
		object_id,
		index_id,
		partition_number,
		avg_fragmentation_in_percent AS fragmentation_in_percent
	INTO #work_to_do
	FR OM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
	WHERE index_id > 0 
		AND avg_fragmentation_in_percent > 5.0
		AND page_count > 50;
	-- Объявление Открытие курсора курсора для чтения секций
	DECLARE partitions CURSOR FOR SEL ECT * FR OM #work_to_do;
	OPEN partitions;
	-- Цикл по секциям
	FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;
	WHILE @@FETCH_STATUS = 0
		BEGIN
	
			-- Собираем имена объектов по ID		
			SEL ECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
			FROM sys.objects AS o
				JOIN sys.schemas as s ON s.schema_id = o.schema_id
			WH ERE o.object_id = @object_id;
        
			SEL ECT @indexname = QUOTENAME(name)
			FR OM sys.indexes
			WH ERE object_id = @object_id AND index_id = @index_id;
        
			SELECT @partition_number = count (*)
			FR OM sys.partitions
			WH ERE object_id = @object_id AND index_id = @index_id;
			-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
			IF @fragmentation_in_percent < 30.0
				SET @command = N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);'';
				SET @command = @command + N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE;'';
				SET @command = @command + N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' SET (ALLOW_PAGE_LOCKS = OFF , ALLOW_ROW_LOCKS = ON);'';
			IF @fragmentation_in_percent >= 30.0
				SET @command = N''ALT ER   INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
			IF @partition_number > 1
				SET @command = @command + N'' PARTITION='' + CAST(@partition_number AS nvarchar(10));
			
			-- Выполняем команду				
			PRINT N''    Executed: '' + @command;
			EXEC sp_executesql @command			
		
			-- Следующий элемент цикла
			FETCH NEXT FR OM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;
		END;
	-- Закрытие курсора
	CLOSE partitions;
	DEALLOCATE partitions;
	-- Удаление временной таблицы
	DR OP   TABLE #work_to_do;';
	BEGIN TRY
		EXEC sp_executesql @command	
		SET @finalmessage = @finalmessage + 'Успешное выполнение операций обслуживания индексов для базы данных ' + @database_name + CHAR(13) + CHAR(13)
	END TRY
	BEGIN CATCH  
		-- Ошбика выполнения операции
		SET @subject = 'БЫЛИ ОШИБКИ при выполнении операций обслуживания индексов '
		SET @finalmessage = @finalmessage + 'ОШИБКА обслуживания индекса для базы данных ' + @database_name + CHAR(13) + CHAR(13)
			+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
			+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
			+ 'Текст T-SQL: ' + CHAR(13) + @command + CHAR(13) + CHAR(13)  
	END CATCH;
	
	-- Следующая база данных
	FETCH NEXT FR OM DBcursor INTO @database_id, @database_name
	END;

CLOSE DBcursor;
DEALLOCATE DBcursor;
SELECT
	@subject as subject, 
	@finalmessage as finalmessage 

GO
Показать

(92)
LineykaSBK; +1 Ответить
97. user1863362 10.01.23 15:11 Сейчас в теме
(96)
Проверьте кто может.
Не будет работать для баз в состояниях READONLY и SINGLE_USER. Для баз, которые реплицируются и являются дистрибьютерами тоже скорей всего.
98. bardaknn 10.01.23 21:32 Сейчас в теме
(97)
Не будет работать для баз в состояниях READONLY и SINGLE_USER. Для баз, которые реплицируются и являются дистрибьютерами тоже скорей всего.


А ставилась задача что бы работало для этих состояний?)

(92)
напишите уже кто-нибудь годный скрипт дефрагментации индексов для всех БД:
чтобы в начале блокировки включал (ALLOW_PAGE_LOCKS = ON) - потом дефрагментировал (например скриптом Tavalik) - под конец блокировки выключал (ALLOW_PAGE_LOCKS = OFF)
это же будет полезно всему сообществу и снимет все вопросы.


Решалась эта задача :-)

(95)
Хе-хе. Сообщество.