Database file growth settings

Question

Database file growth settings

ravisamigo

SSCertifiable

Points: 7440
More actions
September 23, 2010 at 6:08 am

#97503

Dear All,
Iam afraid, since all the databases in our organization have been set with defaul file growth settings ( 10% ,unrestricted growth) and database's size nearly 250-400 GB.
1. Please advise whether it's fine and also provide me if you have any related document about file growth settings.
2. MODEL database used space is 100%[ 1 MB, unrestricted growth ] & MSDB database used space is 95% [10% ,unrestricted growth ]. Please advise how can I reduce data file size or no need since autogrowth was already set? please advise.
Thanks and Regards,
Ravichandra.

Viewing 10 posts - 1 through 9 (of 9 total)

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

Approach depends database to database.

the database which is being updated very frequently(kind of highly OLTP database), % growth is not recommended. Its better to have autogrowth with fixed size* and unrestricted growth.

* you can see how quickly your file are growing and can decide accordingly on fixed autogrowth size.

----------
Ashish

ravisamigo SSCertifiable Points: 7440 More actions · Answer 2

Thanks.

But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

I think, I didn’t get answer for my previous question that, what is the action required from my side if used space of the data(mdf) file is reached to 98-100% of the production database.

Best Regards,

Ravichandra.

crazy4sql SSCoach Points: 19590 More actions · Answer 3

But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

if you google it, you will find the script which tells the growth of your data. I did in past and found it on one of the sql forum. Unfortunately the script is no more with me.

I think, I didn’t get answer for my previous question that, what is the action required from my side if used space of the data(mdf) file is reached to 98-100% of the production database.

you need to either increase the disk space where you have your mdf or need to move mdf(using attach/dettach) to new location where you have sufficient space for it to grow.

----------
Ashish

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

But could you advise how I can analyze/monitor the growth of the files? And also suggest ,is there any documents related to this?

Do you have access to the Internet????

Top searches from Bing:

http://sqlserverpedia.com/blog/sql-server-bloggers/monitoring-log-file-growth-using-perfmon/[/url]

http://technet.microsoft.com/en-us/library/ms365418.aspx

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Shawn Melton (9/23/2010)
Do you have access to the Internet????

Was that supposed to be a trick question? 😎

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ravisamigo SSCertifiable Points: 7440 More actions · Answer 6

Yes, I have access to the internet.

Please advise on my previous question regarding mdf file size growth reached to 100%.

Thanks and Regards,

Ravichandra.

crazy4sql SSCoach Points: 19590 More actions · Answer 7

i have just read another very good article related to moving the database and advice you to have a look into it.

http://www.sqlservercentral.com/articles/Administration/65896/

article describe why using alter database is preferred on detach/attach.

----------
Ashish

ravisamigo SSCertifiable Points: 7440 More actions · Answer 8

Thanks Ashish.

But I think, this is not answer for my question?

please advise on my question about mdf file size is reached to 100%.

Regards,

Ravichandra.

crazy4sql SSCoach Points: 19590 More actions · Answer 9

But I think, this is not answer for my question?

think again, your answer is already answered

Approach depends database to database.
the database which is being updated very frequently(kind of highly OLTP database), % growth is not recommended. Its better to have autogrowth with fixed size* and unrestricted growth.

if you have disk space available on that drive then you need to modify the autogrowth of your mdf. else need to move files to some other disk where you have sufficient space and then configure the autogrowth as suggested.

----------
Ashish