August 15, 2013 at 2:45 pm
F: drive is total 100GB and now 75GB space left (only drive besides C: and it’s on VM Ware don’t know if that makes any different)
Initial size :
Datafile = 2885MB
Logfile 3399MB
1.Question is What is the best number for Data and Log initial size and ALSO what is best practice on Data ‘File Growth’ in Megabytes and Maximum File Size should I choose Restricted file growth or Unrestricted file growth??
2.Same on Log file FILE GROWTH what’s good no. in Megabytes and on Maximum File Size restricted or unrestricted if restricted what’s the good number?
August 15, 2013 at 11:57 pm
It depends... It al boils down to good capacity planning and disksize monitoring.
Aks the business/developers for the expected growth of the database. Set the initial size to the expected size (if the database doesn't grow) or to such a value so that it can handle about one year of growth (if the database keeps growing). In the latter also keep monitoring the size of the database and the free space within and increase the size before autogrowth kicks in.
Autogrowth of the data- and log-files should be set to a fixed number of MB. When "Instant file Initialisation" is on, the growth of the datafile will be much faster so a higher growth value can be set. This setting is not applicable to the growth of the log-file, so keep the autogrowth of the log-file to a more limited size.
According to the size you have specified, you could set the autogrow of the datafile to something like 500MB and the logfile to something like 250MB. Keep the maxgrowth of both files empty and keep monitoring the available diskspace.
If the database is in FULL recovery mode you must take regularly LOG backups (preverably every 15 - 120 minutes). If you don't take LOG backups with FULL recovery the size of the LOG file will always be growing.
Above are just very rough settings. Without the knowledge of the expected growth it's not possible to give you more then rough guidelines.
August 16, 2013 at 12:16 am
Hanshi what you suggest for the below scenario??
If Database size is around 20 GB
and Backup taken once a day and Log backup every 3 hours then what can be set??
************************************
Every Dog has a Tail !!!!! :-D
August 16, 2013 at 12:47 am
OnlyOneRJ (8/16/2013)
If Database size is around 20 GBand Backup taken once a day and Log backup every 3 hours then what can be set??
There is too little information. Like I said: it boiles down to good capacity planning.
What is the expected growth of the database? What is the available diskspace?
At least try to set the inital size of the LOGfile to such a value that it will never (under normal operation) have to autogrowth. To do so, you can set the autogrowth to a reasonable size (~250MB) and let it grow when necassary. Don't shrink the LOGfile and after a few days the autogrowth doesn't have to kick in anymore.
August 16, 2013 at 4:43 am
Thanks for you reply one more question what to sex Maximum file size on Data and Log?
Restricted file growth or Unrestricted file growth? if it is Restricted, how much should I put? plz suggests.
August 16, 2013 at 4:50 am
The most common setting is to set the growth to unrestricted. But you must keep monitoring the available disksize and the size of the database.
In our environment we had some sharepoint databases set to restricted growth. That was because the Sharepoint Admin would rather have errors instead of sharepoint sites that would explode in size.
When a database is unable to grow because of the maxgrowth setting, all actions that needed the grow will fail an be rolled back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply