Filegrous Primary and user FG 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 2000 Forums

SQL Server Administration (2000)

Filegrous Primary and user FG run out of space

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-10 : 17:38:48

Good afternnon, need your help pls, In Production System there are 2 FGs that run out od space, one of hem was Primary 0% availabe and user Filegroup Called Indices was 1% available.

So we scheduled a 3 day manteinance windows in order to free up some space to both filegroups becaause our DBA told us there are no disks and phisical space to add.

So we stop the Production and started the windows by deleting in batch then 3 days in a row,and afterward surprisngly and sadly we run sp_utilfileinfo we got the same available space 0% and 1%1, and it was kind of disapointing.

This the batch delete:

set rowcount 1000000 -- Increasing everytime we could
WHILE 1=1
BEGIN
delete from HECHOS_MOVTO_PREPAGO_ACTUACIONES
where HOR_PROCESO >= '20120101' and HOR_PROCESO <= '20120229'
IF @@ROWCOUNT < 80000 BREAK
end
SET ROWCOUNT 0

we tried to run in parallel another delete in batch b
ut changing dates 20120301 - 20130331 but it was blocked for the above delete.
Ive heard you can delete in parallel with no lock option but is not longer used because indexes could be damaged ?

These are the indexes of the table:
Data_located_on_filegroup
PRIMARY

index_name|index_description|index_keys
IX_HECHOS_MOVTO_PREPAGO_ACTUACIONES_COD_ABONADO|nonclustered located on PRIMARY|COD_ABONADO
IX_HECHOS_MOVTO_PREPAGO_ACTUACIONES_HOR_PROCESO|nonclustered located on PRIMARY|HOR_PROCESO
IX_HECHOS_MOVTO_PREPAGO_ACTUACIONES_NUM_CELULAR|nonclustered located on PRIMARY|NUM_CELULAR

constraint_type|constraint_name|delete_action|update_action|status_enabled|status_for_replication|constraint_keys
DEFAULT on column FEC_ACTUALIZACION|DF_HECHOS_MOVTO_PREPAGO_ACTUACIONES_FEC_ACTUALIZACION_1|(n/a)|(n/a)|(n/a)|(n/a)|(getdate())

Id appreciate your help to get around this becuase it is blowing my mind

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-10 : 17:44:08

You probably don't have enough space to run ALTER INDEX REBUILD on the indexes, so instead DROP the indexes and then CREATE them again.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-10 : 17:44:41

How big are these filegroups anyway?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-11 : 15:54:11

First i thank you for you support, and about your question this is the current space of both FGS: Those numbers are given in Gigabites?

groupname|Free|total
PRIMARY|0|881369
INDICES|1|1058727
The Table has about 900 millions of rows

And according waht you said Ive got a couple of questions questions, Im not a DBA and i want to make sre if i understood corectly:

1.The deletion is too slow because the lack of manteinance like index fragmentations or something like that and droping and creating indexes on the table improve performance fotr the deletion? i check out the deletion and in an average it took 5 hours to delete only 4 millios of rows

2.you probably don't have enough space to run ALTER INDEX REBUILD on the indexes, so instead DROP the indexes and then CREATE them again.2
Me: because disks run out of space i can't run ALTER INDEX REBUILD on the indexes by using DBCC DBREINDEX in sql server 2000 ?

3 so instead DROP the indexes and then CREATE them again
me:So i run the simple T-SQL staments for dropping and creating indexes for instance:

DROP INDEX table.t1_id_index
and CREATE [ INDEX index_name.. -- with its options

if so we need a manteinance window to DROP the indexes and then CREATE them again. So how much longer is gonna take this mptocess taking into account is sqlserver 2000 and this table ahas about 900 millions of rows ?

Thanks you ance again and i appreciate your help in advanced

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-13 : 22:08:05

is there any answer in these above questions ?

I'd appreciate your help in advanced

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-14 : 08:33:14

1. recreating the indices will definitely improce performance
2. yes
3. yes

How much longer? Impossible to know at this point. However, what other choices do you have? This is production, right? The system needs to function, right? You have to do what you have to do.

FWIW, I'd escalate to the DBA's manager to say, "Get us more space! Now!!!"

alejo46
Posting Yak Master

157 Posts

Posted - 2014-12-15 : 19:10:08

OK Thanks a lot for your support and your answers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources