SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database file growth settings


Database file growth settings

Author
Message
ravisamigo
ravisamigo
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 760
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.
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
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
ravisamigo
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 760
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
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489


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
Shawn Melton
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2223 Visits: 3512
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/
http://technet.microsoft.com/en-us/library/ms365418.aspx

Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4113 Visits: 11639
Shawn Melton (9/23/2010)


Do you have access to the Internet????



Was that supposed to be a trick question? Cool



Alvin Ramard
Memphis PASS Chapter

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
ravisamigo
ravisamigo
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 760
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
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
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
ravisamigo
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 760
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
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search