Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 table size growing everyday?

Author  Topic 

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-05-06 : 00:46:41
Experts...i got stuck with my huge table here.any help is highly appreciated.

i have a table of 500GB with 50 million records.I have 20 columns in the table and 2 columns are of text data type. i am having hardtime with the size of the table...everyday i delete 1 million records and run the update statistics and later during the day it inserts one more million records. So now, my problem is the datasize of the table is 50GB, index size is 20GB and the text size is 420GB. so now, my question is when ever i delete data and update the statistics, does the text data space release from the table?if Yes, then why does my table is growing 1GB on everyday?

Thanks in Advance.

dshelton
Yak Posting Veteran

73 Posts

Posted - 2011-05-06 : 02:31:28
This could be caused by table fragmentation, do you have a clustered index on this table?

Run sys.dm_db_index_physical_stats against this table and return your results.

David
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-05-06 : 08:25:16
Yes!! The table do have clustered index.

The table is of 500GB. May be the index_physic_stats
needs lit of resources which is difficult on our server.

Doesn't Update statistics after the delete operation take care of the
Fragmentation?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 08:46:36
No, update statistics only updates statistics

Have a look at ALTER INDEX (both rebuild and reorganize options) in BOL

Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-05-06 : 09:11:59
so does this mean, when ever i delete and update statistics, the fragmentation will be the same and some more fragmentation will be added to it?
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-05-06 : 09:15:39
Statistics have nothing to do with fragmentation.

So basically, yes. Deleting and adding rows will very likely cause index fragmentation.

How much fragmentation are you seeing?
Go to Top of Page

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-05-06 : 19:15:08
i havent ran the physical_stats query since it was huge table and i dont have that many resources...but my guess is around 250-300GB.
Go to Top of Page
   

- Advertisement -