Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database file growth settings Expand / Collapse
Author
Message
Posted Thursday, September 23, 2010 6:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:00 PM
Points: 251, 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.

Post #991928
Posted Thursday, September 23, 2010 6:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:43 AM
Points: 882, Visits: 4,144
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
Post #991968
Posted Thursday, September 23, 2010 8:02 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:00 PM
Points: 251, 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.
Post #992039
Posted Thursday, September 23, 2010 8:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:43 AM
Points: 882, Visits: 4,144


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
Post #992047
Posted Thursday, September 23, 2010 8:13 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:36 AM
Points: 946, Visits: 2,740
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
Post #992057
Posted Thursday, September 23, 2010 8:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 1,510, Visits: 8,470
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

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.
Post #992068
Posted Thursday, September 23, 2010 11:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:00 PM
Points: 251, 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.
Post #992484
Posted Friday, September 24, 2010 1:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:43 AM
Points: 882, Visits: 4,144
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
Post #992528
Posted Friday, September 24, 2010 2:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 7:00 PM
Points: 251, 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.
Post #992542
Posted Friday, September 24, 2010 2:14 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:43 AM
Points: 882, Visits: 4,144
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
Post #992544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse