Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Индекс не создаётся в нужной ФГ  [new]
Zulus
Member

Откуда: Замкадье
Сообщений: 490
Коллеги, подскажите что не так под вечер
нужно переместить индекс в другую ФГ

вот запрос №1
select
  object_name(object_id) as [obj_name]
, ds.[name] as [filegroup_name]
, p.[object_id]
, p.index_id
from
sys.data_spaces ds
inner join sys.database_files df on df.data_space_id = ds.data_space_id
inner join sys.allocation_units au on ds.data_space_id = au.data_space_id
inner join sys.partitions p on au.container_id = 
case when au.type = 2 then p.partition_id else p.hobt_id end
where df.[name] = 'HISTORY2'


вот результат (одинокий кластерный индекс в нужной ФГ):
obj_name filegroup_name object_id index_id
tCardsEventsHistory DataFGHYSTORY2 854371650 1

удаляем ПК
alter table [dbo].[tCardsEventsHistory] drop constraint [ItCardsEvent_CardEventIdHistory]

опять запрос №1
ПК помер, куча в наличии
obj_name filegroup_name object_id index_id
tCardsEventsHistory DataFGHYSTORY2 854371650 0

двигаем ПК
alter table [dbo].[tCardsEventsHistory] add constraint [ItCardsEvent_CardEventIdHistory_qwerty] primary key clustered ([CardEventId] asc )
on [PRIMARY];

опять запрос №1
obj_name filegroup_name object_id index_id
tCardsEventsHistory DataFGHYSTORY2 854371650 1

Индекс создан, но не в явно указанной PRIMARY, а опять там, где и был.
Уже чего только не делал - вместо ПК создавал кластерный где надо, проверял по-всякому.
Но воз и ныне там.

ФГ по-умолчанию - PRIMARY, файл HISTORY2 не принадлежит ФГ PRIMARY, куда ещё смотреть - неясно
24 сен 18, 20:17    [21684596]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Zulus,

Я бы посоветал сделать явно кластерный индекс CardEventId без primary key по нужному столбцу в файловой группе PRIMARY, строки таблицы физически переедут при этом в PRIMARY, потом его удалить, потом добавить constraint по тому же столбцу.
24 сен 18, 20:20    [21684601]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
aleksrov
Member

Откуда:
Сообщений: 948
Andy_OLAP,

вы как всегда бред пишете

Автору - используйте DROP_EXISTING=ON для класт. индекса.
24 сен 18, 20:30    [21684616]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
aleksrov
Andy_OLAP,

вы как всегда бред пишете

Автору - используйте DROP_EXISTING=ON для класт. индекса.

Вы явно не прочитали "ПК помер, куча в наличии".
24 сен 18, 20:37    [21684626]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
invm
Member

Откуда: Москва
Сообщений: 9270
Zulus,

Чересчур мудрено и не с той стороны заходите.

select
 ds.name
from
 sys.partitions p join
 sys.allocation_units au on au.type in (1, 3) and au.container_id = p.hobt_id join
 sys.data_spaces ds on ds.data_space_id = au.data_space_id
where
 p.object_id = object_id('[dbo].[tCardsEventsHistory]') and
 p.index_id = 1;
24 сен 18, 20:42    [21684635]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Zulus
Member

Откуда: Замкадье
Сообщений: 490
Andy_OLAP
Zulus,

Я бы посоветовал сделать явно кластерный индекс CardEventId без primary key по нужному столбцу в файловой группе PRIMARY, строки таблицы физически переедут при этом в PRIMARY, потом его удалить, потом добавить constraint по тому же столбцу.

Andy, спасибо, но так я уже пробовал
Zulus
Уже чего только не делал - вместо ПК создавал кластерный где надо, проверял по-всякому.

как ни странно, после create clustered index индекс оказывается как принадлежащий не PRIMARY, хоть явно указано, а DataFGHYSTORY2.

Я понимаю, что чудес не бывает и выглядит со стороны неправдоподобно, но вот такая засада.
24 сен 18, 21:51    [21684709]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
invm
Member

Откуда: Москва
Сообщений: 9270
Zulus
после create clustered index индекс оказывается как принадлежащий не PRIMARY, хоть явно указано, а DataFGHYSTORY2.
Database trigger гадит?
24 сен 18, 22:19    [21684724]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Zulus
Member

