the transaction log. Read up on truncate/ shrink TLog.
quote:
>--Original Message--
>Hello all,
>I'm having the following space allocation issue with a
large table in a SQL
quote:
>Server 2000 DB.
>I have a table which stores visits to a site, currently
with 95 million
quote:
>rows. This table currently has a total of 60GB space:
24GB used space, 4GB
quote:
>index space, and 32 GB unused space. The server has been
running out of
quote:
>space recently, and I would like to reclaim alot of the
unused space from
quote:
>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
quote:
>GUID_for_clustering, both with a 50% fill factor.
Shrinking the table helps
quote:
>temporarily, but the unused space climbs back up to 30GB
after reindexing
quote:
>the table. Any thoughts on what is causing the
allocation of all the extra
quote:
>free space, and how I can reduce it? My initial thoughts
relate to
quote:
>adjusting the fill factor, or possibly modifying the
visitor_settings
quote:
>varchar 7000 field (perhaps 'text' would actually reduce
the space
quote:Because of space concerns, this database has no transaction log (it is
>allocation?). Any thoughts would be much appreciated.
>Thanks!
>-Mark
>
>.
>
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...[QUOTE]
> If all that space gets consume after reindexing then its
> the transaction log. Read up on truncate/ shrink TLog.
>
> large table in a SQL
> with 95 million
> 24GB used space, 4GB
> running out of
> unused space from
> clustered index on
> Shrinking the table helps
> after reindexing
> allocation of all the extra
> relate to
> visitor_settings
> the space
