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.

Mary
  • 331
  • 2
  • 5
  • 15

1 Answers1

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.

KASQLDBA
  • 7,203
  • 6
  • 30
  • 53