Откуда: Замкадье
Сообщений: 490
invm
Zulus,

Чересчур мудрено и не с той стороны заходите.

select
 ds.name
from
 sys.partitions p join
 sys.allocation_units au on au.type in (1, 3) and au.container_id = p.hobt_id join
 sys.data_spaces ds on ds.data_space_id = au.data_space_id
where
 p.object_id = object_id('[dbo].[tCardsEventsHistory]') and
 p.index_id = 1;


Ну, Вы правы, конечно, по-своему, но мне изначально не особо было нужно знать, в каком файле размещён индекс =1 некоторой таблицы.
Есть ФГ с файлом и эту пару надо удалить.
Я посмотрел что там есть, перенёс содержимое в другую ФГ и попытался выполнить dbcc shrinkfile.
В ответ получил
Cannot move all contents of file "HISTORY2" to other places to complete the emptyfile operation.
Опять посмотрел, что мешает (размещается в файла данных) - и вижу индекс, который я шагом ранее перенёс.

Сейчас такой запрос
select
  object_id(p.[object_id])
, p.index_id
from
sys.data_spaces ds
left join sys.database_files df on df.data_space_id = ds.data_space_id
left join sys.allocation_units au on ds.data_space_id = au.data_space_id
left join sys.partitions p on au.type in (1, 3) and au.container_id = p.hobt_id
where
df.[name] = 'HISTORY2'

возвращает вот что:
(No column name) index_id
NULL NULL

что я интерпретирую как то, что в ФГ HISTORY2 ничего нет.
При попытке выполнить шринк
dbcc shrinkfile(N'HISTORY2', emptyfile);

вылетает ошибка
DBCC SHRINKFILE: Index Allocation Map (IAM) page 11:155 could not be moved.
Msg 2555, Level 16, State 1, Line 194
Cannot move all contents of file "HISTORY2" to other places to complete the emptyfile operation.

Упражняюсь на восстановленном бэкапе БД заказчика, и, начинаю подозревать, что бэкап битый.
24 сен 18, 22:25    [21684733]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
Zulus
Опять посмотрел, что мешает (размещается в файла данных) - и вижу индекс, который я шагом ранее перенёс.
Упражняюсь на восстановленном бэкапе БД заказчика, и, начинаю подозревать, что бэкап битый.

Есть еще предположение.

Что команды по переделке индексов Вы выполняете на развернутом бэкапе, а команды, которые сверяют результат, выполняете в другом окне SSMS на соединении с сервером, где работает исходная база заказчика.

Я бы рекомендовал Вам исходя из своего опыта не доверять своим глазам, а закрыть все окна и студию SSMS, явно открыть соединение к развернутой из бэкапа БД и еще раз последовательно в одном окне выполнять команду за командой, комментирую выполненные ранее шаги.

Очень часто нужно не доверять себе, а как бы начать с чистого листа. Невнимательность от усталости настигает в любом возрасте и на любой, казалось бы доведенной до автоматизма, операции или последовательности шагов.
25 сен 18, 00:05    [21684792]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
Вы вот этот фильтр уберите для начала, может быть у вас там LOB_DATA?

select
    object_id(p.object_id)
  , p.index_id
  , au.type_desc
from sys.data_spaces as ds
    left join sys.database_files as df on df.data_space_id = ds.data_space_id
    left join sys.allocation_units as au on ds.data_space_id = au.data_space_id
    left join sys.partitions as p on au.container_id = p.hobt_id
                                     --and au.type in ( 1, 3 )
where df.name = 'HISTORY2';


При ребилде индекса на другую файловую группу LOB_DATA не переносится. Чтобы это обойти нужно ребилдить кластерный индекс на схему секционирования сначала, а потом просто на файловую группу. При ребилде на схему секционированния LOB_DATA будет перенесен.
25 сен 18, 06:43    [21684889]     Ответить | Цитировать Сообщить модератору
 Re: Индекс не создаётся в нужной ФГ  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Zulus,
если я ничего не пропустил, то
автор
You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
25 сен 18, 08:44    [21684942]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить