0

I want to create a new database that I can use for testing, restoring it from a full backup from a production database. These backup file sizes are around 45,486KB, and the production database size is 3.7GB. When I try creating the new database, the default initial size (MB)says 45 for the Primary rows data and 3739 for the Log, Autogrowth by 5MB restricted to 50MB for the data and by 10 percent restricted to another large number!

Any advice on what is the ideal number to start with? When do I need to change them? I'm using SQL Server 2008. Thanks.

Improve this question
1
  • Thanks John for pointing this link, I will use the advice provided.
    – Mary
    Apr 8, 2015 at 16:32

1 Answer 1

Reset to default
0

I believe the log file is seeing that large size because during the backup from production the size there itself was 3.6 GB approx.

As mentioned below by experts, excluding the part of my answer recommending a fix size. Therefore,to analyse more on how to decide the size you may want to keep, check on the autogrow size of data and log file:

select DB_NAME(files.database_id) database_name, files.name logical_name, 
CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],
[next_auto_growth_size (MB)] = case is_percent_growth
    when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)
    when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)
end,
is_read_only = case is_read_only 
    when 1 then 'Yes'
    when 0 then 'No'
end,    
is_percent_growth = case is_percent_growth 
    when 1 then 'Yes'
    when 0 then 'No'
end, 
physical_name
from sys.master_files files
where files.type in (0,1)
and files.growth != 0

Also It is not recommended to keep “In Percent” File Growth option for database files. If “In Percent” option is used for any database file and if the file size is very big, it may cause performance degradation during the file growth phase.

Improve this answer
4
  • 3
    Just curious - how did you get this recommendation - It is recommended to keep 1024MB for data files and 256MB for log files. ? You can use this script to find out the autogrowth events and then use a sensible value that works in your environment.
    – Kin Shah
    Apr 8, 2015 at 17:20
  • Yes right, Thanks @Kin, :) , i was just editing my answer for same. Based on observations from autogrowth we can decide the size.
    – KASQLDBA
    Apr 8, 2015 at 17:27
  • 1
    Who or what process is recommending for the data size growth to be 1024 and 256MB for log? I'd suggest we remove that part, it's not accurate for a lot of environments.
    – Ali Razeghi
    Apr 8, 2015 at 18:08
  • 1
    There is no right size for everyone. Please remove any specific numbers from your recommendations. This is how bad myths get started.
    – Aaron Bertrand
    Apr 8, 2015 at 19:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.