Friday, March 9, 2012

Excessive Free Space in Large Table

If all that space gets consume after reindexing then its
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:

>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...[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

No comments:

Post a Comment