Filegroup Hetrllprce1 run out of space

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Filegroup Hetrllprce1 run out of space

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2014-01-11 : 03:09:59

Good morning
Need your help pls
There is a Production Process that inserts rows form a temp table to a table HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401 and 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)
GO
CREATE CLUSTERED INDEX [INDICENFG] ON [dbo].[HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401]
(
[INDICENFG] ASC
) ON [Hetrllprce1]
GO

DROP INDEX INDICENFG ON HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_201401
GO
ALTER TABLE HECHOS_TRAFICO_LLAMADAS_PREPAGO_CELULAR_2014 DROP COLUMN INDICENFG
GO

i run the above script and took about 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 because the above script moved the table but not its indexes

these 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 grows ?

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 ? is tehre any script ?

4. The above script moves only the table but not its nonclusterd indexes, so is the a general script that moves those objects table and indexes ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-11 : 04:57:30

1. generally its good to keep data for objects in one fg and indexes in another fg
1.1 Non clustered will grow when table grows but not necessarily at the same pace.
2. Nope. better to keep them in separate FGs
3. As per 2 no need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources