I'm using SQL 2005, sp2.

I have inherited a table that has aprox 300 million records, and is very
wide. We started running out of disk space, so I started deleting data from
the table. I've noticed that the database doesn't seem to reuse the space
that is free'd up from the purge (there is 15 gig of unallocated space, and
the database tried to grow last night). The table doesn't have a clustered
index, but does have 4 non-clustered indexes.

Is the reason it doesn't seem to be reusing the space because it doesn't
have a clustered index? Does anyone have any insight into this?

Thanks.

Hello Jason

Thank you for contacting Microsoft Online Community Support. It is Mark, a
SQL Server Engineer. I'm glad to assist you with the issue.

For this case, you indicated after deleting data from the table with 300
million records, the unallocated sapce increase; however the size of the
database still try to be bigger. Is it correct? If I misunderstand
anything, please tell me directly. It will us to resolve this issue
quickly. Once the issue resolved, I'll appreciate your verification.

in order to resolve the issue, I would like to explain the following
1) when you delete the data from the talbe, the unallocated sapce of data
file will increase.
2) when you take the delete operation, the size of the log file will
increase.
3) since the transaction will not be recorded into data file, the size of
the database will increase when a lot of delete operations taken.

To confirm what I suspect, please run the following script and send the
result to me.
sp_db 'database name'

use database name
go
exec sp_spaceused @updateusage='true'
go

dbcc sqlperf('logspace')

If anything is unclear or you need me explain further, please tell me.

I look forward to your update.

Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Jason,

In addition to what Mark stated you most likely have a lot of free space on
the pages due to the deletes. Since you don't have a clustered index the
only way to remove that kind of fragmentation is to export al the data,
truncate the table and reimport it back in. It would be easier to create a
proper clustered index but if you already lack space this in itself will be
a problem since it requires about 1.2x the size of the table in free space
to build the index. You can also try rebuilding some of the non-clustered
indexes to remove any fragmentation there to get a little more space. But
the bottom line is you need plenty of free space in any db that is not
strictly read-only so I suggest you look for more disk space as well.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Jason" <Jason@discussions.microsoft.com> wrote in message
news:988F486A-AC69-4651-9B60-6E0080B5D898@microsoft.com...
> I'm using SQL 2005, sp2.
>
> I have inherited a table that has aprox 300 million records, and is very
> wide. We started running out of disk space, so I started deleting data
> from
> the table. I've noticed that the database doesn't seem to reuse the space
> that is free'd up from the purge (there is 15 gig of unallocated space,
> and
> the database tried to grow last night). The table doesn't have a
> clustered
> index, but does have 4 non-clustered indexes.
>
> Is the reason it doesn't seem to be reusing the space because it doesn't
> have a clustered index? Does anyone have any insight into this?
>
> Thanks.

Hi Jason,

What is this issue going on?

If there is any issue, please feel free to post back. We are very glad for
further assistance.

Have a good day!

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Jasion,

This is Mark. I'm writing to follow this issue.

Is anything I can assist you with the techonical issue?

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======