mdf file grew bigger

Question

Post reply

mdf file grew bigger

herald_singh

Grasshopper

Points: 10
More actions
June 13, 2009 at 10:34 pm

#135103

Guys I need help. I'm wondering why did my mdf file grew bigger after I did the following:
1) Altered data types from nvarchar/ntext to varchar. Also reduced length as neccessary.
2) Deleted some indexes.
3) Used DBReindex on production tables
4) Updated null value fields to blank strings
I did this to 3 of our production tables which has 5M records. From 170gb the database size went up to 230gb.
Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29423 More actions · Answer 1

Are you sure your mdf grew or ldf? reindexing causes the log to grow heavily.

Also, if you have performed DBReindex after quite long time, chances are the file has grown due to this activity.

Pradeep Singh

herald_singh Grasshopper Points: 10 More actions · Answer 2

First of all thanks for the reply.

Yes, I'm certain that the mdf file grew. Before we did the updates and alters it was at 170gb and now its 210gb. What could have happened?

ps. One Orange Chip Points: 29423 More actions · Answer 3

unless you have excessive amount of data getting inserted, most likely the cause is dbcc reindex.

Perhaps this link should help you.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112916

What i can think of is, DBCC REINDEX creates a new index and drops older ones. If you have very large indexes, when the new indexes building activity completes, older ones still occupy your mdf and then they are deleted subsequently. Since your mdf grew because of this, it wouldnot shrink back to older size once your old index is dropped.

Pradeep Singh

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

herald_singh (6/13/2009)
Guys I need help. I'm wondering why did my mdf file grew bigger after I did the following:
1) Altered data types from nvarchar/ntext to varchar. Also reduced length as neccessary.
2) Deleted some indexes.
3) Used DBReindex on production tables
4) Updated null value fields to blank strings
I did this to 3 of our production tables which has 5M records. From 170gb the database size went up to 230gb.
Thanks in advance.

Three of these four things (all except (2)) could cause that effect. But as others has pointed out, reindexing is the most likely culprit

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]