SQL2000-How to reclaim the Unallocate Space for Database(low hdd space - Urgent!!!)

[lbseong]

Good day,
I felt this forum is good because the reply is fast and relevent. so, this is my second question...
I am working on SQL2000, one of my db is use up around 250gb, and the hard disk is running out of space soon...(very soon).
I have run the sp_spacesused and found that the unallocate db is

database_name database_size unallocated space ----
Internet 113693.50 MB 43466.96MB -


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
71808552 KB 70249352 KB 957440 KB 601760 KB


I have clean up the log file, and also run the SQL EM shrink database, but no help...I would like to reclaim the space that allocated in the server (43466.96MB ) , how can I do that....i have try the dbcc shrink file from EM , and start with 10 MB , but it take more than 2 hours, but again no luck...the result still the same...can anyone please me ? my hard disk is running out of space.......very very soon.
thanks

[ck9663]

Have you tried shrinking the FILEGROUP of your database?

-- CK

[iburyak]

To free up space is tedious job and takes days sometimes to do it and in many cases need space. So I am not mistaken you need space to free up space.

1. I will get back to you in couple of hours with a script that will help you identifying largest tables on which you should work first.
2.It takes a lot of time because you don't have space and as soon you'll free up some it will get easier.
3. For now you can just do manually.
sp_spaceused table_name
on tables that you have heaviest updates or just feel that they are a problem.
4.If you'll see that table has a big number on unallocated space you should start working on such table.

So what could you do on such table?
Make sure you work at off hours it will affect your users.
a. Create clustered index on any column and then drop it. (if clustered index exists this step will not help) What this index does, is re oranges data because data is a last level of this index. It will basically defrag data itself and free up space to the server. But unfortunately while this happening space is needed to move data around while index built so choose first tables carefully.
Use sp_spaceused table_name to see result.
b.Defrag indexes. There is such a command.
If you don't know how there is an easier way to do the same is to drop and recreate indexes. It will also free up empty pockets on index pages.
Again use sp_spaceused table_name to see result.
I usually save what was before and after to see result.
Also you can view final result on whole database by executing:
sp_spaceused
c.Before using step a or b you might think of some tables that have old data that could be deleted or offloaded somewhere else.
5. You probably need reorg job to be running on schedule for a future. You should consult your DBA for info on this.

Good Luck.

[copper21]

I'd recommend dbcc shrinkfile in 500mb chunks, to run more efficiently

[copper21]

To see exact space used in the current database (sp_spaceused is not always accurate unless dbcc updateusage is run), you could use this :
select cast(size/128.0 as decimal(10,1)) as [size mb], cast(FILEPROPERTY(f.name, 'spaceused')/128.0 as decimal(10,1)) as [used mb], maxsize [maxsize mb]
, cast(growth*8/1024 as varchar(11))+case when f.status&0x100000>0 then '%' else '' end [growth]
, isnull(filegroup_name(f.groupid),'log') [groupname]
, cast(100*(size/128-(FILEPROPERTY(f.name,'spaceused')/128))/(size/128.0) as int) [% free]
from dbo.sysfiles f order by f.fileid

[iburyak]

To see exact space used in the current database (sp_spaceused is not always accurate unless dbcc updateusage is run), you could use this :
select cast(size/128.0 as decimal(10,1)) as [size mb], cast(FILEPROPERTY(f.name, 'spaceused')/128.0 as decimal(10,1)) as [used mb], maxsize [maxsize mb]
, cast(growth*8/1024 as varchar(11))+case when f.status&0x100000>0 then '%' else '' end [growth]
, isnull(filegroup_name(f.groupid),'log') [groupname]
, cast(100*(size/128-(FILEPROPERTY(f.name,'spaceused')/128))/(size/128.0) as int) [% free]
from dbo.sysfiles f order by f.fileid

This is great I love this approach.
I’ll use it in a future for sure.

Thanks.

[lbseong]

Thanks to you guy for giving me help ...

This is the result base on (select cast(size/128.0 as decimal(10,1)) as [size mb], cast(FILEPROPERTY(f.name, 'spaceused')/128.0 as decimal(10,1)) as [used mb], maxsize [maxsize mb]
, cast(growth*8/1024 as varchar(11))+case when f.status&0x100000>0 then '%' else '' end [growth]
, isnull(filegroup_name(f.groupid),'log') [groupname]
, cast(100*(size/128-(FILEPROPERTY(f.name,'spaceused')/128))/(size/128.0) as int) [% free]
from dbo.sysfiles f order by f.fileid)

113583.0 76608.6 -1 200 PRIMARY 32
301.0 16.5 -1 100 log 94

But againt, I also run the DBCC Shink File cmd, no luck....shirnking 3 MB of data, it take more than 1 hour and slow down the server performance, until I have to kill the process...and get scolded by my manager for decrease the server performance....when I use sp_spaceused

Content 4120526 26925048 25831184 836760 257104
How can I free up the 35gb from the database ?

[lbseong]

Hello,
Thanks for your advise, can I just check with u, my database is for internet access and not allow to be down, I guess maybe the dbccindexdefrag will help a little bit, dbcc reindex will not allow as the db is 24 hours accessible...do you have any other good suggestion? maybe like shinking individual table command...fyi, the shrinking for 3 -5 mb have taken like 1 hour + and not completed...ai...

To free up space is tedious job and takes days sometimes to do it and in many cases need space. So I am not mistaken you need space to free up space.

1. I will get back to you in couple of hours with a script that will help you identifying largest tables on which you should work first.
2.It takes a lot of time because you don't have space and as soon you'll free up some it will get easier.
3. For now you can just do manually.
sp_spaceused table_name
on tables that you have heaviest updates or just feel that they are a problem.
4.If you'll see that table has a big number on unallocated space you should start working on such table.

So what could you do on such table?
Make sure you work at off hours it will affect your users.
a. Create clustered index on any column and then drop it. (if clustered index exists this step will not help) What this index does, is re oranges data because data is a last level of this index. It will basically defrag data itself and free up space to the server. But unfortunately while this happening space is needed to move data around while index built so choose first tables carefully.
Use sp_spaceused table_name to see result.
b.Defrag indexes. There is such a command.
If you don't know how there is an easier way to do the same is to drop and recreate indexes. It will also free up empty pockets on index pages.
Again use sp_spaceused table_name to see result.
I usually save what was before and after to see result.
Also you can view final result on whole database by executing:
sp_spaceused
c.Before using step a or b you might think of some tables that have old data that could be deleted or offloaded somewhere else.
5. You probably need reorg job to be running on schedule for a future. You should consult your DBA for info on this.

Good Luck.

[iburyak]

Hello,
Thanks for your advise, can I just check with u, my database is for internet access and not allow to be down, I guess maybe the dbccindexdefrag will help a little bit, dbcc reindex will not allow as the db is 24 hours accessible...do you have any other good suggestion? maybe like shinking individual table command...fyi, the shrinking for 3 -5 mb have taken like 1 hour + and not completed...ai...

See if you can shrink tran log and temdb.
When you'll free up some space your other commands will run faster.

Good Luck.