Posting Yak Master
Posted - 01/09/2014 : 04:38:52
| Good morning
Need your help pls
There is a Production table that insert rows form a temp table to a table HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401and failed because FG Hetrllprce1 run out of space
i asked our DBA to move this object from a FG Hetrllprce2 to another FG Hetrllprce1 that is greater,so he sent me a script to be run like this:
ALTER TABLE dbo.HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401
ADD INDICENFG DECIMAL(10,0)
CREATE CLUSTERED INDEX [INDICENFG] ON [dbo].[HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401]
) ON [Hetrllprce1]
DROP INDEX INDICENFG ON HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401
ALTER TABLE HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_2014 DROP COLUMN INDICENFG
i run the above script and took abot 8 hours OK
But i didnt mention to our DBA and dindt take into account that i also wanted to move its non clustered index to the FG Hetrllprce1 but they are still located in FG Hetrllprce2 besause the above script moved the table but not its indexex
these there are some of them:
IND_HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401_ANO_MES nonclustered located on Hetrllprce2 ANO_MES
IND_HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401_COD_ABONADO_IN nonclustered located on Hetrllprce2 COD_ABONADO_IN
IND_HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401_COD_ABONADO_OUT nonclustered located on Hetrllprce2 COD_ABONADO_OUT
Im not a DBA i lack some knowledge so i have some questions:
1.Is there any impact (Performance or something like that) having a table in one FG and having its indexes in another FG ? 1.1 These nonclustered indexes will to grow everytime the table greowing ?
2.If so its conveniennt to move its indexes to the FG where table is also located ?
3. For this specific case how do i move these nonclusterd indexes located in FG Hetrllprce2 to the FG Hetrllprce1 ?
4. The above script moves only the table but not its nonclusterd indexes, so is the a general script that moves those objects table iand indexes ?
I appreciate your help