index size grown 1 GB in 1 day

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)

index size grown 1 GB in 1 day

Author

Topic

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 14:26:50

question, since yesterday i have a problem with my indexes, they started growing very big, this morning it stopped growing but now my database is 22 GB instead of 15 GB, i already tried to rebuild my indexes and even tried to recreate my indexes but they didn't get any smaller.

Is somebody formiliar with this problem, the app developer only changed a few tables and they say that it's have nothing to do with it, i don't know what they changed, i'll tell that later.

tia
Benjamin

Need an SQLDB consultant?
check www.veeningsengineering.nl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 14:35:45

Well something changed for these tables. Either more indexes were added, more data was added, fragmentation is greater, ...

It's hard to know without finding out more information.

Have you run dbcc updateusage to correct the inaccuracies in sys.indexes?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 14:39:28

im now running update usage, waiting for it till it's done.
for about 1000 rows were added, i also ran dbcc indexdefrag (dbname, tablename) <-- didn't helped.

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 14:42:13

didn't helped at all, indexes are the same as yesterday. i think i need to kick somebody in his n*ts because i don't believe everything he says about the changes. thnx for your help:-)
Benjamin

Need an SQLDB consultant?
check www.veeningsengineering.nl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 14:53:12

Have him provide to you the scripts that he used to modify the database.

Is your issue on SQL Serer 2005 or 2000? DBCC INDEXDEFRAG is a 2000 thing. We now use ALTER INDEX in 2005.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-09 : 15:08:05

What's fill factor? How about free space in each page?

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 15:12:50

for this i use 2005, i was forgotten that:-) i counted the data in the table, for about 5000 rows were inserted, i already checked for duplicated but there isn't

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 15:25:40

wan't set :-S changed it to 90 % and guess what

problem solved. Thnx for your help!!!!!!

Need an SQLDB consultant?
check www.veeningsengineering.nl

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-09 : 15:28:41

Well you shouldn't blindly modify fill factors to 90%. Do a lot of these tables have identity column as the primary key? For those, the fill factor should be set to 100%.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 15:32:10

for 1 index i changed the index to 1, the rest is non_unique

thnx

Need an SQLDB consultant?
check www.veeningsengineering.nl

b.veenings
Yak Posting Veteran

96 Posts

Posted - 2007-07-09 : 15:34:47

i ment 100%

Need an SQLDB consultant?
check www.veeningsengineering.nl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources