How to get free space

Hi,
SQL2K, Win 2k.
I am trying to acquire space by deleting older records from a table.
When I ran sp_spaceused on a Table A, I got following output
Table A
rows: 145030470
reserved: 139653704 KB
data: 81832616 KB = 78GB
index_size: 23655616 KB = 22.5
unused: 34165472 KB
Afte that I had deleted around 60 million records based on certain criteria.
I thought I will get atleast 30GB free space. But when I checked in EM > task
pad, I only see around 14GB free space.
Is there anyway to get free space from the pages?
Thanks in advance
Chinna.See BOL... DBCC Shrinkfile
"Chinna Kondaveeti" wrote:
> Hi,
> SQL2K, Win 2k.
> I am trying to acquire space by deleting older records from a table.
> When I ran sp_spaceused on a Table A, I got following output
> Table A
> rows: 145030470
> reserved: 139653704 KB
> data: 81832616 KB = 78GB
> index_size: 23655616 KB = 22.5
> unused: 34165472 KB
> Afte that I had deleted around 60 million records based on certain criteria.
> I thought I will get atleast 30GB free space. But when I checked in EM > task
> pad, I only see around 14GB free space.
> Is there anyway to get free space from the pages?
> Thanks in advance
> Chinna.
>|||Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space. What
I am trying to say is, based on the size estimation, there should have been
more than 14GB free space. But I don't see that in the task pad view. Is
there any way to reorganize the pages/db files to get the remaining space?
Thanks in advance
Chinna.
"Wex" wrote:
> See BOL... DBCC Shrinkfile
> "Chinna Kondaveeti" wrote:
> > Hi,
> > SQL2K, Win 2k.
> > I am trying to acquire space by deleting older records from a table.
> > When I ran sp_spaceused on a Table A, I got following output
> >
> > Table A
> > rows: 145030470
> > reserved: 139653704 KB
> > data: 81832616 KB = 78GB
> > index_size: 23655616 KB = 22.5
> > unused: 34165472 KB
> >
> > Afte that I had deleted around 60 million records based on certain criteria.
> > I thought I will get atleast 30GB free space. But when I checked in EM > task
> > pad, I only see around 14GB free space.
> >
> > Is there anyway to get free space from the pages?
> >
> > Thanks in advance
> > Chinna.
> >
> >|||Does the table have a Clustered Index? If so use DBCC SHOWCONTIG to see the
level of fragmentation. You can use DBCC INDEXDEFRAG to defragment the
index. Watch out for performance drop of while defraging
"Chinna Kondaveeti" wrote:
> Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space. What
> I am trying to say is, based on the size estimation, there should have been
> more than 14GB free space. But I don't see that in the task pad view. Is
> there any way to reorganize the pages/db files to get the remaining space?
> Thanks in advance
> Chinna.
> "Wex" wrote:
> > See BOL... DBCC Shrinkfile
> >
> > "Chinna Kondaveeti" wrote:
> >
> > > Hi,
> > > SQL2K, Win 2k.
> > > I am trying to acquire space by deleting older records from a table.
> > > When I ran sp_spaceused on a Table A, I got following output
> > >
> > > Table A
> > > rows: 145030470
> > > reserved: 139653704 KB
> > > data: 81832616 KB = 78GB
> > > index_size: 23655616 KB = 22.5
> > > unused: 34165472 KB
> > >
> > > Afte that I had deleted around 60 million records based on certain criteria.
> > > I thought I will get atleast 30GB free space. But when I checked in EM > task
> > > pad, I only see around 14GB free space.
> > >
> > > Is there anyway to get free space from the pages?
> > >
> > > Thanks in advance
> > > Chinna.
> > >
> > >|||Chinna,
Do you have text or image column? If you do then you will have issues
reclaming all the space.
Did you use truncate option or noturncate option. There is a difference in
the way these work.
"Chinna Kondaveeti" <ChinnaKondaveeti@.discussions.microsoft.com> wrote in
message news:4DA0D6E0-E353-4751-A485-817CBD4A53E7@.microsoft.com...
> Thanks for the reply. I ran dbcc shrinkfile to get that 14GB free space.
What
> I am trying to say is, based on the size estimation, there should have
been
> more than 14GB free space. But I don't see that in the task pad view. Is
> there any way to reorganize the pages/db files to get the remaining space?
> Thanks in advance
> Chinna.
> "Wex" wrote:
> > See BOL... DBCC Shrinkfile
> >
> > "Chinna Kondaveeti" wrote:
> >
> > > Hi,
> > > SQL2K, Win 2k.
> > > I am trying to acquire space by deleting older records from a table.
> > > When I ran sp_spaceused on a Table A, I got following output
> > >
> > > Table A
> > > rows: 145030470
> > > reserved: 139653704 KB
> > > data: 81832616 KB = 78GB
> > > index_size: 23655616 KB = 22.5
> > > unused: 34165472 KB
> > >
> > > Afte that I had deleted around 60 million records based on certain
criteria.
> > > I thought I will get atleast 30GB free space. But when I checked in EM
> task
> > > pad, I only see around 14GB free space.
> > >
> > > Is there anyway to get free space from the pages?
> > >
> > > Thanks in advance
> > > Chinna.
> > >
> > >