Excessive Free Space in Large Table

Hello all,
I'm having the following space allocation issue with a large table in a SQL
Server 2000 DB.
I have a table which stores visits to a site, currently with 95 million
rows. This table currently has a total of 60GB space: 24GB used space, 4GB
index space, and 32 GB unused space. The server has been running out of
space recently, and I would like to reclaim alot of the unused space from
this table. The table definition is as follows:
visitor_id int 4
visitor_settings varchar 7000
short_settings tinyint 1
visit_count int 4
created_reason tinyint 1
modified_by int 4
modified_date datetime 8
created_by int 4
created_date datetime 8
GUID_for_clustering uniqueidentifier 16
There are 2 indexes, a PK index on visitor_id, and a clustered index on
GUID_for_clustering, both with a 50% fill factor. Shrinking the table helps
temporarily, but the unused space climbs back up to 30GB after reindexing
the table. Any thoughts on what is causing the allocation of all the extra
free space, and how I can reduce it? My initial thoughts relate to
adjusting the fill factor, or possibly modifying the visitor_settings
varchar 7000 field (perhaps 'text' would actually reduce the space
allocation?). Any thoughts would be much appreciated.
Thanks!
-MarkIf all that space gets consume after reindexing then its
the transaction log. Read up on truncate/ shrink TLog.
>--Original Message--
>Hello all,
>I'm having the following space allocation issue with a
large table in a SQL
>Server 2000 DB.
>I have a table which stores visits to a site, currently
with 95 million
>rows. This table currently has a total of 60GB space:
24GB used space, 4GB
>index space, and 32 GB unused space. The server has been
running out of
>space recently, and I would like to reclaim alot of the
unused space from
>this table. The table definition is as follows:
>visitor_id int 4
>visitor_settings varchar 7000
>short_settings tinyint 1
>visit_count int 4
>created_reason tinyint 1
>modified_by int 4
>modified_date datetime 8
>created_by int 4
>created_date datetime 8
>GUID_for_clustering uniqueidentifier 16
>There are 2 indexes, a PK index on visitor_id, and a
clustered index on
>GUID_for_clustering, both with a 50% fill factor.
Shrinking the table helps
>temporarily, but the unused space climbs back up to 30GB
after reindexing
>the table. Any thoughts on what is causing the
allocation of all the extra
>free space, and how I can reduce it? My initial thoughts
relate to
>adjusting the fill factor, or possibly modifying the
visitor_settings
>varchar 7000 field (perhaps 'text' would actually reduce
the space
>allocation?). Any thoughts would be much appreciated.
>Thanks!
>-Mark
>
>.
>|||Because of space concerns, this database has no transaction log (it is
non-critical data, and is backed-up daily).
I can only think the extra space is being allocated for the visitor_settings
varchar 7000 on each row after reindexing, even though the average length is
probably only around 3000. Because it's a varchar type, the space would not
actually be used, but perhaps is still allocated and thus, unavailable?
-Mark
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:725101c3e793$8abb8620$a101280a@.phx.gbl...
> If all that space gets consume after reindexing then its
> the transaction log. Read up on truncate/ shrink TLog.
> >--Original Message--
> >Hello all,
> >
> >I'm having the following space allocation issue with a
> large table in a SQL
> >Server 2000 DB.
> >
> >I have a table which stores visits to a site, currently
> with 95 million
> >rows. This table currently has a total of 60GB space:
> 24GB used space, 4GB
> >index space, and 32 GB unused space. The server has been
> running out of
> >space recently, and I would like to reclaim alot of the
> unused space from
> >this table. The table definition is as follows:
> >
> >visitor_id int 4
> >visitor_settings varchar 7000
> >short_settings tinyint 1
> >visit_count int 4
> >created_reason tinyint 1
> >modified_by int 4
> >modified_date datetime 8
> >created_by int 4
> >created_date datetime 8
> >GUID_for_clustering uniqueidentifier 16
> >
> >There are 2 indexes, a PK index on visitor_id, and a
> clustered index on
> >GUID_for_clustering, both with a 50% fill factor.
> Shrinking the table helps
> >temporarily, but the unused space climbs back up to 30GB
> after reindexing
> >the table. Any thoughts on what is causing the
> allocation of all the extra
> >free space, and how I can reduce it? My initial thoughts
> relate to
> >adjusting the fill factor, or possibly modifying the
> visitor_settings
> >varchar 7000 field (perhaps 'text' would actually reduce
> the space
> >allocation?). Any thoughts would be much appreciated.
> >
> >Thanks!
> >
> >-Mark
> >
> >
> >.
> >|||Mark,
What exactly do you mean when you say "Guid for clustering"? Are you
talking about a Clustered Index? If so then why would you want to do that?
A Guid is about the worst choice for a clustered index that there is.
Here's what I think your issues are other than the clustered index on the
guid. You need plenty of free space in the database for normal activities
such as Reindexing. As a matter of fact BOL recommends 1.2 times the size
of the table in free space before you reindex. That is mainly because sqls
erver will rebuild the table (if it has a clustered index on it) elsewhere
in the db and then drop the original table when done. So you need lots of
free and preferably contiguous space to do this. If you reindex a table and
then shrink the database you will most likely undo all that you accomplished
when you reindexed the table. To shrink the files sql server must clear
space at the end of the file inwards to shrink it. This means it will move
the data to unused space near the beginning. This usually results in
getting heavy fragmentation of the tables again. So if you need the space
you can't keep reindexing the tables otherwise it will just keep growing
again. The real solution is to get more disk space and always keep plenty
of free space available. If your really tight you can bcp out all the data,
truncate all the tables,Shrink the db and bcp it back in again.
--
Andrew J. Kelly
SQL Server MVP
"Mark Weidner" <mjw22@.drexel.edu> wrote in message
news:e9SLoI55DHA.2764@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I'm having the following space allocation issue with a large table in a
SQL
> Server 2000 DB.
> I have a table which stores visits to a site, currently with 95 million
> rows. This table currently has a total of 60GB space: 24GB used space,
4GB
> index space, and 32 GB unused space. The server has been running out of
> space recently, and I would like to reclaim alot of the unused space from
> this table. The table definition is as follows:
> visitor_id int 4
> visitor_settings varchar 7000
> short_settings tinyint 1
> visit_count int 4
> created_reason tinyint 1
> modified_by int 4
> modified_date datetime 8
> created_by int 4
> created_date datetime 8
> GUID_for_clustering uniqueidentifier 16
> There are 2 indexes, a PK index on visitor_id, and a clustered index on
> GUID_for_clustering, both with a 50% fill factor. Shrinking the table
helps
> temporarily, but the unused space climbs back up to 30GB after reindexing
> the table. Any thoughts on what is causing the allocation of all the
extra
> free space, and how I can reduce it? My initial thoughts relate to
> adjusting the fill factor, or possibly modifying the visitor_settings
> varchar 7000 field (perhaps 'text' would actually reduce the space
> allocation?). Any thoughts would be much appreciated.
> Thanks!
> -Mark
>