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, March 19, 2014 6:30 AM
Points: 250, Visits: 743

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: Yesterday @ 3:20 AM
Points: 880, Visits: 4,093
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, March 19, 2014 6:30 AM
Points: 250, Visits: 743
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: Yesterday @ 3:20 AM
Points: 880, Visits: 4,093


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: Today @ 12:25 AM
Points: 860, Visits: 2,391
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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:01 PM
Points: 3,153, Visits: 8,007
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, March 19, 2014 6:30 AM
Points: 250, Visits: 743

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: Yesterday @ 3:20 AM
Points: 880, Visits: 4,093
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, March 19, 2014 6:30 AM
Points: 250, Visits: 743
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: Yesterday @ 3:20 AM
Points: 880, Visits: 4,093
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