mdf file increasing rapidly - what are the causes for increasing the mdf file size?

Question

mdf file increasing rapidly - what are the causes for increasing the mdf file size?

venu_ksheerasagaram

SSCommitted

Points: 1911
More actions
July 30, 2009 at 11:02 pm

#134347

Hi Friends,
Here i am facing a big problem. I need your ideas and advices to overcome this.
I am having a database in which its Data file (.mdf file) size is 650Gb.Below are my queries.
1) mdf file size depends on what factors?
2) What are the ways to decrease the mdf file size?
3) Precautions to take to get rid of increasing the mdf file size rapidly?
Thank you all,
Venu Gopal.K
Software Engineer
INDIA

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

venu_ksheerasagaram (7/30/2009)
Hi Friends,
Here i am facing a big problem. I need your ideas and advices to overcome this.
I am having a database in which its Data file (.mdf file) size is 650Gb.Below are my queries.
1) mdf file size depends on what factors?
2) What are the ways to decrease the mdf file size?
3) Precautions to take to get rid of increasing the mdf file size rapidly?
Thank you all,

#1)

The amount of data entered into the system by users.

How long data needs to be retained in the database by the users for reporting and regulatory/legal requirements.

Index rebuilds and reorganizations.

#2)

If the file is growing, you don't want to shrink it as it will just need to grow again. In fact, you should plan the size of the mdf file such that it should have sufficient free space to allow for 3 to 6 months of activity before you need to add additional space to the mdf.

#3)

Not much you can do, especially if users are adding more data to the system than originally planned. This is the nature of databases, they tend to grow over time.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

venu_ksheerasagaram (7/30/2009)
1) mdf file size depends on what factors?
2) What are the ways to decrease the mdf file size?
3) Precautions to take to get rid of increasing the mdf file size rapidly?

#1 Database creation time original size plus additional extensions most probably caused by DUKIDS (*)

#2 Apply KDDT (**) until (DUKIDS volume = KDDT volume) over time

#3 Alternatives are to either implement GROFUI (***) initiative or like some authors suggest shutdown database; last one may sound a little extreme but it achieves desired outcome 100% of the time.

(*) Damn Users Keep Inserting Data Syndrome

(**) Keep Deleting Data Technique

(***) Get Rid of F&*%ing Users Initiative

😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Sounds like interview questions... if I answer correctly, do I get the job?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Jeff Moden (7/31/2009)
Sounds like interview questions... if I answer correctly, do I get the job?

Question is, do you want the job?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass